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 ms

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"
<m.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?
>