As Bryan points out, please consult the Tuning Guide for information on how to view your query plan. In any event, your descending index is not a covering index. That is, it does not contain all of the columns in your query. That may be what forces the optimizer to choose a full table scan.

You might be able to trick the optimizer into generating a more efficient plan by re-writing your query as follows:

SELECT time as t_time,
  FROM system_log
    SELECT id
    FROM system_log
    ORDER BY time DESC
  ORDER BY t_time

On 2/9/21 7:17 AM, Bryan Pendleton wrote:
I think Rick was suggesting that if you got the right query plan,
using your index as efficiently as possible, then that would be the
best solution? You can find a lot of information about this in

Perhaps your statistics are inaccurate, so Derby is not choosing the
index-based plan? I think there are ways to force Derby to choose that
plan, and there are also ways to ensure that your statistics are

Specifically, see:


On Tue, Feb 9, 2021 at 4:04 AM John English <> wrote:
On 08/02/2021 18:43, Rick Hillegas wrote:
I would recommend throwing more memory at your JVM or adding a
descending index to system_log.time.
So, there is already a descending index to system_log.time, and I've
upped the heap to 2GB. I also added the time taken to service the
request as an HTML comment at the end of the page.

The disk is an SSD, and the table contains about 261,000 rows. Accessing
the first 20 rows ordered by time desc, it takes about 13 seconds, which
seems a bit excessive to me. Ordering by event also takes about the same
(12690ms as opposed to 13151ms), so the index doesn't seem to have much

Not sure what to try next... any ideas?

John English

Reply via email to