A little follow up on the explains:
here is the query as run when displaying a ticket:
mysql> EXPLAIN SELECT DISTINCT main.* FROM Users main JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3 ON ( Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE (Principals_1.Disabled = ‘0’) AND (Principals_1.id != ‘1’) AND (Principals_1.PrincipalType = ‘User’) AND ((Groups_3.Domain = ‘RT::Queue-Role’ AND Groups_3.Instance = ‘3’) OR (Groups_3.Domain = ‘RT::System-Role’)) AND (Groups_3.Type = ‘AdminCc’) 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 | 316 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | Groups_3 | ref | PRIMARY,Groups1,Groups2 | Groups2 | 67 | const | 4000 | Using where; Distinct |
| 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY | PRIMARY | 4 | rt3.main.id | 1 | Using where; Distinct |
| 1 | SIMPLE | CachedGroupMembers_2 | ref | DisGrouMem,CachedGroupMembers3 | DisGrouMem | 10 | rt3.Groups_3.id,rt3.Principals_1.id | 1 | Using where; Using index; Distinct |
4 rows in set (0.00 sec)
here is the modified removing the OR
mysql> EXPLAIN SELECT DISTINCT main.* FROM Users main JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3 ON ( Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE (Principals_1.Disabled = ‘0’) AND (Principals_1.id != ‘1’) AND (Principals_1.PrincipalType = ‘User’) AND ((Groups_3.Domain = ‘RT::Queue-Role’ AND Groups_3.Instance = ‘3’)) AND (Groups_3.Type = ‘AdminCc’) ORDER BY main.Name ASC;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | Groups_3 | ref | PRIMARY,Groups1,Groups2 | Groups1 | 139 | const,const,const | 1 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | main | range | PRIMARY | PRIMARY | 4 | NULL | 316 | Using where |
| 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY | PRIMARY | 4 | rt3.main.id | 1 | Using where; Distinct |
| 1 | SIMPLE | CachedGroupMembers_2 | ref | DisGrouMem,CachedGroupMembers3 | DisGrouMem | 10 | rt3.Groups_3.id,rt3.main.id | 1 | Using where; Using index; Distinct |
4 rows in set (0.00 sec)
index listing from Principals:
mysql> show index from Principals;
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
| Principals | 0 | PRIMARY | 1 | id | A | 22309 | NULL | NULL | | BTREE | NULL |
| Principals | 1 | Principals2 | 1 | ObjectId | A | 22309 | NULL | NULL | YES | BTREE | NULL |
2 rows in set (0.00 sec)
not sure if this helps tracking it down… still having figured out what the Groups_3.Domain = ‘RT::System-Role’…
NicolaFrom: rt-users-bounces@lists.bestpractical.com on behalf of Foggi, Nicola
Sent: Fri 6/25/2010 11:07 AM
To: Ruslan Zakirov
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] slow mysql query after upgrade from 3.8.6 to 3.8.8
UseSQLForACLChecks is set as the default under RT_Config.pm to “Set($UseSQLForACLChecks, undef);”
here is the explain:
EXPLAIN SELECT DISTINCT main.* FROM Users main JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3 ON ( Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE (Principals_1.Disabled = ‘0’) AND (Principals_1.id != ‘1’) AND (Principals_1.PrincipalType = ‘User’) AND ((Groups_3.Domain = ‘RT::Queue-Role’ AND Groups_3.Instance = ‘3’) OR (Groups_3.Domain = ‘RT::System-Role’)) AND (Groups_3.Type = ‘AdminCc’) 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 | 316 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | Groups_3 | ref | PRIMARY,Groups1,Groups2 | Groups2 | 67 | const | 3992 | Using where; Distinct |
| 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY | PRIMARY | 4 | rt3.main.id | 1 | Using where; Distinct |
| 1 | SIMPLE | CachedGroupMembers_2 | ref | DisGrouMem,CachedGroupMembers3 | DisGrouMem | 10 | rt3.Groups_3.id,rt3.Principals_1.id | 1 | Using where; Using index; Distinct |
4 rows in set (0.00 sec)
here is the indexes:
mysql> show index from Groups;
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
| Groups | 0 | PRIMARY | 1 | id | A | 21563 | NULL | NULL | | BTREE | NULL |
| Groups | 1 | Groups1 | 1 | Domain | A | 31 | NULL | NULL | YES | BTREE | NULL |
| Groups | 1 | Groups1 | 2 | Instance | A | 21563 | NULL | NULL | YES | BTREE | NULL |
| Groups | 1 | Groups1 | 3 | Type | A | 21563 | NULL | NULL | YES | BTREE | NULL |
| Groups | 1 | Groups1 | 4 | id | A | 21563 | NULL | NULL | | BTREE | NULL |
| Groups | 1 | Groups2 | 1 | Type | A | 10 | NULL | NULL | YES | BTREE | NULL |
| Groups | 1 | Groups2 | 2 | Instance | A | 21563 | NULL | NULL | YES | BTREE | NULL |
7 rows in set (0.00 sec)
I did find in some trial and error testing if I drop the " OR (Groups_3.Domain = ‘RT::System-Role’)" from the query it gives the same results in under 1 second vs 11-12 seconds, but bot sure what that OR is adding into the mix. That query is what is performed when I display a ticket through the web interface.
Any help is appreciated!
Nicola
From: ruslan.zakirov@gmail.com on behalf of Ruslan Zakirov
Sent: Fri 6/25/2010 11:05 AM
To: Foggi, Nicola
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] slow mysql query after upgrade from 3.8.6 to 3.8.8
Hello,
Do you use SQLForACLChecks option?
Where is EXPLAIN for this query?
Show indexes from Groups table.