Retrieved columns affects index usage
Posted by:
Angus
Date: February 13, 2007 12:06PM
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 .
Subject
Views
Written By
Posted
Retrieved columns affects index usage
2666
February 13, 2007 12:06PM
1941
February 13, 2007 06:37PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.