List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:October 27 2006 4:50pm
Subject:Re: Optimizer Bug?
View as plain text  
In the last episode (Oct 27), David Hillman said:
> On Oct 25, 2006, at 5:32 PM, Dan Buettner wrote:
> >My understanding of what is happening here is this:
> >
> >The 'rows' column of EXPLAIN output is an estimate of how many rows
> >MySQL thinks it will likely have to examine in a table to get your
> >answer.  When there's an index, it will hopefully be able to use
> >that to exmaine a small subset of the rows in the table.
> >
> >Problem here is, MySQL thinks it will have to examine 1463 of 1950
> >rows.  At that point (or any point higher than about 30%) MySQL will
> >decide that a table scan may be faster.  Hence the decision to not
> >use the d_id index.
> >
> >When you drop the index, MySQL can no longer plan to eliminate any
> >rows using an index, so it knows up front it will have to do a table
> >scan, giving you the 1950 answer for the table with no d_id index.
> >
> >What's probably happening is that you have a large grouping of the
> >5098 number in your data, based on a quick read of your query.
>    I guess that makes sense.  It's not very obvious, and arguably
> wrong, that the "type" and "rows" columns in the EXPLAIN output are
> not necessarily referring to the same scenario.  Apparently, "type"
> always refers to what /will/ happen, and "rows" refers to how many
> rows /might/ be looked at.

MySQL is just giving you as much information as it can without actually
running the query.  It knows how it will go about running the query (so
"type" is known absolutely), but it doesn't know exactly what it will get
(so "rows" is only a guess).  Nothing wrong with that.

	Dan Nelson
Optimizer Bug?David Hillman25 Oct
  • Re: Optimizer Bug?Dan Buettner26 Oct
    • Re: Optimizer Bug?David Hillman27 Oct
      • Re: Optimizer Bug?Dan Nelson27 Oct
        • Re: Optimizer Bug?David Hillman27 Oct
          • Re: Optimizer Bug?Dan Nelson27 Oct