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/
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [email protected]
2010 RT Training Sessions!
San Francisco, CA, USA - Feb 22 & 23
Dublin, Ireland - Mar 15 & 16
Boston, MA, USA - April 5 & 6
Washington DC, USA - Oct 25 & 26
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
