"More about" box loads very slowly

RT Users
1

We’re running RT 3.6.5* on MySQL 5.0.51a, and the “More about ” 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?

Cheers.

*Yes, we acknowledge that it’s out of date and we do have plans to upgrade, just not immediately.
David X. Glover
Department of Physics
University of Oxford

2

Hello,

It usually means missing key on CachedGroupMembers table, you need an
index on (MemberId, GroupId) columns. Order of columns is important.On Mon, Mar 8, 2010 at 1:29 PM, David X. Glover d.glover1@physics.ox.ac.uk wrote:

We’re running RT 3.6.5* on MySQL 5.0.51a, and the “More about ” 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?

Cheers.

*Yes, we acknowledge that it’s out of date and we do have plans to upgrade, just not immediately.

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

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

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

Best regards, Ruslan.

3

We’re running RT 3.6.5* on MySQL 5.0.51a, and the “More about ” 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

4

Hello, David.

[snip]

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 | |
±------±-----------±---------±-------------±-------------±----------±------------±---------±-------±-----±-----------±--------+

Create primary key on Users(id). Drop index Users3. As an option make
keys Users2 and Users4 unique.

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 | |
±-------------------±-----------±---------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+

Create index on CachedGroupMembers(MemberId, GroupId, Disabled) and
reversed one CachedGroupMembers(GroupId, MemberId, Disabled).

Instructions on dropping indexes and creating new you can find in
mysql’s documentation.

Best regards, Ruslan.

5

Create primary key on Users(id). Drop index Users3. As an option make
keys Users2 and Users4 unique.

Create index on CachedGroupMembers(MemberId, GroupId, Disabled) and
reversed one CachedGroupMembers(GroupId, MemberId, Disabled).

It might be useful to add this information to the wiki here:

http://wiki.bestpractical.com/view/DatabaseIndexes

Cheers,
Dominic.

Dominic Hargreaves, Systems Development and Support Team
Computing Services, University of Oxford

signature.asc (197 Bytes)