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

Reply via email to