db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.com>
Subject Re: Using indexes
Date Sun, 26 Sep 2021 19:57:40 GMT
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?



Mime
View raw message