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

 

 

 



  • [firebird... 'Andrew Zenz' and...@aimsoftware.com.au [firebird-support]
    • RE: ... 'Andrew Zenz' and...@aimsoftware.com.au [firebird-support]
      • ... 'Andrew Zenz' and...@aimsoftware.com.au [firebird-support]
    • Re: ... Mark Rotteveel m...@lawinegevaar.nl [firebird-support]
    • RE: ... liviuslivius liviusliv...@poczta.onet.pl [firebird-support]
      • ... 'Andrew Zenz' and...@aimsoftware.com.au [firebird-support]
      • ... Mark Rotteveel m...@lawinegevaar.nl [firebird-support]
        • ... 'Andrew Zenz' and...@aimsoftware.com.au [firebird-support]
        • ... 'P-Soft - Fabio Codebue' f.code...@p-soft.biz [firebird-support]
          • ... Dmitry Yemanov dim...@users.sourceforge.net [firebird-support]
            • ... 'P-Soft - Fabio Codebue' f.code...@p-soft.biz [firebird-support]
            • ... Rustam rusta...@ukr.net [firebird-support]

Reply via email to