[rt-users] weird Slow SelectOwner query

[Laas Toom]

Tuesday 28 March 2006 7:01 pm Ruslan Zakirov wrote:
> Could you show full explain for the query?

Unfortunately (or fortunately ;-) the query now is planned differently than, 
when I began testing yesterday so I can not give you the very first explain 
output, where the 'main' table was joined in first and which took 8 sec to 
complete.

But below are two different EXPLAINs which I can get right now.
(Use fixed-font to view these tabels, but I also included URLs for screenshots 
of the same tables)

1) The way this query is run now on our server. This query takes 1.5 sec to 
complete and after some runs gets even faster (caching probably).

http://www3.eenet.ee/~laas/explain_select_owners_1.png

SQL query:
EXPLAIN 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;

EXPLAIN:
id select_ table  type  possible_   key     key_ ref       rows Extra
   type                 keys                len
------------------------------------------------------------------------------
1 SIMPLE  ACL_2  range  ACL1        ACL1    85   NULL        7  Using where;
                                                                Using index;
                                                                Using 
temporary;
                                                                Using filesort
------------------------------------------------------------------------------
1 SIMPLE  main   range  PRIMARY,    PRIMARY 4    NULL      9259 Using where
                        Users3
------------------------------------------------------------------------------
1 SIMPLE  Princi eq_ref PRIMARY     PRIMARY 4    rt3.        1  Using where;
          pals_1                                 main.id        Distinct 
------------------------------------------------------------------------------
1 SIMPLE  Cached ref    DisGrouMem  GrouMem 10   rt3.ACL_2.  1 Using where;
          Group         GrouMem                  PrincipalId,   Using index;
          Members_3     MemberId_idx             rt3.main.id    Distinct


2) The same query, but I have put the 'Users main' table to the last position 
in the FROM statement. This query is fast (0.0032 sec) from the beginning (as 
can be concluded by the small number of rows involved in this).

http://www3.eenet.ee/~laas/explain_select_owners_2.png

SQL query:
EXPLAIN SELECT DISTINCT main.* FROM Principals Principals_1, ACL ACL_2, 
CachedGroupMembers CachedGroupMembers_3, Users main 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;

EXPLAIN:
id select_ table  type   possible_   key      key_ ref       rows Extra
   type                  keys                 len
-----------------------------------------------------------------------------
1  SIMPLE  ACL_2  range  ACL1        ACL1     85   NULL        7  Using where;
                                                                  Using 
-----------------------------------------------------------------------------
1  SIMPLE  Cached ref    DisGrouMem, GrouMem  5    rt3.ACL_2.  4  Using where;
           Group         GrouMem,                  PrincipalId    Using index
           Members_3     MemberId_idx
-----------------------------------------------------------------------------
1  SIMPLE  main   eq_ref PRIMARY,    PRIMARY  4    rt3.CachedG 1           
                         Users3
-----------------------------------------------------------------------------
1  SIMPLE  Princ  eq_ref PRIMARY     PRIMARY  4    rt3.main.id 1  Using where;
           ipals_1                                                Distinct


Best regards,
Laas Toom