Really odd.
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 <[EMAIL PROTECTED]>
> Subject: Re: [rt-users] RT running slowly... Caused by RTx-Shredder
> indexes
> To: rt-users <[email protected]>
> Message-ID: <[EMAIL PROTECTED]>
> 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:[EMAIL PROTECTED]
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
