FW: [rt-users] Slow ticket search page becoming a problem
Wojciech Jawor
wjawor at Shopzilla.com
Fri Jun 15 11:05:34 EDT 2007
Hi,
Has this issue been resolved in RT 3.6.4rc2 by any chance?
Could you please give me an update on the status of the fix? This is
becoming a huge problem for us.
Thanks,
Wojciech Jawor
Software Architect
Shopzilla, Inc.
-----Original Message-----
From: Ruslan Zakirov [mailto:ruslan.zakirov at gmail.com]
Sent: Thu 26/04/2007 17:34
To: Philip Kime
Cc: Jesse Vincent; Todd Chapman; RT Users
Subject: Re: [rt-users] Slow ticket search page becoming a problem
Philip, please try the following query and send us times and EXPLAIN:
SELECT STRAIGHT_JOIN DISTINCT main.* FROM
ACL ACL_4,
Groups Groups_3,
CachedGroupMembers CachedGroupMembers_2,
Principals Principals_1,
Users main
WHERE ((ACL_4.PrincipalType = Groups_3.Type))
AND ((ACL_4.RightName = 'OwnTicket'))
AND ((CachedGroupMembers_2.MemberId = Principals_1.id))
AND ((Groups_3.id = CachedGroupMembers_2.GroupId))
AND ((Principals_1.Disabled = '0'))
AND ((Principals_1.PrincipalType = 'User'))
AND ((Principals_1.id != '1'))
AND ((main.id = Principals_1.id))
AND ((ACL_4.ObjectType = 'RT::Queue') OR (ACL_4.ObjectType =
'RT::System'))
AND ((Groups_3.Domain = 'RT::Queue-Role') OR (Groups_3.Domain =
'RT::System-Role'))
ORDER BY main.RealName ASC;
It's the same query but with forced order of joins, I do believe that
this is the ideal plan for joins in this situation for all setups.
On 4/27/07, Philip Kime <pkime at shopzilla.com> wrote:
>
>
> Ok, the issue is that MYSQL 5 won't use the index on main.Name by
default
> (possible keys list PRIMARY only, which is useless for this ORDER BY
> clause), which it really needs to do with an ORDER BY clause for
main.Name
> (or main.RealName as in my example as I have modified the display
code). It
> is fixed if you force the index use:
>
[snip]
>
> Then it's nice and fast again. The explain shows that it's still a
> filesort/temp query but it does a indexed table scan instead of an
unindexed
> range scan.
>
> I assume that this would need a SearchBuilder mod to force the use of
the
> index related to the ORDER BY clause?
>
> PK
--
Best regards, Ruslan.
More information about the rt-users
mailing list