From: Dan Nelson
Date: June 11 2002 3:19am
Subject: Re: MySQL AB:  Need documentation clarification
List-Archive: http://lists.mysql.com/mysql/111544
Message-Id: <20020611031925.GB15990@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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.

-- 
	Dan Nelson
	dnelson@stripped