I think I already know the answer, but I'd like a bit more detail on why
there was such a difference.

TICKET - primary key INTEGER from GENERATOR
TRANSACTIONS ( detail for TICKET ) - primary key INTEGER, TIMESTAMP

I need the first and second TRANSACTIONS entry for each ticket, so I was
using
SELECT FIRST 1 (SKIP 1) PREVIOUS FROM TRANSACTIONS WHERE TICKET_ID =
TICKET_ID ORDER BY TRANSACT.
Which was showing that the plan used the primary key index.

On 500,000 transactions this was starting to get to slow, approaching
minutes for a months worth of stats :(
We took advantage of the fact that we can run as many copies of Firebird
as we like, and switched the long term report generation to a copy of
Firebird on the backup computer. They do not need todays info for last
weeks results.

However the time taken was concerning when other reports on the same
data take seconds.

Tidying up the data to give more access to the details, I've added a
TRANSACT_NO SMALLINT to transactions in addition to the TRANSACT
TIMESTAMP - which is simply populated by 'CURRENT_TIMESTAMP' when a
detail record is created. The only write activity on TRANSACTIONS is to
insert a record via a trigger from TICKET updates.

So now I have
TRANSACTIONS ( detail for TICKET ) - primary key INTEGER, SMALLINT
and
SELECT PREVIOUS FROM TRANSACTIONS WHERE TICKET_ID = TICKET_ID AND
TRANSACT_NO = 0 (or 1)

And the PLAN is showing the same primary key index being used.

So why does the TIMESTAMP key take so much longer to use than the
SMALLINT. Is it just that you can't get direct to the data with the
first and have to FIRST/ORDER. The majority of TICKET entries only have
2 transactions, and once the first value is found the second takes no
time ( setting the second select to 0 does not noticeably affect the
time of the first method which is what I did expect ).

Can someone who understands the engine please fill in a little more
explanation here?

--
Lester Caine - G8HFL
-----------------------------
L.S.Caine Electronic Services - http://home.lsces.co.uk
Model Engineers Digital Workshop -
http://home.lsces.co.uk/ModelEngineersDigitalWorkshop/
Treasurer - Firebird Foundation Inc. - http://www.firebirdsql.org/index.php