How can I optimize SQLite ORDER BY rowid?

Question

I want to query my sqlite db for all level values ​​greater than 20, limit the result to 100 and sort by rowid.

When ordering rowid, the query is much slower. The database contains ~ 3 million records and the maximum level value is 50. An index is created for the level.

This statement takes ~ 20ms:

SELECT * FROM log WHERE level > 20 LIMIT 100 

This statement takes ~ 100 ms:

 SELECT * FROM log WHERE level > 20 ORDER BY rowid LIMIT 100 

This statement takes ~ 1000 ms (there are no lines with a level above 50):

 SELECT * FROM log WHERE level > 50 ORDER BY rowid LIMIT 100 

Is there a way to optimize this for a faster ORDER BY query?

This is the index used:

 CREATE INDEX level_idx ON table (level) 

Answer 1

There are two possible ways to fulfill this request:

• Find the first record with level>20 in the index level_idx , and then look at all the following records and select each corresponding row from the table. Since index entries are not stored in rowid order, all results must then be sorted. Then the first 100 of them can be returned.

• Ignore the index. Scan all the rows in the table (which are already stored in rowid order) and return all where the level column corresponds.

The database estimates that the second method is faster.

If you evaluate that the first method is faster, i.e. so few rows correspond to the level filter, which selects and sorts the remaining rows faster than ignoring inconsistent rows when scanning through a table, then you can force the database to use the index with the INDEXED BY clause:

 SELECT * FROM log INDEXED BY level_idx WHERE level > 20 ORDER BY rowid LIMIT 100 

However, forcing an index can lead to terrible slowdowns if your own estimate is incorrect.