--- In firebird-support@yahoogroups.com, <firebird-support@yahoogroups.com> wrote:

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