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

>
> Hi All,
>
> A trivial example (FB 1.5.4):
>
> CREATE TABLE T (Foo VARCHAR(20), Bar INTEGER);
>
> SELECT * FROM T
> ORDER BY Foo DESC, Bar
>
> How can I create an index for this query? (yes, I want that Foos are
> descending order and Bars are ascending order)
>
> CREATE INDEX IDX_1 ON T (Foo DESC, Bar)
>
> Statement failed. :(
> But,
>
> CREATE INDEX IDX_1 ON T (Foo DESC)

No it didn't ;)

CREATE DESC INDEX IDX_1 ON T (Foo);

Would have worked.

> CREATE INDEX IDX_2 ON T (Bar)
>
> Statements are OK, but the query is don`t use these indexes. :(

You are using a query without a where clause. The optimiser thinks it
is quicker to read the data into memory in the order it is stored in
the database, then do an in-memory sort, than to read an index into
memory, then thrash around the hard disk looking for the various records.

Indexed reads are generally slower than sorting natural reads on disk
technology. Hard drives have terrible seek time (milliseconds) but
reasonable throughput.

When flash becomes more popular in hard drive technology, I imagine
this assumption needs to be reconsidered, as the seek time is measured
in nanoseconds and the storage order throughput is slower, so the
pendulum swings the other way.

Adam