[rt-users] Query Builder very slow after migration to MySQL 5.0

[Michael Polivanov]

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