db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Re: Using indexes
Date Sat, 25 Sep 2021 18:59:01 GMT
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


Mime
View raw message