--- In firebird-support@yahoogroups.com, "t.s." <truesaint@...> wrote:

>
> Dear all,
>
> In most transaction tables, there's usually a TRANS_DATE column/field
> containing the date of the transaction (duh! :). Usually i create an
> ASCENDING index on this field and leave it at that.
>
> But on some scenarios, we'd like to list the transactions in a
> descending order (i.e. newest one first). Will it be a good idea to
> create a DESCENDING index on the column ?

It will give the optimiser another option. At the moment, it must
perform a sort in memory (or disk) but if it had the descending index,
it could read the records in the order they should be returned.

Frankly, there is no point theorising about it though, it takes
seconds to write the create index statement, run some real world tests
against it. We don't really know enough about the data shape to tell
you either way, but if it is a pretty simple select statement with an
order by you will benefit.

> Is there any pitfalls that i
> should be aware of ?

None that don't exist with ascending indices. I imagine that a
transaction date field will have poor selectivity. You may want to
combine it as a superkey. If your descending order query has some
where condition, then that column would be a good candidate to use in
the superkey.

>Will this help performance or confuse the optimizer?

It can confuse it, but normally it is pretty good. If it does confuse
it, then the problem will be that it uses this index when another plan
may be better. In this case, you can always coax the query into
changing plan.

select *
from table1
order by field1+0 desc

Will not be able to use the descending index on field1.

Adam