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.
--
John English
--
This email has been checked for viruses by AVG.
https://www.avg.com