SQLite query is slower with appropriate index

Bepis 0
Viewed 70

I'm trying to improve the performance of a query with this table:

CREATE TABLE frag(hash primary key, type, ulen, clen, offset, refs);

SELECT MAX(offset + clen) AS 'EndPos', type AS 'Type' FROM frag GROUP BY type;

My query plan is as such:

sqlite> EXPLAIN QUERY PLAN SELECT MAX(offset + clen) AS 'Offset', type AS 'Type' FROM frag GROUP BY type;
QUERY PLAN
|--SCAN TABLE frag
`--USE TEMP B-TREE FOR GROUP BY

After creating a new index, the query plan changes to this:

CREATE INDEX max_frag ON frag(type, offset+clen DESC);

sqlite> EXPLAIN QUERY PLAN SELECT MAX(offset + clen) AS 'EndPos', type AS 'Type' FROM frag GROUP BY type;
QUERY PLAN
`--SCAN TABLE frag USING INDEX max_frag

My issue is that my query has slowed from from taking 90s to 280s with this new index. My database is 5.6GB large, with 45.5 million rows. Why is a full table scan faster than using this index? .expert is telling me that no new index can improve this

performance sqlite indexing sql
2 Answers

I don't know if SQLite support function indices, but one alternative approach you could try here would be to store the sum offset + clen in the table as some new column, say total. Then, you may add the following index to the table:

CREATE INDEX idx ON frag (type, total DESC);

Then, the above index should work with the following query:

SELECT MAX(total) AS EndPos, type AS Type FROM frag GROUP BY type;
Tim Biegeleisen0

As you stated in your comment, expressions are supported in indices. However, "The SQLite query planner will consider using an index on an expression when the expression that is indexed appears in the WHERE clause or in the ORDER BY clause of a query".

Since you are using it in an aggregate function, that part of the index will not be used and only the type part of the index will be used. If type is a field with only a few different values across the table, and the values are scattered in all pages of the database, the result is that for each value of type, sqlite will have to read nearly all the pages of the table to get max(offset + clen) and this has to be repeated for every value of type.

The solution is to add to your table a generated colum

total AS (offset + clen) STORED

and then using this column in the index and query

CREATE INDEX max_frag ON frag(type, total DESC);
ANALYZE;
SELECT MAX(total) AS 'EndPos', type AS 'Type' FROM frag GROUP BY type;

The result will be that only the index will be read, without accessing the table data, and the query plan should be like:

SCAN TABLE frag USING COVERING INDEX max_frag

EDIT: I tried the above with a test and apparently sqlite doesn't use covering indexes with generated colums.

Another solution can be to rewrite your query to leverage the fact that you are using MAX() and the index is in descending order, so the first record of any type is the one with the max value:

SELECT (SELECT offset + clen FROM frag f2 WHERE f2.type=frag.type ORDER BY type, offset + clen DESC LIMIT 1) AS 'EndPos', type AS 'Type' FROM frag GROUP BY type;
Andrea B.0
Related