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 11:10:20 GMT
A couple more data points, from testing different variants of the inner 
select:

1) SELECT time FROM system_log
      ORDER BY time DESC NULLS LAST FETCH NEXT 20 ROWS ONLY;

419ms, "Index Scan ResultSet for SYSTEM_LOG using index LOG_INDEX at 
read uncommitted isolation level using share row locking chosen by the 
optimizer"

2) SELECT time FROM system_log
      WHERE username='foo'
      ORDER BY time DESC NULLS LAST FETCH NEXT 20 ROWS ONLY;

2245ms, "Table Scan ResultSet for SYSTEM_LOG at read uncommitted 
isolation level using share row locking chosen by the optimizer"

Removing "FETCH NEXT 20 ROWS ONLY":

1) 388,237 rows in 3859ms, otherwise same as above (uses index).
2) 23,211 rows in 2199ms, otherwise same as above (doesn't use index).

The WHERE clause seems to prevent the index from being used.

-- 
John English

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


Mime
View raw message