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

[Jesse Vincent]

On Fri, Feb 18, 2005 at 05:18:09PM -0800, dustin at cnw.com wrote:
> 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.

> 
> 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!
> _______________________________________________
> Rt-devel mailing list
> Rt-devel at lists.bestpractical.com
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel
> 

--