[rt-users] weird Slow SelectOwner query

[Ruslan Zakirov]

I've investigated more on this problem and found out that this problem
of mysql as couldn't optimize well some queries when it's possible, I
know how to hack a workaround and help RDBMs optimize it, but later...
-EHAVENOTIME

On 3/29/06, Laas Toom <laas.toom at eenet.ee> wrote:
> 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
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>
>
> We're hiring! Come hack Perl for Best Practical: http://bestpractical.com/about/jobs.html
>


-- 
Best regards, Ruslan.