--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

>
> Hi Sean!
>
> To me, your results doesn't sound sensible. Are you saying that query 2
> and 3 return more than a million rows within less than a second? That's
> impossible... What is possible, is that query 2 and 3 return the first
> rows a lot faster than query 1, but that doesn't mean they return the
> entire result set faster.

You're right. I missed tying the "first 1000" in the queries. I only
intend to get first 1000 records.

> Try three other queries (they may be quicker or slower, the first query
> is mainly because I expect to get to learn more about the plan - I

guess

> you're using Firebird 1.5?):
>
> 4.
> select * from ABC
> where ID between 2000000 and 5000000
> and Insert_Time between '11-JUN-2007 00:00:00' and '21-JUN-2007
> 00:00:00'
> Order by ID+0 desc;

The query failed with a sort error:
I/O error for file "D:\TEMP\fb_sort_7v4rb1".
Error while trying to write to file.
The printer is out of paper.

I guess w/o using ID_DESC index, it tries sorting it on the fly and
run out of space.

Yes, I am using Firebird 1.5.

> 5.
> select * from ABC
> where ID+0 between 2000000 and 5000000
> and Insert_Time between '11-JUN-2007 00:00:00' and '21-JUN-2007
> 00:00:00'
> Order by ID desc;

This query is fantastic! It returns me first 1000 record immediately.
I am wondering why this works so nicely?

The query plan shown is still: PLAN (ABC ORDER IDX_ID_DESC)

> 6.
> select * from ABC
> where ID+0 between 2000000 and 5000000
> and Insert_Time between '11-JUN-2007 00:00:00' and '21-JUN-2007
> 00:00:00'
> Order by ID+0 desc;

Same error as No.4

> Also, ask yourself whether the entire result set is of interest. If

not,

> try to make your WHERE criteria more restrictive. E.g. if you know that
> you're normally only interested in the first 10 records, only rarely in
> the first 100 records and never interested in more than 1000 records

and

> records are normally inserted with increasing insert_time, then you
> could in theory do something like 'ID between 4980000 and 5000000'
> rather than checking the last 3 million IDs.
>

My application displays records page by page, and user has freedom to
browse to records of any day inside one week. So I am afraid I can't
predict ID range.