[rt-users] Ludicrously slow queries on custom fields

[Tony Bowden]

Our RT installation has recently gotten amazingly slow, most obviously
where it's dealing with custom fields.

Some of the biggest culprits are rt_tidy snippets that we (used to!) run
from cron, although many of the standard queries on the web interface
have become unacceptably slow.

After investigating a set of particularly problematic queries that were
each taking 40 minutes(!) to run we noticed a very peculiar clause in
the query, which basically boils down to a select on

  Tickets LEFT JOIN TicketCustomFieldValues 
    ON TicketCustomFieldValues.CustomField = '1' 
    OR TicketCustomFieldValues.Ticket = Tickets.id

There are other tables involved as well, but this 'OR' seems to wipe
out all hope of sensibly using indexes, and results an a 1.4 billion
row temporary table being created. 

Unless I'm missing something important about how this should work, this
should surely be an AND, not an OR. And, indeed, replacing the OR with
an AND and running the query by hand gets back the correct results in a 
fraction of a second.

Also, some queries on custom fields appear to have recently stopped
working altogether in that they now return every ticket that meets the
other criteria - not restricting by the custom field at all. 

This appears to have happened after we upgraded mysql. We didn't upgrade
any direct RT things with this, but perhaps something else underneath
has changed in an unexpected way, or MySQL has changed its use of
indexes on certain types of queries or something? (Mostly I'm at a loss
as to what could have caused this and just scrambling here)

I can't find anything obvious in the list history or on the wiki about
this. (We've added the suggested index to the table, but that doesn't seem
to help, and even if it did, it appears it would be just papering over
a deeper problem.) I'm assuming that other people would have encountered
the problem if it was something common, so perhaps this has already been
fixed in a recent version? We're still running RT 3.2.2, and although
we are planning on upgrading, it's not something we were expecting to
do just yet. We have about 45,000 tickets over about 15 queues, which I
wouldn't have thought was a particularly huge installation.

Anyone any ideas, or pointers to anything I've missed?

Thanks,

Tony