I've noted that the documentation says that whether your index is ASC or DESC matters, but it's not clear to me either why it should or exactly what the implications are.
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? (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? [#] 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; at this stage I'm doing experiments, not writing production code. -- Tim Ward
