After adding this key:
ALTER TABLE CachedGroupMembers Add KEY SHREDDER_CGM1 (GroupId,MemberId,Disabled);
- MemberId & GroupId swapped comparing to original SHREDDER_CGM1 - explain (and query too
) is OK.
mysql> explain extended SELECT DISTINCT main.* FROM Users main
mysql> CROSS JOIN ACL ACL_4
mysql> JOIN Principals Principals_1 ON ( Principals_1.id = main.id )
mysql> JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id )
mysql> JOIN Groups Groups_3 ON ( Groups_3.id = CachedGroupMembers_2.GroupId )
mysql> WHERE (Principals_1.Disabled = ‘0’) AND (ACL_4.PrincipalType = Groups_3.Type)
mysql> AND (Principals_1.id != ‘1’) AND (Principals_1.PrincipalType = ‘User’)
mysql> AND (ACL_4.RightName = ‘OwnTicket’) AND (Groups_3.Domain = ‘RT::System-Role’)
mysql> AND ((ACL_4.ObjectType = ‘RT::Ticket’ AND ACL_4.ObjectId = 147792) OR (ACL_4.ObjectType = ‘RT::Queue’ AND ACL_4.ObjectId = 10) OR (ACL_4.ObjectType = ‘RT::System’))
mysql> ORDER BY main.Name ASC;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | main | ALL | PRIMARY | NULL | NULL | NULL | 39464 | Using temporary; Using filesort |
| 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY | PRIMARY | 4 | rt3.main.id | 1 | Using where; Distinct |
| 1 | SIMPLE | CachedGroupMembers_2 | ref | DisGrouMem,SHREDDER_CGM1 | SHREDDER_CGM1 | 5 | rt3.Principals_1.id | 1 | Using where; Using index; Distinct |
| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 5 | Using where; Using index; Distinct |
| 1 | SIMPLE | Groups_3 | eq_ref | PRIMARY,Groups1,Groups2 | PRIMARY | 4 | rt3.CachedGroupMembers_2.GroupId | 1 | Using where; Distinct |
After dropping SHREDDER_CGM1:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | Groups_3 | ref | PRIMARY,Groups1,Groups2 | Groups1 | 65 | const | 4 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | CachedGroupMembers_2 | ref | DisGrouMem | DisGrouMem | 5 | rt3.Groups_3.id | 1 | Using where; Using index |
| 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY | PRIMARY | 4 | rt3.CachedGroupMembers_2.MemberId | 1 | Using where |
| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 5 | Using where; Using index |
| 1 | SIMPLE | main | eq_ref | PRIMARY | PRIMARY | 4 | rt3.Principals_1.id | 1 | |
SHREDDER_CGM1:
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 | 1 | SHREDDER_CGM1 | 1 | MemberId | A | 400337 | NULL | NULL | YES | BTREE | |
| CachedGroupMembers | 1 | SHREDDER_CGM1 | 2 | GroupId | A | 400337 | NULL | NULL | YES | BTREE | |
| CachedGroupMembers | 1 | SHREDDER_CGM1 | 3 | Disabled | A | 400337 | NULL | NULL | | BTREE | |
…
Message: 6
Date: Thu, 22 May 2008 16:08:27 +1000 (EST)
From: David Hobley david.hobley@mionegroup.com
Subject: Re: [rt-users] RT running slowly… Caused by RTx-Shredder
indexes
To: rt-users rt-users@lists.bestpractical.com
Message-ID: 10763813.80751211436507065.JavaMail.root@mail.onegrp.com
Content-Type: text/plain; charset=“utf-8”
All,
I ran an explain on the query in MySQL and noticed that it was
using one of the RTx-Shredder indexes. After I deleted all those
indexes things went back to normal. Phew.
In case this is useful:
mysql>> explain 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 (
mysql>> 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)
mysql>> AND (Principals_1.id != ‘1’) AND (Principals_1.PrincipalType = ‘User’) AND (ACL_4.RightName = ‘OwnTicket’) AND ((ACL_4.ObjectType = ‘RT::Queue’) OR (ACL_4.ObjectType = ‘RT::System’)) AND
mysql>> ((Groups_3.Domain = ‘RT::Queue-Role’) OR (Groups_3.Domain = ‘RT::System-Role’)) ORDER BY main.Name ASC;
±—±------------±---------------------±-------±-------------------------±--------------±--------±----------------------------±------±---------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±---------------------±-------±-------------------------±--------------±--------±----------------------------±------±---------------------------------------------+
| 1 | SIMPLE | main | range | PRIMARY | PRIMARY | 4 | NULL | 28920
| Using where; Using temporary; Using filesort |
| 1 | SIMPLE | Groups_3 | range | PRIMARY,Groups1,Groups2 | Groups1
| 67 | NULL | 119 | Using where; Using index; Distinct |
| 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY | PRIMARY | 4 |
rt3.main.id | 1 | Using where; Distinct |
| 1 | SIMPLE | CachedGroupMembers_2 | ref |
DisGrouMem,SHREDDER_CGM1 | SHREDDER_CGM1 | 10 |
rt3.main.id,rt3.Groups_3.id | 1 | Using where; Using index; Distinct |
| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 45 | Using where; Using index; Distinct |
±—±------------±---------------------±-------±-------------------------±--------------±--------±----------------------------±------±---------------------------------------------+
Cheers,
David
Best regards,
Boris Lytochkin mailto:boris.lytochkin@e-port.ru