8

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

Yields

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

Yields

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?

2

The documentation says:

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.

So expressions in the SELECT clause will not use the expression index.

Using a covering index is not as much an improvement over using a normal index for searching/sorting as using a normal index would be over using no index at all, so this optimization has not (yet?) been implemented for expression indexes.

  • Using the JSON_EXTRACT expressions in the WHERE and/or ORDER BY statement is equivalent since I merely made an alias with the SELECT fields. Rewriting the query to use JSON_EXTRACT instead of the alias yields no different result. – Roel van Uden May 4 '17 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.