List:General Discussion« Previous MessageNext Message »
From:Scott Gifford Date:June 14 2005 5:58am
Subject:Re: Slow query: optimizer ignores index, using filesort
View as plain text  
Thanks for your response, Jigal.  More below...

"Jigal van Hemert" <jigal@stripped> writes:

> From: "Scott Gifford"

[...]

>> Apparently MySQL's optimizer sees that it can use the primary key for
>> mirealsource_home_supplemental to do the query, but for some reason
>> decides not to.
>
> This is often the case when the query will probably return more than 30% of
> the records in that table. In such cases it is more efficient to do a full
> table scan (which is indicated here by the 'ALL' type in the explain
> output).

Right, ALL would be a great plan if it weren't for the LIMIT 1.  

>> I can fix the problem by requiring a STRAIGHT_JOIN, or by sorting by
>> "mirealsource_homes_supplemental.mls_num" instead.  However, this
>> query is part of a larger framework that handles a wide variety of
>> queries, so I need to understand why this is happening instead of
>> tweaking individual cases.
>
> Furthermore MySQL can only use an index for sorting if all columns in the
> ORDER BY clause are from the first table in the explain output that doesn't
> have a 'const' join type. This is why setting the ORDER BY to
> mirealsource_homes_supplemental.mls_num will remove the 'Using filesort' and
> result in faster sorting.

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.

> MySQL tries to optimize queries by (among others) guestimating which path
> will result in the smallest number of records. It appears that this path
> with 100 * 8 * 8 * 8 (51200) records is the minimum size it can achieve.

It looks to me like it's actually 100 * 1 * 1 * 1 = 100 (8 is the key
length), 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.

> You can use USE INDEX to (strongly) suggest the use of an index to MySQL and
> see if it speeds up the query. Using  FORCE INDEX will tell MySQL that a
> full table scan is very expensive, so this will make it extremely unlikely
> that it will not use the index.

I know there are a couple of tricks like that to fix this one query.
What makes this hard is that that query is one of many that can be
generated by a CGI-to-SQL search gateway.  If I put in a FORCE INDEX
(mls_num) and the user searches by price, for example, query
performance will be very bad.  In order to know the right indexes to
force, as far as I can tell I'd have to implement my own optimizer,
which seems somewhat excessive.

[...]

> In this query you want the data where mls_num is as small as possible. Is
> there a way you can limit the number of records by using an extra where
> condition? This way you may change the order of the tables and make the
> query faster.

I tried that using mirealsource_homes.mls_num in the WHERE clause and
it didn't make a difference.

> I would also move the current WHERE condition to an ON condition in the FROM
> part as it is not meant to limit the selected records, but as a definition
> on how to join the two tables.

I tried that too, and it didn't make a difference (though I agree it
is clearer).

I also tried installing a copy of 4.1.12-standard to see if the
situation was better, but the results were identical.

And, I tried setting "max_seeks_for_key" to a low number (I tried 100,
10, 1, and 0), which is supposed to discourage tables scans, and that
didn't help.

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

----Scott.

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