When to create multi-column indices in SQLite?-CodePudding

Assume I have a table in an SQLite database:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    price INTEGER NOT NULL,
    updateTime INTEGER NOT NULL,
) [WITHOUT ROWID];

what indices should I create to optimize the following query:

SELECT * FROM orders WHERE price > ? ORDER BY updateTime DESC;

Do I create two indices:

CREATE INDEX i_1 ON orders(price);
CREATE INDEX i_2 ON orders(updateTime);

or one complex index?

CREATE INDEX i_3 ON orders(price, updateTime);

What can be query time complexity?

CodePudding user response：

From The SQLite Query Optimizer Overview/WHERE Clause Analysis:

If an index is created using a statement like this:

CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z);

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 = or IN or IS operators. The right-most column that is used can employ inequalities.

As explained also in The SQLite Query Optimizer Overview/The Skip-Scan Optimization with an example:

Because the left-most column of the index does not appear in the WHERE clause of the query, one is tempted to conclude that the index is not usable here. However, SQLite is able to use the index.

This means than if you create an index like:

CREATE INDEX idx_orders ON orders(updateTime, price);

it might be used to optimize the WHERE clause even though updateTime does not appear there.

Also, from The SQLite Query Optimizer Overview/ORDER BY Optimizations:

SQLite attempts to use an index to satisfy the ORDER BY clause of a query when possible. When faced with the choice of using an index to satisfy WHERE clause constraints or satisfying an ORDER BY clause, SQLite does the same cost analysis described above and chooses the index that it believes will result in the fastest answer.

Since updateTime is defined first in the composite index, the index may also be used to optimize the ORDER BY clause.