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.
Make sense?
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.
Thanks.
--
David Hillman
LiveText, Inc
1.866.LiveText x235
