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

[dustin at cnw.com]

On Fri, 18 Feb 2005, Jesse Vincent wrote:

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

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