On Thu, 22 May 2008, David Hobley wrote: > 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;
Using DISTINCT with MySQL is bad thing. It could even freeze the engine. You could use FORCE INDEX or USE INDEX to force MySQL to use some index of that table, that usually solves the problem. Hope this helps. _______________________________________________ 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
