Hi.

I would use a separate descending index on a date/timestamp field.
And if this table has some another detail one then that detail FK
should reference just PK of the master.

Then if you query basing on dates then you should alwayes mention the
master table in your SQL first.

CREATE TABLE MASTER (
MASTER_ID ... PRIMARY KEY,
ADATE DATE,
...
);

CREATE DESCENDING INDEX I_MASTER_ADATE ON MASTER(ADATE);

CREATE DETAIL (
DETAIL_ID ... PRIMARY KEY,
MASTER_ID ... FOREGIN KEY REFERENCES MASTER,
...
);

SELECT M.*, D.*
FROM MASTER M
LEFT JOIN DETAIL D ON D.MASTER_ID = M.MASTER_ID
WHERE
M.ADATE >= :some_recent_date AND
D.DETAIL_ID IS NOT NULL AND
...

Note that LEFT JOIN will force the engine to use just I_MASTER_ADATE
index first and then all other if available.

But it always good to test different scenarios, see the plan and
measure actual performance.

Maxim.

--- In firebird-support@yahoogroups.com, Lee Jenkins <lee@...> wrote:
>
>
....