I performed a trace and this is the result. I suspected it was doing an
order by:
Preparing Statement 5694738 : SELECT
"ID","USERNAME","USERNO","LOGDATE","LOGTIME","LOGTABLE","RECORDKEY","REC
ORDACTION","RECORDBEFORE","RECORDAFTER" FROM "AIMLOG" WHERE ("ID" <= ? )
ORDER BY "ID" DESC Time Taken:0.00 secs
Setting number of rows to fetch to 1 for Statement 5694738 Time
Taken:0.00 secs
Binding Column 1 to C type SLONG for Statement 5694738 Time Taken:0.00
secs
Binding Column 2 to C type CHAR for Statement 5694738 Time Taken:0.00
secs
Binding Column 3 to C type SSHORT for Statement 5694738 Time Taken:0.00
secs
Binding Column 4 to C type SLONG for Statement 5694738 Time Taken:0.00
secs
Binding Column 5 to C type SLONG for Statement 5694738 Time Taken:0.00
secs
Binding Column 6 to C type CHAR for Statement 5694738 Time Taken:0.00
secs
Binding Column 7 to C type CHAR for Statement 5694738 Time Taken:0.00
secs
Binding Column 8 to C type CHAR for Statement 5694738 Time Taken:0.00
secs
Binding Column 9 to C type CHAR for Statement 5694738 Time Taken:0.00
secs
Binding Column 10 to C type CHAR for Statement 5694738 Time Taken:0.00
secs
Binding ? 1 with C type SLONG for Statement 5694738 Time Taken:0.00 secs
Executing prepared Statement 5694738 Time Taken:15.73 secs
Fetching Row from Statement 5694738 Return Code: 0 Time Taken:0.00 secs
HTH
Andrew Zenz
________________________________
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: Wednesday, 18 September 2019 10:11 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] index use when using order by
Hi everyone.
I am experiencing an unusual situation.
I have a log table for tracking user changes to records. Table is as
follows:
CREATE TABLE AIMLOG
(
ID Integer NOT NULL,
USERNAME Char(12) COLLATE EN_UK,
USERNO Integer,
LOGDATE Integer,
LOGTIME Integer,
LOGTABLE Char(30) COLLATE EN_UK,
RECORDKEY Char(30) COLLATE EN_UK,
RECORDACTION Char(10) COLLATE EN_UK,
RECORDBEFORE Varchar(1000) COLLATE EN_UK,
RECORDAFTER Varchar(1000) COLLATE EN_UK,
CONSTRAINT AIMLOG_PK PRIMARY KEY (ID)
);
Using Flamerobin, if I want to find the last record I run the following
query:
SELECT a.ID, a.USERNAME, a.USERNO, a.LOGDATE, a.LOGTIME, a.LOGTABLE,
a.RECORDKEY, a.RECORDACTION, a.RECORDBEFORE, a.RECORDAFTER
FROM AIMLOG a
order by a.id descending
The query takes about 15 seconds because it doesn't want to use the
primary key, AIMLOG_PK. Plan below:
Starting transaction...
Preparing query: SELECT a.ID, a.USERNAME, a.USERNO, a.LOGDATE,
a.LOGTIME, a.LOGTABLE, a.RECORDKEY, a.RECORDACTION, a.RECORDBEFORE,
a.RECORDAFTER
FROM AIMLOG a
order by a.id descending
Prepare time: 0.015s
Field #01: AIMLOG.ID Alias:ID Type:INTEGER
Field #02: AIMLOG.USERNAME Alias:USERNAME Type:STRING(12)
Field #03: AIMLOG.USERNO Alias:USERNO Type:INTEGER
Field #04: AIMLOG.LOGDATE Alias:LOGDATE Type:INTEGER
Field #05: AIMLOG.LOGTIME Alias:LOGTIME Type:INTEGER
Field #06: AIMLOG.LOGTABLE Alias:LOGTABLE Type:STRING(30)
Field #07: AIMLOG.RECORDKEY Alias:RECORDKEY Type:STRING(30)
Field #08: AIMLOG.RECORDACTION Alias:RECORDACTION Type:STRING(10)
Field #09: AIMLOG.RECORDBEFORE Alias:RECORDBEFORE Type:STRING(1000)
Field #10: AIMLOG.RECORDAFTER Alias:RECORDAFTER Type:STRING(1000)
PLAN SORT ((A NATURAL))
Executing...
Done.
267615 fetches, 0 marks, 3818 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 129986 seq.
Delta memory: 9460264 bytes.
Total execution time: 14.734s
Script execution finished.
Another simple query that should use the index (I would assume) but
doesn't is:
select max(a.id) from aimlog a
which uses the plan:
Starting transaction...
Preparing query: select max(a.id) from aimlog a
Prepare time: 0.015s
Field #01: .MAX Alias:MAX Type:INTEGER
PLAN (A NATURAL)
Executing...
Done.
267615 fetches, 0 marks, 3821 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 129986 seq.
Delta memory: 9724 bytes.
Total execution time: 0.172s
Script execution finished.
The application we develop in, Clarion, uses a mechanism of SET the
records in a key order (usually placing the cursor at the bottom or top
of the order, in this case top), then PREVIOUS to find the highest
record, then adding 1 to get the next ID. (Don't question the method,
it works in the IDE we have never had an issue), however when the
application does it's SET/PREVIOUS it takes 10 to 15 seconds to
complete. I can only gather that it is doing a NATURAL read as above
instead of using the index.
We don't seem to have the issue (as far as I can tell) with other tables
that use the SET/PREVIOUS mechanism.
Is it a property of the 'order by' to not use the index, is it a bug, is
it a fault in my table definition or am I missing the point?
Firebird 2.5.7 (WI-2.5.7.27050)
Just realised I 'should' be using 2.5.8, but on a server running
WI-V2.5.8.27089 it still uses NATURAL.
Any pointers or ideas?
Thanks.
Andrew Zenz
