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
