[rt-users] RT running slowly...

[rgonzalez at gnt.cc]

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 # User at Host: 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.