MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Scott Gifford Date:June 14 2005 5:37pm
Subject:Re: Slow query: optimizer ignores index, using filesort
View as plain text  
"Jigal van Hemert" <jigal@stripped> writes:

> From: "Scott Gifford"

[...]

>> Right, ALL would be a great plan if it weren't for the LIMIT 1.
>
> The LIMIT 1 will be performed *after* the recordset is sorted :-(

Ah, I think that is the piece I was missing.

[...]

>> I'm a little surprised MySQL can't figure out on its own that it can
>> get the same effect by using mls_num from either table, since the
>> tables are joined on it, so the values will always be identical.
>
> You have two LEFT JOINs, so the values of mls_num might be something (the
> identical value you refer to) or NULL. So, you expect MySQL to evaluate the
> JOIN conditions, figure out that the ORDER BY column actually is the same as
> columns from other tables and see if one of these columns is actually the
> first non-const table in the execution path?

Of course with the LEFT JOINs it can be difficult to figure out
(though in this case the JOINs are on mls_num, so it will never be
NULL), but with two tables were using a "regular" join on the column
in question, it does seem like a straightforward optimization.  Maybe
it's not common enough in practice to justify implementing it (or
maybe I'm missing something).

[...]

>> which is the same as what I get when I force a STRAIGHT_JOIN.
>> So with two plans that will use the same number of records, I'm
>> surprised MySQL doesn't choose the one that will allow it to use an
>> index.
> There is no limiting condition whatsoever in the WHERE clause (except the
> JOIN condition), so it will try to estimate based on the cardinality and the
> number of records in the table which excecution path will require it to
> evaluate the smallest number of records. It will try to estimate whether the
> use of an index is appropriate or not. But I can imagine that it will favour
> a full table scan of a small table that will result in a small recordset,
> especially when it knows that the other, bigger tables are JOINed with only
> 1 or a few records per join.

In this case, the two tables have identical cardinality, so it
shouldn't be favoring one over the other.

[...]

> This is part of a routine that generates many different queries based on a
> wide variety of search forms, predefined selections, etc. Most of the time
> MySQL does a great job with finding the 'right' execution path, but
> sometimes it goes horribly wrong.

That's been my experience too; we've been running this application for
about 5 years, and this is only the second time something like this
has come up.

[...]

>> I tried that using mirealsource_homes.mls_num in the WHERE clause and
>> it didn't make a difference.
>
> I tried to say that you could/should try to limit the number of records that
> are selected in the first place. 
[...]
> So, can you think of a limitation that would preselect a part of the
> mirealsource_homes.mls_num table?

Unfortunately, not in the general case.

>> Are there any other global things I can try, to tell MySQL to avoid
>> table scans?  The queries almost always use at most LIMIT 10, so a
>> table scan is generally not the right idea
>
> A full table scan is the right idea as long as you select more than 30% of
> the table, sort it and after that apply the limit 10 (which is the only
> solution for such a query).

Right, but our queries always use a LIMIT 10 or LIMIT 1, so a full
table scan is never a good deal if an index is available.  I was
hoping for some way to communicate that to MySQL.

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.

Thanks for all your help!

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