Query Slowness (was: 3.4.0rc1 slow)

Hello,

Our instance of RT is suffering from the exact same issue. It happens
anytime an owner dropdown box is rendered. RT 3.0.11, MySQL 4.0.16,
SearchBuilder p5-DBIx-SearchBuilder-0.99

David Kerry wrote :

LOG: duration: 1983.993 ms statement:
SELECT DISTINCT main.* FROM (Groups main LEFT JOIN
Principals Principals_2 ON ( main.id = Principals_2.id)),
ACL ACL_1 WHERE ((ACL_1.RightName = ‘OwnTicket’)) AND
((Principals_2.Disabled = ‘0’)) AND ( ( ACL_1.PrincipalId = main.id AND
ACL_1.PrincipalType = ‘Group’ AND ( main.Domain = ‘SystemInternal’ OR
main.Domain = ‘UserDefined’ OR main.Domain =
‘ACLEquivalence’)) OR ( ( (main.Domain = ‘RT::Queue-Role’ AND
main.Instance = 102) ) AND main.Type = ACL_1.PrincipalType) ) AND
(ACL_1.ObjectType = ‘RT::System’ OR (ACL_1.ObjectType = ‘RT::Queue’ AND
ACL_1.ObjectId = 102) ) ORDER BY main.Name ASC

Here’s ours :

Time: 050218 16:12:22

User@Host: rt_user[rt_user] @ localhost []

Query_time: 5 Lock_time: 0 Rows_sent: 17 Rows_examined: 72

SELECT DISTINCT main.* FROM ( Groups main LEFT JOIN Principals as
Principals_2 ON ( main.id = Principals_2.id)), ACL ACL_1 WHERE
((ACL_1.RightName = ‘OwnTicket’)OR(ACL_1.RightName = ‘SuperUser’)) AND
((Principals_2.Disabled = ‘0’)) AND
( ( ACL_1.PrincipalId = main.id
AND ACL_1.PrincipalType = 'Group’
AND ( main.Domain = 'SystemInternal’
OR main.Domain = 'UserDefined’
OR main.Domain = ‘ACLEquivalence’))
OR ( ( (main.Domain = ‘RT::Queue-Role’ AND main.Instance = 3)
OR ( main.Domain = ‘RT::Ticket-Role’ AND main.Instance = 410374) ) AND
main.Type = ACL_1.PrincipalType) )
AND (ACL_1.ObjectType = ‘RT::System’ OR (ACL_1.ObjectType =
‘RT::Queue’ AND ACL_1.ObjectId = 3) ) ORDER BY main.Name ASC;

Output of a DESCRIBE on that query :

table, type, possible_keys, key, key_len, ref, rows, Extra
ACL_1, range, ACL1, ACL1, 54, NULL, 19, Using where; Using index; Using temporary; Using filesort
main, ALL, PRIMARY, Groups1, Groups2, NULL, NULL, NULL ,576818, Using where
Principals_2, eq_ref, PRIMARY,PRIMARY, 4, main.id, 1, Using where; Distinct

Yikes!

Hello,

Our instance of RT is suffering from the exact same issue. It happens
anytime an owner dropdown box is rendered. RT 3.0.11, MySQL 4.0.16,
SearchBuilder p5-DBIx-SearchBuilder-0.99

So. We did a lot to fix this in 3.4.0 and newer searchbuilders.
Upgrading might be worth a shot.

Hello,

Our instance of RT is suffering from the exact same issue. It happens
anytime an owner dropdown box is rendered. RT 3.0.11, MySQL 4.0.16,
SearchBuilder p5-DBIx-SearchBuilder-0.99

So. We did a lot to fix this in 3.4.0 and newer searchbuilders.
Upgrading might be worth a shot.

The thing that scares me is that David and his tests with 3.4.0 show the
same issue with the same query using the new RT and the new version of
SearchBuilder.

David’s Thread :

http://lists.bestpractical.com/pipermail/rt-devel/2005-January/006663.html

I took a look at the patch you posted in this post :

http://lists.bestpractical.com/pipermail/rt-devel/2005-January/006709.html

But it looks like the performance problem he had was when there was no
object passed to the SelectOwner component.

This is an issue that someone reported. I proposed a fix. Everyone who’s
tried the fix (which is more extensive than your first glance, I
believe) has told me that it’s resulted in a massive performance
improvement. Give it a shot and tell me if I’m wrong? Regardless, we’re
unlikely to do performance work on 3.0, except for customers who can’t
upgrade.

Jesse