In the last episode (Jun 10), Erv Young said:
> MySQL AB staff:
And the thousands and thousands of regular people reading this list :)
> 1) The recent discussion on the subject "Not using indexes???"
> brought to light an opportunity for interpreting the documentation in
> contradictory ways. The page
> http://www.mysql.com/doc/M/y/MySQL_indexes.html does not mention the
> IS NOT NULL comparison operator, nor does it mention the inequality
> (<>) operator.
> This allows the reader to believe that since those operators are not
> mentioned, it goes without saying that they never use an index. It
> also allows the reader to believe that since each is simply the
> negation of a comparison whose index use is specifically documented,
> it goes without saying that they also use an index.
Indexes are only used if they significantly reduce the number of reads
required vs a full table scan (I believe 30% is mysql's threshhold).
An IS NOT NULL clause may very well use an index if almost all of the
records have NULL in that particular field. A != clause is a bit
harder, since that basically devolves into "field < ## OR field > ##",
and I don't think can optimize that.