db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Re: Using indexes
Date Tue, 28 Sep 2021 10:28:12 GMT
On 27/09/2021 18:23, John English wrote:
> I'll try the temporary table approach as soon as I get a minute, and 
> will let you know what happens.

I'm finally getting somewhere. Having (a) moved the row counting out to 
a separate query, (b) renamed columns in the table to match what my code 
requires so I can access the table directly instead of using a view, and 
(c) using your (Rick's) original idea of a nested select (which I 
decided to try again before going for a temporary table), I get an 
execution time of 455ms (woo-hoo!) for this query:

SELECT DateTimeFormat(t_time,null) AS t_time,facility,event,details
FROM system_log,
     (SELECT time FROM system_log
      ORDER BY time DESC NULLS LAST FETCH NEXT 20 ROWS ONLY) AS x
WHERE system_log.time=x.time;

However, if I add in a WHERE clause:

SELECT DateTimeFormat(t_time,null) AS t_time,facility,event,details
FROM system_log,
     (SELECT time FROM system_log
      WHERE username='foo'
      ORDER BY time DESC NULLS LAST FETCH NEXT 20 ROWS ONLY) AS x
WHERE system_log.time=x.time;

the execution time goes up to 2 seconds (I'm still happy!). The left 
result set (the nested select, by the look of it) still doesn't use the 
index:

Left result set:
   Row Count (2):
   Number of opens = 1
   Rows seen = 20
   Rows filtered = 0
   ...
   Source result set:
     Sort ResultSet:
     Number of opens = 1
     Rows input = 23211
     Rows returned = 20
     Eliminate duplicates = false
     In sorted order = false
     Sort information:
       Number of rows input=23211
       Number of rows output=23211
       Sort type=internal
       ...
       optimizer estimated row count: 38824.20
       optimizer estimated cost: 399695.58
     Source result set:
       Project-Restrict ResultSet (4):
       Number of opens = 1
       Rows seen = 23211
       Rows filtered = 0
       restriction = false
       projection = true
       ...
       Source result set:
         Table Scan ResultSet for SYSTEM_LOG at read uncommitted 
isolation level using share row locking chosen by the optimizer
         Number of opens = 1
         Rows seen = 23211

but the right result set uses the PK index.

So, I'm in a much better position as the result of Rick's advice, but I 
still have no idea why the descending PK index isn't ever used!!!

-- 
John English

-- 
This email has been checked for viruses by AVG.
https://www.avg.com


Mime
View raw message