[rt-devel] Slow queries, part 2, custom fields

[Iain Price]

Wow my users have a great sense of direction for finding slow queries - 
originally the multiple-requestor-search email which is now all nice in 
3.0.5, but now it seems searching for a single custom field is very very 
slow - mysql spent almost 5 min (280s) 'sending data' and another 5 min 
'copying to tmp table' on this

 SELECT DISTINCT main.* FROM ( Tickets main LEFT JOIN 
TicketCustomFieldValues as TicketCustomFieldValues_1  ON  
((TicketCustomFieldValues_1.CustomField = '1')) AND (  main.id = 
TicketCustomFieldValues_1.Ticket))  WHERE ((main.EffectiveId = main.id)) 
AND ((main.Type = 'ticket')) AND ( (  ( 
(TicketCustomFieldValues_1.Content = 'xxxxxxxxx') )  ) )  ORDER BY 
main.LastUpdated DESC LIMIT 50

seems the reason theres 2 x 5 min (sending data and the copying phase) 
relates to the count(*) which runs first (causing 'sending data' for 5 
min) which just counts the number of matches, then the actual search 5 
min later causes the 'copying to tmp table'........  unpleasant as it 
is, seems it might be worth doing the count() on the client over the 
results by hand rather than asking poor old mysql to do something :P

I've masked out the custom field value above as 'custom field 1' is 
location and contains a site address as the value...

I appreciate the failing is not in RT particularly, but more in the 
(seemingly worse and worse) handling of the query on mysql, bad analysis 
and all that... this one is nice because its much more readable to 
humans than the previous multi-requestor-email one...

Notably all this is only a big issue because RT locks the database 
during the query, hence preventing all other activity by other users RT 
sessions... i wondered before if this was alterable, I appreciate the 
concurrency issues and race conditions this implies, but if the display 
is written very defensively this might not have to be a problem - if the 
ticket is deleted, modified etc during display, no big worry (!) just 
make best effort to render something as and when you need to... things 
like ACLs changing during display - again no biggie, if they previously 
let you through then this is all still fine at display time, after all 
if someone opened the ticket a second before the ACL change then you 
cant revoke the display on their web browser that easily.... or is there 
a real complex issue at the heart of all this preventing you not-locking 
the database for a search?  Having thought about it tho i suspect my 
users might just resubmit the queries over and over again until the 
mysql server just becomes bogged down with a dozen of the same query 
(when i kill one defective one i still have to hang around watching for 
resubmissions of the query within the next 20 seconds, and shout at 
people to kill web browsers if necessary)...

I'm going to do some analysis on the query, play with tables etc....  
was there anything i should have upgraded in the DB from 3.0.2 to 3.0.5 
(i.e. indexes) that i missed?

Iain