[rt-users] weird Slow SelectOwner query

[Ruslan Zakirov]

Could you show full explain for the query?

On 3/28/06, Laas Toom <laas.toom at eenet.ee> wrote:
> 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

--
Best regards, Ruslan.