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