[rt-users] extremely slow rt response, query hanging

[Harald Wagener]

Hello,
One of our users constructed a search on our RT 3.2.2 instance which  
is slowing the system down quite a lot:

SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions  
Transactions_1, Attachments Attachments_2  WHERE ((main.EffectiveId =  
main.id)) AND ((main.Status != 'deleted')) AND ((main.Type =  
'ticket')) AND ((main.Subject LIKE '%Performance%')OR  
( (Attachments_2.Content LIKE '%Performance%')AND 
(Attachments_2.TransactionId = Transactions_1.id)AND(main.id =  
Transactions_1.Ticket) ) )

I have the feeling that 'something' in there is less than ideal, and  
would like to get some input by you folks as to what can be done to  
speed that up (I found two queries like this hogging the cpu for 200  
hours.

The database is MySQL 4.0.24. I updated DBIx::Searchbuilder to  
version 1.33 from 1.27, with no difference in results. The database  
contains roughly 2400 tickets.

Any help is appreciated.

Regards,
     Harald

-- 
Harald Wagener
Technischer Leiter

Foote Cone & Belding
FCB Wilkens
An der Alster 42
20099 Hamburg
Germany

T: +49 (0)40 2881 1252
F: +49 (0)40 2881 1217
hwagener at hamburg.fcb.com
http://www.footeconebelding.de