Re: Statistics used by optimizer.
Posted by:
Rick James
Date: September 01, 2009 09:59PM
A possible answer...
In drilling down the BTree to find const1 (or const2), it gets some feel for what percentage of the BTree nodes INDEX(x) are to the left of the path it is traversing. If that is < 30%, it will use the index; if > 30%, it won't bother with the index. Instead it will use a table scan.
OTOH, if x is a "clustered key" (ie, PRIMARY KEY for InnoDB), the index and the data are one and the same. So it will scan the data/PK from beginning up to const1 (or const2).
OTOOH, your example is rather simplistic -- note that the only columns mentioned are (x). So, it will do the entire query in the index. Think of any index as being a BTree-ordered table. And BTrees are easily walked through (forward or backward). If the query can be resolved by scan of the index, that's always (well, almost always) faster than any other approach.
SELECT x, y FROM t1 WHERE x<const1
would invalidate this optimization, unless you had
INDEX(x,y)
or even (in this case)
INDEX(y,x)
or even
INDEX(z,y,x)
This last one _may_ be less inefficient than scanning the table -- the entire index would have to be scanned, and
(1) the index could be bigger than the data
(2) a BTree is probably less efficient to scan thru than the data.