Weird Slow SelectOwner query

RT Users
1

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

2

Could you show full explain for the query?On 3/28/06, Laas Toom laas.toom@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.

3

Tuesday 28 March 2006 7:01 pm Ruslan Zakirov wrote:

Could you show full explain for the query?

Unfortunately (or fortunately :wink: 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

  1. 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

4

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…
-EHAVENOTIMEOn 3/29/06, Laas Toom laas.toom@eenet.ee wrote:

Tuesday 28 March 2006 7:01 pm Ruslan Zakirov wrote:

Could you show full explain for the query?

Unfortunately (or fortunately :wink: 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

  1. 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

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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: Careers — Best Practical Solutions

Best regards, Ruslan.

5

tor 2006-06-01 klockan 16:55 +0400 skrev 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

The following seems to help MySQL optimize the query better:

alter table ACL add key ACLKTH1 using BTREE (PrincipalType, RightName);

/Alexander