Time > Boiled down[#], I've got a table MYTABLE with an integer column > MYCOLUMN with an index on it, and I'm looking at queries like > > (a) select COUNT(*) from MYTABLE where MYCOLUMN < 2 > (b) select COUNT(*) from MYTABLE where MYCOLUMN > 2 > > The vast majority of rows in the table have MYCOLUMN = 2, so I'm looking at > these queries to return very small numbers very quickly. (MYCOLUMN = > 2 means "this object is OK", and I'm looking to find the objects that are not > OK.) > > If the index is ASC then > > (a) is fast > (b) is very slow (although it claims to be using the index, according to the > plan, it's doing as much work as a table scan, according to the stats) > > If the index is DESC then > > (a) is a bit slower but still fast > (b) is fast. > > Questions: > > (1) So it seems that if I want to do a ">" comparison in the WHERE clause I'm > better off with a DESC index, but if I'm wanting to do a "<" > comparison there isn't the same need to use an ASC index?
Correct! > (2) How does this indexing work anyway, that makes it sensitive to direction? > - from other databases I'm sort-of vaguely used to the idea (without having > got into the source code) that an index is a tree structure, and having walked > down it to a particular node there's no different cost to going left (DESC) or > right (ASC) from there? Firebird is only able to walk indexes in one direction, due to prefix compression within the index structures, which is why ASC/DESC matters. > [#] The real cases are rather more complicated. I haven't actually re-run the > experiments on this cut-down scenario so don't know for sure that it will > behave in the same way. In particular the real index is on multiple columns, > of which the last is the interesting one. Yes I know that multiple column > indices aren't exactly encouraged in Firebird... That is not completely true. There are plenty of cases where multi-column indexes are an excellent solution, much better than depending on the use of separate single column indexes. It really depends on your data usage/access modalities. Sean
