[rt-devel] Slow Queries part3, Groups

[Matthew Watson]

well,

i've played around a little bit with this.

by dropping the search for the "RT::Queue-Role" or "RT::Ticket-Role" parts
of the query it index for primary kicks in (bringing the result down to 
0.01 ms).

Now i'm not really sure what these fit in, I assume its for finding if a 
Requestor can has that given right, perhaps this query would be better off 
being broken down into two queries (I've spent about an hour trying to get 
this query optimised, with no luck).

Any thoughts?

The query without the above bits looks like,

SELECT DISTINCT main.*
FROM Groups main, Principals Principals_1, ACL ACL_2
WHERE ((ACL_2.RightName = 'OwnTicket')OR(ACL_2.RightName = 'SuperUser'))
AND ( 	  (    ACL_2.PrincipalId = Principals_1.id
AND ACL_2.PrincipalType = 'Group'
AND (   main.Domain = 'SystemInternal'
OR main.Domain = 'UserDefined'
OR main.Domain = 'ACLEquivalence')
AND main.id = Principals_1.id))
AND ( ACL_2.ObjectType = 'RT::System'  OR ( ACL_2.ObjectType = 'RT::Queue' 
AND ACL_2.ObjectId = 36) )
ORDER BY main.Name ASC

Matt.