Subject Re: [firebird-support] Re: MAX(ID) Performance
Author Helen Borrie
At 12:02 AM 25/10/2009, you wrote:
>Hi Helen,
>> Missing a DESCENDING index on ID?
>This tip is very helpful and now performance are much better, but now I have MIN(ID) problem, if I set up primary key like it is and create new index on sam filed but DESC is this will be best solution form my problem?

If the primary key is ID, it has an ASCENDING index created for it automatically.

To find MAX(ID) efficiently you add the DESCENDING *index*. The DESC index does not affect the existing ASC index.

You also wrote:

>Ok, this trick is solved my problem for now, but I have same ordering details by ID field where I musn't use DESC indexing.

By default, ORDER BY sorts in ascending order. So

ORDER BY ID is the same as ORDER BY ID ASC

If the optimizer decides to use an index for the ascending sort, it will use the ascending index.

The optimizer would consider the DESCENDING index only if the sort was specified as ORDER BY ID DESC
It won't use the DESC index for an ASC order plan.