My query to filter from 3 columns:
WHERE x <= 'something' AND y = 'something' AND z = 'SOMETHING ELSE' ORDER BY x DESC
How do I index to cover this query? I have tried:
CREATE INDEX idx_x_y_z ON user_messages(x, y, z);
CREATE INDEX idx_y_z ON user_messages(y, z);
The best I get is:
SEARCH TABLE table USING INDEX idx_y_z
USE TEMP B-TREE FOR ORDER BY
Is that optimal (or can I avoid USE TEMP B-TREE FOR ORDER BY
)? Seems so but since I ORDER BY
a field I filter on, maybe not.
From the query planner documentation:
Then the index might be used if the initial columns of the index (columns a, b, and so forth) appear in
WHERE
clause terms. The initial columns of the index must be used with the=
orIN
orIS
operators. The right-most column that is used can employ inequalities.
So since your WHERE
has two exact comparisons and one less than or equal, that one should come last in an index for best effect:
CREATE INDEX idx_y_z_x ON user_messages(y, z, x);
Using that index with a query with your WHERE
terms:
sqlite> EXPLAIN QUERY PLAN SELECT * FROM user_messages
...> WHERE x <= 'something' AND y = 'something' AND z = 'something'
...> ORDER BY x DESC;
QUERY PLAN
`--SEARCH TABLE user_messages USING INDEX idx_y_z_x (y=? AND z=? AND x<?)
As you can see, it fully uses the index, with no temporary table needed for sorting the results.
More, essential, reading about how sqlite uses indexes can be found here.