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
