Query Builder very slow after migration to MySQL 5.0

RT Users
1

Hello,

i have a big problem after migration from MySQL 4.1.11 to 5.0.45: some
pages need minutes to load, especially the “Query Builder” page. I got
the query, which is running while loading the page and compared the
explain plan on both databases. Here is the outcome:

Query:

SELECT DISTINCT main . *
FROM Users main
CROSS JOIN ACL ACL_2
JOIN Principals Principals_1 ON ( Principals_1.id = main.id )
JOIN CachedGroupMembers CachedGroupMembers_3 ON (
CachedGroupMembers_3.MemberId = Principals_1.id )
WHERE ( Principals_1.Disabled = ‘0’ )
AND ( ACL_2.PrincipalId = CachedGroupMembers_3.GroupId )
AND ( Principals_1.id != ‘1’ )
AND ( ACL_2.PrincipalType = ‘Group’ )
AND ( Principals_1.PrincipalType = ‘User’ )
AND ( ACL_2.RightName = ‘OwnTicket’ )
AND (
( ACL_2.ObjectType = ‘RT::Queue’)
OR
( ACL_2.ObjectType = ‘RT::System’)
)
ORDER BY main.Name ASC;

MySQL 4.1.11

| id | select_type | table | type | possible_keys
| key | key_len | ref
| rows | Extra
|
| 1 | SIMPLE | ACL_2 | range | ACL1
| ACL1 | 50 | NULL
| 5798 | Using where; Using index; Using temporary; Using
filesort |
| 1 | SIMPLE | CachedGroupMembers_3 | ref |
DisGrouMem,MemberId | DisGrouMem | 5 |
rt3.ACL_2.PrincipalId | 1 | Using where; Using index
|
| 1 | SIMPLE | Principals_1 | eq_ref |
PRIMARY,Principals_PrincipalType_idx | PRIMARY | 4 |
rt3.CachedGroupMembers_3.MemberId | 1 | Using where
|
| 1 | SIMPLE | main | eq_ref | PRIMARY
| PRIMARY | 4 | rt3.Principals_1.id
| 1 |
|

MySQL 5.0.45

| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra
|
| 1 | SIMPLE | main | range | PRIMARY |
PRIMARY | 4 | NULL | 1092 | Using where; Using
temporary; Using filesort |
| 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY |
PRIMARY | 4 | rt3.main.id | 1 | Using where; Distinct
|
| 1 | SIMPLE | CachedGroupMembers_3 | ref | CGM1,CGM2 |
CGM1 | 5 | rt3.Principals_1.id | 1 | Using where; Using
index; Distinct |
| 1 | SIMPLE | ACL_2 | range | ACL1 |
ACL1 | 54 | NULL | 4129 | Using where; Using
index; Distinct |

Please note the big difference in column “row” for the main/Users
table and the order of tables processed. Ref is also different for
main/Users in both plans.

Execution of the query on the database resulted in:

4.1.1: 449 rows in set (3.18 sec)
5.0.45: 461 rows in set (17 min 32.94 sec)

Any idea how i can get my RT back to acceptable performance?
I am using RT 3.6.6 here. Would an upgrade to 3.6.7 help?

Thanks in advance,
-michael

2

Ok, i have solved this one: i rebuild all indexes on
CachedGroupMembers and now the plan and exec time changed to expected.

But “Query Builder” page is still slow, now this query takes a long
time to finish:

SELECT DISTINCT main . *
FROM Users main
CROSS JOIN ACL ACL_4
JOIN Principals Principals_1 ON ( Principals_1.id = main.id )
JOIN CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Principals_1.id )
JOIN Groups Groups_3 ON ( Groups_3.id = CachedGroupMembers_2.GroupId )
WHERE (
Principals_1.Disabled = ‘0’
)
AND (
ACL_4.PrincipalType = Groups_3.Type
)
AND (
Principals_1.id != ‘1’
)
AND (
Principals_1.PrincipalType = ‘User’
)
AND (
ACL_4.RightName = ‘OwnTicket’
)
AND (
Groups_3.Domain = ‘RT::Queue-Role’
)
AND (
(
ACL_4.ObjectType = ‘RT::Queue’
)
OR (
ACL_4.ObjectType = ‘RT::System’
)
)
ORDER BY main.Name ASC;

