List:General Discussion« Previous MessageNext Message »
From:Scott Gifford Date:June 14 2005 8:01pm
Subject:Re: Slow query: optimizer ignores index, using filesort
View as plain text  
Scott Gifford <sgifford@stripped> writes:

[...]

> I think I'm going to take a look at the MySQL source and see if
> there's anything I can tweak to get the effect I want.  I'll report
> back my results.

The MySQL source looked a bit too complex for casual hacking, but
here's what I ended up doing.  When I'm JOINing a table to the primary
table, if there are no columns in the table which are used in the
WHERE or ORDER BY clause, then I do a LEFT JOIN instead of a regular
JOIN.  That seems to stop MySQL from choosing bad plans in the cases
that are common for my application, since in most cases WHERE and
ORDER BY use only columns from the primary table, so the table with
the ORDER BY will end up first, so the index can be used.

---ScottG.
Thread
Slow query: optimizer ignores index, using filesortScott Gifford13 Jun
  • Re: Slow query: optimizer ignores index, using filesortJigal van Hemert13 Jun
    • Re: Slow query: optimizer ignores index, using filesortScott Gifford14 Jun
  • Re: Slow query: optimizer ignores index, using filesortJigal van Hemert14 Jun
    • Re: Slow query: optimizer ignores index, using filesortScott Gifford14 Jun
      • Re: Slow query: optimizer ignores index, using filesortScott Gifford14 Jun