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

[Michael Polivanov]

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