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.

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;

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;

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;

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.

HTH,
Set

Sean wrote:

> Yes, query optimization may be the solution. This is what I have:
>
> Table ABC with ID, Insert_Time, and some other fields. ID has both
> ascending and descending index, same for Insert_Time.
>
> I tested 3 queries, and the logic of 1st query is what I need:
>
> 1.
> 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 desc;
> running time: 38 second
>
> 2.
> select * from ABC
> where ID between 0 and 5000000
> Order by ID desc;
> running time: 500 ms0
>
> 3.
> select * from ABC
> where Insert_Time between '11-JUN-2007 00:00:00' and '21-JUN-2007
> 00:00:00'
> Order by ID desc;
> running time: 600 ms
>
> The optimizer used the same plan:
> PLAN (ABC ORDER IDX_ID_DESC)
>
> No.1 searched less records than No.2 and No.3, but it's the slowest :-(
>
>
> I have around 1,7 million records per week, and I need to store up to
> 1 year data.
>
>
> --- In firebird-support@yahoogroups.com, "Martijn Tonies" wrote:
>
>> The problem with separate tables is when you have to query across
>> multiple tables.
>>
>> Do you have any indices on your large table? How many rows per week?