Archive for April 2nd, 2009
In the previous article I wrote about ranged conditions (
BETWEEN) on one field combined with
ORDER BY another field.
We have to choose an index either for filtering or for sorting, as a single index cannot be used for both purposes.
Well, actually, it can.
There is a special access method called
SKIP SCAN, designed just for these cases. This method can be used when a filtering condition applied to a secondary column of a composite index. Like, we have and index on
(orderer, ranger) and search for
ranger < 10.
This method scans the index bottom to top, selecting each possible
orderer values. For each
orderer, it applies the filtering condition on
ranger, thus obtaining a valid
range condition on the index that can be iterated. As soon and this range is iterated, the method defines the range for the next
orderer, skipping all other values (hence the name).
SKIP SCAN does not perform as well as a simple index scan, but a query can benefit from it is there are few
orderer‘s in the table.
Oracle supports this method directly, but
MySQL, of course, not.
SKIP SCAN, though, can be easily emulated.
Read the rest of this entry »