On 09/02/2021 19:50, Rick Hillegas wrote:
On 2/9/21 8:21 AM, John English wrote:
On 09/02/2021 18:05, Rick Hillegas wrote:
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.

OK. The concept of a "covering index" is a new one on me, so I need to read up on that. Would it be possible to create a covering index for this table just by specifying "time DESC" followed by the other columns (i.e. everything except id)?
Yes.
Would it be a reasonable thing to do in terms of resource usage?
It would probably double the on-disk footprint of your table since the index would have to contain most of the columns (including the big DETAILS column). You have a space vs. time tradeoff here if the subquery-based solution won't work for your app.

I've just tried this:

create index log_index on system_log(time desc, username, name, facility, event, sector, item, details);

but I then get the following error:

SQL exception: Limitation: Record of a btree secondary index cannot be updated or inserted due to lack of space on the page. Use the parameters derby.storage.pageSize and/or derby.storage.pageReservedSpace to work around this limitation.

In my derby.properties I've added these two lines:

derby.storage.pageSize=32768
derby.storage.pageReservedSpace=0

but I still get the same error. (I also tried derby.storage.pageReservedSpace=90 just in case I'd misunderstood which direction to set it, but that didn't work either.)

So it looks like there isn't any way to create a covering index for this table... or is there something else I can do?

--
John English

Reply via email to