FW: Slow ticket search page becoming a problem

RT Users
1

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.From: Ruslan Zakirov [mailto:ruslan.zakirov@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.

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.

2

I’ve done more investigation on this query and looks like we have to
do more work to make it better.On 6/15/07, Wojciech Jawor wjawor@shopzilla.com wrote:

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@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@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.

Best regards, Ruslan.