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 ( 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) 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 ((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 ----- Original Message ----- From: "David Hobley" <[EMAIL PROTECTED]> To: "rt-users" <[email protected]> Sent: Thursday, 22 May, 2008 3:30:21 PM GMT +10:00 Brisbane Subject: [rt-users] RT running slowly... Hello, Our RT has been running slowly for a while; having turned on mysql slow query logging, it appears to only be a single query which is causing grief. Anyone any suggestions as to what can be done to fix this? # Time: 080522 15:32:21 # [EMAIL PROTECTED]: rt[rt] @ localhost [] # Query_time: 40 Lock_time: 0 Rows_sent: 0 Rows_examined: 6888927 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 ( 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) 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 ((Groups_3.Domain = 'RT::Queue-Role') OR (Groups_3.Domain = 'RT::System-Role')) ORDER BY main.Name ASC; -- Cheers, David _______________________________________________ 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 -- Cheers, David Hobley IT Manager Creators of Miessence, MiVitality and MiEnviron Phone: +61 (7) 5582 7020 Fax: +61 (7) 5539 6719 USA Fax 1800 840 0827 Email : [EMAIL PROTECTED] Website: www.mionegroup.com
_______________________________________________ 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
