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)? Would it be a reasonable thing to do in terms of resource usage?

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
   WHERE id IN
     SELECT id
     FROM system_log
     ORDER BY time DESC
   ORDER BY t_time

That's not so easy, since all the tables in the webapp are generated by a showTable() method, which displays results from a view as a table which allows for sorting, filtering, selecting the numbers of rows per page and so on, using a ridiculously long list of parameters to specify all the different possibilities. But I'll think about it.

John English

Reply via email to