[rt-users] weird Slow SelectOwner query

[Laas Toom]

Hello!

Some time ago when we upgraded to RT 3.4.5 and MySQL 5.0.18, we started having 
slow SelectOwner queries, but then an answer from this list helped us out - 
we created an extra index to the table CachedGroupMembers:
create index mbowe_MemberId on CachedGroupMembers(MemberId)

But now we moved the database to dedicated server and again this query takes 
~8 sec to complete. 
Then I thought this index was not the solution, dropped that extra index, did 
some testing - nothing,  recreated index - still nothing and redropped this 
index.
Interestingly enough after this last dropping the query started taking 0.8 - 
1.4 sec. But this probably is some caching by MySQL server, because of 
frequent queries.

BTW, all the times are taken from phpMyAdmin, as RT on fastcgi seems to cache 
this query after first time for each ticket, but as it is mostly needed only 
one time for a ticket, this caching does not help.

The problematic query is:
SELECT DISTINCT main.*
  FROM Users main, Principals Principals_1, ACL ACL_2, CachedGroupMembers 
CachedGroupMembers_3
  WHERE ((ACL_2.PrincipalId = CachedGroupMembers_3.GroupId)) AND 
((ACL_2.PrincipalType = 'Group')) AND
     ((ACL_2.RightName = 'OwnTicket')) AND ((CachedGroupMembers_3.MemberId = 
Principals_1.id)) AND
     ((Principals_1.Disabled = '0')) AND ((Principals_1.PrincipalType = 
'User')) AND ((Principals_1.id !=
     '1')) AND ((main.id = Principals_1.id)) AND ((ACL_2.ObjectType = 
'RT::Ticket' AND ACL_2.ObjectId = 1365)
     OR (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 8) OR 
(ACL_2.ObjectType = 'RT::System'))
  ORDER BY main.Name ASC

After the first dropping of index MySQL EXPLAIN showed that this 'main' table 
is joined first with type='range', rows=9432 and "using where; using 
temporary; using filesort".

After second dropping of the extra index the 'main' table is joined in second,  
the row count increased to 9444, but still this query is faster.

But when I reorder the tables in the query, so that 'main' is last:
SELECT ...
FROM Principals Principals_1, ACL ACL_2, CachedGroupMembers 
CachedGroupMembers_3,  Users main
WHERE ....

Then the query takes 0.0030 sec all the time (as it did before the second 
creation of the index) and 'main' table is joined in as third table with 
type='eq_ref' and rows=1

So I am lost here - is this MySQL problem or can RT be forced to use the 
latter FROM statement so that 'main' table is joined in last?

Any help appreciated.

Best regards,
Laas Toom