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

>
> Hi,
>
> I have a table of about 3 million records. The table A has many
> columns, among them are ID (Primary key, BigInt, auto-increasing), and
> update_time (DateTime).
>
> An ascending query completes in about 1 second:
> select first 1000 * from A
> where update_time >= '13-JUN-2006 00:00:00'
> and update_time <= '13-JUN-2007 00:00:00'
> and id <=3000000
> and id >= 2000
>
> Adopted plan is: PLAN(A INDEX(PK_A))
>
> but a descending query returns after 2 minutes:
> select first 1000 * from A
> where update_time >= '13-JUN-2006 00:00:00'
> and update_time <= '13-JUN-2007 00:00:00'
> and id <= 3000000
> and id >= 2000
> order by id desc
>
> Adopted plan is: PLAN(A ORDER A_IDX_ID_DESC)
>
>
> Several relevant indexes of table A are:
> 1. PK_A (PRIMARY KEY INDEX) on column ID, ascending
> 2. A_IDX_ID_DESC on column ID, descending
> 3. A_IDX_UPDATE_TIME_ASC on column update_time, ascending
> 4. A_IDX_UPDATE_TIME_DESC on column update_time, descending
>
> Can anyone help to improve descending query?
>
> Thanks a lot!
> Sean
>

Sorry, I wrote a wrong index name in the plan adopted by first query,
and first query is not ordered, and it's fast. The ascending ordered
query is fast too:

select first 1000 * from A
where update_time >= '13-JUN-2006 00:00:00'
and update_time <= '13-JUN-2007 00:00:00'
and id <= 3000000
and id >= 2000
order by id desc

Adopted plan is: PLAN(A ORDER PK_A)