On 9/25/21 11:59 AM, John English wrote:
On 25/09/2021 21:14, Rick Hillegas wrote:
On 9/25/21 7:39 AM, John English wrote:
SELECT id,DateTimeFormat(time,null) AS
t_time,name,username,facility,event,details
FROM system_log
ORDER BY id DESC
NULLS LAST
FETCH FIRST 20 ROWS ONLY;
I can remember whether you tried to rewrite the query to use a
subquery. Something like this:
SELECT id, time AS t_time,name,username,facility,event,details
FROM
system_log s,
(
SELECT id AS log_id
FROM system_log
ORDER BY id DESC
NULLS LAST
FETCH FIRST 20 ROWS ONLY
) t
WHERE s.id = t.log_id
;
Does that help?
-Rick
No, when I tried it, it made it even worse if anything. The subquery
still fetches all 400,000 rows, sorts them without using the index,
and then uses the results to select again in the outer query. I really
don't understand why the index is ignored.
The support for FETCH/OFFSET is pretty minimal. Probably, the optimizer
isn't smart enough to know that the subquery returns only 20 small rows.
What happens if you dump the results of the subquery into a temporary
table and then join that with system_log?
