[Rt-devel] Slowest query in our setup

[Ruslan U. Zakirov]

Ruslan U. Zakirov wrote:
> Jesse Vincent wrote:
> 
>>
>>
>> On Sun, Aug 01, 2004 at 02:41:37AM +0400, Ruslan U. Zakirov wrote:
>>
>>>     Hello.
>>> SELECT + EXPLAIN attached.
>>>
>>> Fix to issue with Queue-Roles(Queue CC/AdminCc rights was not 
>>> inherited by tickets) broke optimisation ability.
>>
>>
>>
>> Can you list off the indices you have on groups?
IMHO indicies can't help here cause it's mysql 4.0.20 that doesn't 
support subqueries and because of OR clause mysql can't use index in 
Groups <=> ACL join at all.


But as I can see if you do:
UPDATE ACL a, Groups g SET a.PrincipalType = 'UserEquiv' WHERE g.Type = 
'UserEquiv' AND g.id = a.PrincipalId;
UPDATE Groups SET Type = 'Group' WHERE Type = '';


Then query could be changed:
SELECT DISTINCT main.*
FROM Groups main, Principals Principals_2, ACL ACL_1
WHERE
( main.id = Principals_2.id) AND
((ACL_1.RightName = 'OwnTicket')OR(ACL_1.RightName = 'SuperUser')) AND
((Principals_2.Disabled = '0')) AND
main.Type = ACL_1.PrincipalType AND
 
 

( (
         ACL_1.PrincipalId = main.id AND
         ( main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' 
OR main.Domain = 'ACLEquivalence')
) OR (
         ( (main.Domain = 'RT::Queue-Role' AND main.Instance = 9)  OR ( 
main.Domain = 'RT::Ticket-Role' AND main.Instance = 47279) )
) )
 
 

AND (ACL_1.ObjectType = 'RT::System' OR  (ACL_1.ObjectType = 'RT::Queue' 
AND ACL_1.ObjectId = 9) )
 
 

ORDER BY main.Name ASC;


+--------------+--------+-----------------------------------------+---------+---------+---------------------+------+----------------------------------------------+
| table        | type   | possible_keys                           | key 
     | key_len | ref                 | rows | Extra 
                    |
+--------------+--------+-----------------------------------------+---------+---------+---------------------+------+----------------------------------------------+
| ACL_1        | range  | ACL1,ACLFKI1,ACL2,ACL3,ACL4             | ACL1 
    |      54 | NULL                |   11 | Using where; Using 
temporary; Using filesort |
| main         | ref    | PRIMARY,Groups1,Groups2,Groups4,Groups5 | 
Groups2 |      65 | ACL_1.PrincipalType |  491 | Using where 
                       |
| Principals_2 | eq_ref | PRIMARY                                 | 
PRIMARY |       4 | main.id             |    1 | Using where; Distinct 
                       |
+--------------+--------+-----------------------------------------+---------+---------+---------------------+------+----------------------------------------------+