Retrieved columns affects index usage

[Angus]

I was surprised to find EXPLAIN calls on this SELECT I'm doing causes indexes to be used differently. The table has 2 (among other) columns both of which are varchar(50). I wasn't sure what to think when the following did table scans, without even considering an index:

SELECT AColumn,UniqueColumn FROM ATABLE WHERE AColumn!=''

AColumn has a non-unique index attached to it.
The following considered an index, but went with a table scan anyway:

SELECT AColumn,UniqueColumn FROM ATABLE WHERE NOT (AColumn='')

So I was completely bewildered when I found that:

SELECT AColumn FROM ATABLE WHERE NOT (AColumn='')

went to a "range". Since when do the retrieved columns affect the WHERE performance?

ATABLE is InnoDB and the server is 4.1.

I should point out that this table scan is not the result of a relatively large result set. AColumn!='' returns a result set that is <1/3 of ATable's size. Futhermore, SELECT AColumn,UniqueColumn FROM ATABLE WHERE AColumn='' which obviously returns >2/3 of ATable's size *does* use the index on AColumn.

Finally, a single index made out of AColumn and UniqueColumn was used, after I created it, but the SELECT that I'm really trying to optimize is of the form:

SELECT AColumn,UniqueColumn FROM ATable WHERE AColumn!='' AND bool1=0 AND bool2=1

The EXPLAIN for that said the index used was on bool1 and bool2, which represents a larger result set than an index just on AColumn. Should I be using FORCE INDEX or something?



Edited 1 time(s). Last edit at 02/13/2007 12:45PM by Angus .