[Rt-devel] Query Slowness (was: 3.4.0rc1 slow)

[dustin at cnw.com]

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 at 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!