[rt-users] Ludicrously slow queries on custom fields

[Tony Bowden]

On Mon, Oct 16, 2006 at 02:40:49PM -0400, Jesse Vincent wrote:
> >   Tickets LEFT JOIN TicketCustomFieldValues 
> >     ON TicketCustomFieldValues.CustomField = '1' 
> >     OR TicketCustomFieldValues.Ticket = Tickets.id
> 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.

Yeah - upgrading has been on the todo list for quite a while, but we're
not sure how easy it'll be so it keeps getting put off.

> > 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.

Not sure what we were at, but we're now on 5.0.24

In further investigation we found a line in the DBIx::SearchBuilder
Changes file from earlier this year that says:

  * Allow ORs on left joins

It's conceivable that that was upgraded with mysql - is it possible that
this change might have made the old 3.2.2 query work differently? 

Tony