<html><head><style type='text/css'>p { margin: 0; }</style><style type='text/css'>body { font-family: 'Times New Roman'; font-size: 12pt; color: #000000}</style></head><body>All,<br><br>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.<br><br>In case this is useful:<br><br>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;<br>+----+-------------+----------------------+--------+--------------------------+---------------+---------+-----------------------------+-------+----------------------------------------------+<br>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br>+----+-------------+----------------------+--------+--------------------------+---------------+---------+-----------------------------+-------+----------------------------------------------+<br>| 1 | SIMPLE | main | range | PRIMARY | PRIMARY | 4 | NULL | 28920 | Using where; Using temporary; Using filesort | <br>| 1 | SIMPLE | Groups_3 | range | PRIMARY,Groups1,Groups2 | Groups1 | 67 | NULL | 119 | Using where; Using index; Distinct | <br>| 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY | PRIMARY | 4 | rt3.main.id | 1 | Using where; Distinct | <br>| 1 | SIMPLE | CachedGroupMembers_2 | ref | DisGrouMem,SHREDDER_CGM1 | SHREDDER_CGM1 | 10 | rt3.main.id,rt3.Groups_3.id | 1 | Using where; Using index; Distinct | <br>| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 45 | Using where; Using index; Distinct | <br>+----+-------------+----------------------+--------+--------------------------+---------------+---------+-----------------------------+-------+----------------------------------------------+<br><br>Cheers,<br>David<br>----- Original Message -----<br>From: "David Hobley" <david.hobley@mionegroup.com><br>To: "rt-users" <rt-users@lists.bestpractical.com><br>Sent: Thursday, 22 May, 2008 3:30:21 PM GMT +10:00 Brisbane<br>Subject: [rt-users] RT running slowly...<br><br><style>p { margin: 0; }</style><style>body { font-family: 'Times New Roman'; font-size: 12pt; color: #000000}</style>Hello,<br><br>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?<br><br># Time: 080522 15:32:21<br># User@Host: rt[rt] @ localhost []<br># Query_time: 40 Lock_time: 0 Rows_sent: 0 Rows_examined: 6888927<br>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;<br><br>-- <br>Cheers,<br>David<br><br>_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com<br><br>-- <br>Cheers,<br>David Hobley<br><br>IT Manager<br>Creators of Miessence, MiVitality and MiEnviron<br><br>Phone: +61 (7) 5582 7020<br>Fax: +61 (7) 5539 6719<br>USA Fax 1800 840 0827<br>Email : david.hobley@mionegroup.com<br>Website: www.mionegroup.com<br><br><br></body></html>