Boo wrote:
Can someone explain to me why this simple query will not use an index
on the field confirm_date?
select * from comments where confirm_date != 0
MySQL's optimizer tries to make an informed guess at whether using an
index would result in a greater benefit than the extra cost of reading
the index itself.
In cases like your query, the optimizer might reasonably assume that
rows matching confirm_date != 0 is going to match such a high percentage
of the rows in the table that the "wasted" effort of reading a few extra
rows from the data file and discarding them will turn out to be less
than the cost of reading the index file in addition to the data file.
The optimizer isn't strictly guaranteed to make the correct decision,
but in the great majority of cases it seems to be on target.
Regards,
Bill K.