On 10/02/2021 18:51, Rick Hillegas wrote:
The index key (time desc, username, name, facility, event, sector, item,
details) could potentially be 32867 bytes long. However, an index key
must be less than 1/2 the page size, according to the "Page size and key
size" topic at
https://db.apache.org/derby/docs/10.15/ref/rrefsqlj20937.html That is,
your new index key is too big.
OK.
With the subquery-based approach, your covering index key would be only
20 bytes long:
CREATE INDEX log_index ON system_log (time DESC, id);
Timing tests on my development rig (table size 294,217 rows):
SELECT *
FROM system_log
WHERE id IN
(
SELECT id
FROM system_log
ORDER BY time DESC
NULLS LAST
FETCH NEXT 20 ROWS ONLY
);
4510ms with no index, 3277ms with index as above.
SELECT *
FROM system_log
ORDER BY time DESC
NULLS LAST
FETCH NEXT 20 ROWS ONLY;
3452ms with no index, 1903ms with index as above.
So it seems that the nested select makes things worse rather than
better... :(
--
John English
