[rt-users] "More about" box loads very slowly

[David X. Glover]

On 8 Mar 2010, at 10:29, David X. Glover wrote:

> We're running RT 3.6.5* on MySQL 5.0.51a, and the "More about <User>" box on the ticket display page takes several seconds to load.
> 
> I've seen other people with this problem on the list, but never a solution.
> 
> Does anyone have any ideas about what this specific box is doing and why it's so slow?

Extra info:

This is the SQL query causing the problem:

SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN Groups Groups_1  ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND ( Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.MemberId = Users_3.id ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id )  WHERE (Users_3.id = '7611') AND (main.Status != 'deleted') AND ( ( CachedGroupMembers_2.id IS NOT NULL )  AND  ( main.Status = 'open' OR main.Status = 'new' ) ) AND (main.Type = 'ticket') AND (main.EffectiveId = main.id)  ORDER BY main.Priority DESC  LIMIT 10;

And some more diagnostic information: (Caution, wide lines ahead.)

mysql> EXPLAIN SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN Groups Groups_1  ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND ( Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.MemberId = Users_3.id ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id )  WHERE (Users_3.id = '7611') AND (main.Status != 'deleted') AND ( ( CachedGroupMembers_2.id IS NOT NULL )  AND  ( main.Status = 'open' OR main.Status = 'new' ) ) AND (main.Type = 'ticket') AND (main.EffectiveId = main.id)  ORDER BY main.Priority DESC  LIMIT 10;
Current database: rt

+----+-------------+----------------------+--------+------------------------------------+---------+---------+----------------------+-------+-----------------------------------------------------------+
| id | select_type | table                | type   | possible_keys                      | key     | key_len | ref                  | rows  | Extra                                                     |
+----+-------------+----------------------+--------+------------------------------------+---------+---------+----------------------+-------+-----------------------------------------------------------+
|  1 | SIMPLE      | Users_3              | ref    | Users3                             | Users3  | 5       | const                |     2 | Using where; Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | Groups_1             | ALL    | NULL                               | NULL    | NULL    | NULL                 |  7109 | Using where                                               | 
|  1 | SIMPLE      | CachedGroupMembers_2 | ALL    | PRIMARY                            | NULL    | NULL    | NULL                 | 16484 | Using where                                               | 
|  1 | SIMPLE      | main                 | eq_ref | PRIMARY,Tickets3,Tickets4,Tickets5 | PRIMARY | 4       | rt.Groups_1.Instance |     1 | Using where                                               | 
+----+-------------+----------------------+--------+------------------------------------+---------+---------+----------------------+-------+-----------------------------------------------------------+

mysql> SHOW INDEX FROM Users;
+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Users |          1 | Users2   |            1 | Name         | A         |         512 |     NULL | NULL   |      | BTREE      |         | 
| Users |          1 | Users3   |            1 | id           | A         |         512 |     NULL | NULL   | YES  | BTREE      |         | 
| Users |          1 | Users3   |            2 | EmailAddress | A         |         512 |     NULL | NULL   | YES  | BTREE      |         | 
| Users |          1 | Users4   |            1 | EmailAddress | A         |         512 |     NULL | NULL   | YES  | BTREE      |         | 
+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

mysql> SHOW INDEX FROM CachedGroupMembers;
+--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table              | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| CachedGroupMembers |          0 | PRIMARY  |            1 | id          | A         |       16498 |     NULL | NULL   |      | BTREE      |         | 
+--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Ruslan Zakirov sent me an email that said:

On 8 Mar 2010, at 12:46, Ruslan Zakirov wrote:

> It usually means missing key on CachedGroupMembers table, you need an
> index on (MemberId, GroupId) columns. Order of columns is important.

If that's true, what do I need to do?

Thanks.

-- 
David X. Glover
Department of Physics
University of Oxford
http://www.physics.ox.ac.uk/