[rt-users] Very slow query in RT 3.4.1

[Marc Prud'hommeaux]

RT Users-

We're using RT 3.4.1 with MySQL 4.0.21 with perl 5.8.6 on Linux (kernel  
2.2.20).

We recently upgraded from RT 3.0.0 to 3.4.1 in the hopes that it would  
speed up our ever-slowing-down system. A number aspects of the system  
did indeed seem to speed up, but replying to a ticket (hitting the  
"Reply" link from Display.html) has become unbearably slow. Looking in  
my "mysql-slow.log" log, I see that the main offending query is as  
follows:

SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL  
ACL_2, Groups Groups_3, CachedGroupMembers CachedGroupMembers_4  WHERE  
((ACL_2.RightName = 'OwnTicket')) AND ((CachedGroupMembers_4.MemberId =  
Principals_1.id)) AND ((Groups_3.id = CachedGroupMembers_4.GroupId))  
AND ((Principals_1.Disabled = '0')or(Principals_1.Disabled = '0')) AND  
((Principals_1.id != '1')) AND ((main.id = Principals_1.id)) AND  ( (    
  ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType = 'Group' AND  
(   Groups_3.Domain = 'SystemInternal' OR Groups_3.Domain =  
'UserDefined' OR Groups_3.Domain = 'ACLEquivalence')) OR ( (  
(Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance = 5)  OR (  
Groups_3.Domain = 'RT::Ticket-Role' AND Groups_3.Instance = 43004)  )   
AND Groups_3.Type = ACL_2.PrincipalType) )  AND (ACL_2.ObjectType =  
'RT::System' OR  (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId =  
5) ) ORDER BY main.Name ASC;

If I manually run the query, it takes around 50 seconds to complete. If  
I run an EXPLAIN on it, I see:

+----------------------+-------- 
+----------------------------------------------------------------------- 
------------------------------+----------+--------- 
+------------------------------+------- 
+-----------------------------------------------------------+
| table                | type   | possible_keys                          
                                                               | key      
  | key_len | ref                          | rows  | Extra                
                                       |
+----------------------+-------- 
+----------------------------------------------------------------------- 
------------------------------+----------+--------- 
+------------------------------+------- 
+-----------------------------------------------------------+
| ACL_2                | range  |  
ACL1,RightName,PrincipalType,ObjectId,ObjectId_2,ObjectType,PrincipalId, 
PrincipalType_2,RightName_2 | ACL1     |      54 | NULL                  
         |     3 | Using where; Using index; Using temporary; Using  
filesort |
| main                 | ALL    | PRIMARY,Users3,Users_id_idx_mwp        
                                                               | NULL     
  |    NULL | NULL                         | 35305 |                      
                                       |
| Principals_1         | eq_ref | PRIMARY,Disabled,id                    
                                                               | PRIMARY  
  |       4 | main.id                      |     1 | Using where;  
Distinct                                     |
| CachedGroupMembers_4 | ref    | DisGrouMem,GrouMem,MemberId            
                                                               |  
MemberId |       5 | Principals_1.id              |     1 | Using  
where; Distinct                                     |
| Groups_3             | eq_ref |  
PRIMARY,Groups1,Groups2,Type,Domain,id,Instance,Type_2                   
                             | PRIMARY  |       4 |  
CachedGroupMembers_4.GroupId |     1 | Using where; Distinct             
                          |
+----------------------+-------- 
+----------------------------------------------------------------------- 
------------------------------+----------+--------- 
+------------------------------+------- 
+-----------------------------------------------------------+


Does anyone know of any quick fix for this? Creating a new index or  
something? I've seen a number of people report the problem on the list  
in the past, but I couldn't find any satisfactory solution.


A second question: is the file rt-3.4.1/etc/constraints.mysql meant to  
be used? It looks like it might contain some useful foreign key and  
index definitions, but it never appears to be automatically called, and  
I can't find any reference to the file in any documentation anywhere.  
Is it experimental, or can it be applied to an existing RT schema?

--
Marc Prud'hommeaux
SolarMetric Inc.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 2108 bytes
Desc: not available
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20050404/1a79b9de/attachment.bin>