I am attempting to create an index in
SQLite3 (3.18) using
json_extract expressions. My aim is to execute queries that only require the index to yield results. The reason for this is that
json_extract is an expensive operation which would hinder performance when operating on larger data sets and/or values. I concluded I need a covering index to suit my needs.
Step 1 - Testing the theory using a normal table structure
CREATE TABLE Player ( Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, FirstName TEXT NOT NULL, MiddleName TEXT, LastName TEXT NOT NULL ); CREATE INDEX Player_FirstName ON Player ( FirstName ASC, LastName ASC ); EXPLAIN QUERY PLAN SELECT FirstName, LastName FROM Player WHERE LENGTH(LastName) > 10 ORDER BY FirstName LIMIT 10 OFFSET 0
SCAN TABLE Player USING COVERING INDEX Player_FirstName
This is exactly what I expect. The query planner figured that the
Player_FirstName index is appropriate due to the
ORDER BY clause, and since the
WHERE statement operates only on a value that is also in that index, it doesn't need to read the table. Finally, the
SELECT statement includes only the indexed columns therefore resulting in a query that doesn't touch the table at all.
Step 2 - Testing the theory with an extract expression
CREATE TABLE PlayerJ ( Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Data TEXT NOT NULL ); CREATE INDEX PlayerJ_FirstName ON PlayerJ ( JSON_EXTRACT(Data, '$.FirstName') ASC, JSON_EXTRACT(Data, '$.LastName') ASC ); EXPLAIN QUERY PLAN SELECT JSON_EXTRACT(Data, '$.FirstName') AS FirstName, JSON_EXTRACT(Data, '$.LastName') AS LastName FROM PlayerJ WHERE LENGTH(LastName) > 10 ORDER BY FirstName LIMIT 10 OFFSET 0
SCAN TABLE PlayerJ USING INDEX PlayerJ_FirstName
This is not what I expected. The query planner seems to have figured out that the
ORDER BY clause is on
JSON_EXTRACT(Data, '$.FirstName'), and thus seems to have selected the appropriate index. But that is where my reasoning ends abruptly. What is going on here? I would have expected the query planner to figure out that this is the same as the previous test, and the index would be used as a covering index. But it doesn't.
Why not? And how can this second test be modified so that it runs only against the index?