db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.com>
Subject Re: Using indexes
Date Tue, 28 Sep 2021 14:52:33 GMT
Glad that you have made progress on this puzzle. One comment inline...

On 9/28/21 4:10 AM, John English wrote:
> 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.
The index is not usable in this query because username is not the 
leading column in the index.



Mime
View raw message