[rt-users] Ludicrously slow queries on custom fields

[Jesse Vincent]

On Mon, Oct 16, 2006 at 07:06:58PM +0100, Tony Bowden wrote:
> 
> 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
> 
> 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.

That sure doesn't look right, but 3.2.2 was a _long_ time ago. 3.4
entirely reimplemented the custom fields searching, and 3.6 further
improved queries on customfields.

> 
> This appears to have happened after we upgraded mysql. 

From what to what? We've seen pathalogically bad query optimization on
5.0. But still, that query above just doesn't look right.

-j