5.1 plan

| id | select_type | table | type | possible_keys
| key | key_len | ref
| rows | Extra |
| 1 | SIMPLE | main | range | PRIMARY
| PRIMARY | 4 | NULL
| 1092 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | Principals_1 | eq_ref |
PRIMARY,Principals_PrincipalType_idx | PRIMARY | 4 |
rt3.main.id | 1 | Using where; Distinct
|
| 1 | SIMPLE | CachedGroupMembers_2 | ref |
MemberId,DisGrouMem | MemberId | 5 |
rt3.main.id | 1 | Using where; Distinct
|
| 1 | SIMPLE | ACL_4 | range | ACL1
| ACL1 | 54 | NULL
| 4129 | Using where; Using index; Distinct |
| 1 | SIMPLE | Groups_3 | eq_ref |
PRIMARY,Groups1,Groups2 | PRIMARY | 4 |
rt3.CachedGroupMembers_2.GroupId | 1 | Using where; Distinct
|

4.1 plan

| id | select_type | table | type | possible_keys
| key | key_len | ref
| rows | Extra
|
| 1 | SIMPLE | Groups_3 | ref |
PRIMARY,Groups1,Groups2 | Groups1 | 65 | const
| 1280 | Using where; Using index; Using
temporary; Using filesort |
| 1 | SIMPLE | CachedGroupMembers_2 | ref |
DisGrouMem,MemberId | DisGrouMem | 5 |
rt3.Groups_3.id | 1 | Using where; Using index
|
| 1 | SIMPLE | Principals_1 | eq_ref |
PRIMARY,Principals_PrincipalType_idx | PRIMARY | 4 |
rt3.CachedGroupMembers_2.MemberId | 1 | Using where
|
| 1 | SIMPLE | ACL_4 | range | ACL1
| ACL1 | 50 | NULL
| 5798 | Using where; Using index
|
| 1 | SIMPLE | main | eq_ref | PRIMARY
| PRIMARY | 4 | rt3.Principals_1.id
| 1 |
|

If i force to use Groups1 index, the execution time goes to normal
state and the 5.1 plan looks like:

| id | select_type | table | type | possible_keys
| key | key_len | ref
| rows | Extra
|
| 1 | SIMPLE | Groups_3 | ref | Groups1
| Groups1 | 67 | const
| 3544 | Using where; Using index; Using temporary; Using
filesort |
| 1 | SIMPLE | CachedGroupMembers_2 | ref |
MemberId,DisGrouMem | DisGrouMem | 5 |
rt3.Groups_3.id | 1 | Using where; Using index
|
| 1 | SIMPLE | main | eq_ref | PRIMARY
| PRIMARY | 4 |
rt3.CachedGroupMembers_2.MemberId | 1 |
|
| 1 | SIMPLE | ACL_4 | range | ACL1
| ACL1 | 54 | NULL
| 4129 | Using where; Using index; Distinct
|
| 1 | SIMPLE | Principals_1 | eq_ref |
PRIMARY,Principals_PrincipalType_idx | PRIMARY | 4 |
rt3.main.id | 1 | Using where; Distinct
|

Changed line is:

JOIN Groups Groups_3 USE INDEX(Groups1) ON ( Groups_3.id =
CachedGroupMembers_2.GroupId )

Can anyone give me a hint how i can fix this or how i can change the
RT code, so the Groups1 index is used for this query?

Regards,
-michael

3

I have fixed my second performance problem by creating following index:

CREATE INDEX Groups_mp ON Groups (Domain,id,Type);

Regards,
-michael