How indexes change the options

A composite index has a specific order

CREATE INDEX orders_customer_date_idx
ON orders (customer_id, created_at);

This index groups entries by customer, then sorts each group by date. It is not one global date order.

SELECT id, total FROM orders
WHERE customer_id = 42 AND created_at >= '2025-01-01';

Equality before a range

An equality condition on the first key picks one customer's group. A range on the next key narrows the search within that group. The plan can show both bounds:

SEARCH orders USING INDEX orders_customer_date_idx
  (customer_id=? AND created_at>?)

The exact comparison spelling in EQP is an implementation detail. It shows that both columns narrow the search. It is not a literal rewrite of the SQL condition.

With no restriction on the leading key, a date predicate does not identify one small contiguous range. SQLite may use a skip-scan. It visits each distinct value of the first key and searches the later key within that group. This depends on statistics and whether the planner expects enough duplicates to make it worthwhile. A date-first index may work better for queries that search date ranges across all customers.

Putting the most selective column first is not a general rule. When both leading keys have equality conditions, either order may locate the pair. Choose an order that also supports the ranges, sorting, and other queries you need.

Predict which keys narrow the search

One index can filter and order

SELECT id, created_at, total FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC LIMIT 20;

The customer/date index can find customer 42, walk that group's dates backward, and stop after the needed rows. It may avoid a separate temporary sorting B-tree.

A date-only index can also supply newest-first order, but SQLite may pass many other customers' entries before finding 20 matches. Ordinary EQP does not print how many were rejected. The animation shows these rejected entries with a small invented dataset.

If timestamps tie and the application needs stable ordering, specify a tie-breaker such as ORDER BY created_at DESC, id DESC in both versions of a comparison. Check that the index supports the full requested order. Without a tie-breaker, rows with the same timestamp can appear in either order.

Across multiple customers, a (customer_id, created_at) index still does not provide global date order. Also, reversing an all-ascending multicolumn index reverses every key direction; it does not produce customer ascending and date descending simultaneously. Mixed-direction indexes can support that requirement.

Separate indexes are not one composite index

For AND-connected conditions on a table, SQLite generally chooses one index for that table access rather than intersecting two single-column indexes. OR optimization is a different case. A customer/date index can both narrow the search by customer and supply date order. Separate customer and date indexes do not combine to do this.

Cover the requested values

CREATE INDEX orders_cover_idx
ON orders (customer_id, created_at, total);

SQLite has no INCLUDE clause. To carry total in this index, put it in the key list. Because it follows customer and date, it does not disrupt their ordering. It also makes entries larger.

In these rowid-table examples, id is an INTEGER PRIMARY KEY alias for rowid. Secondary indexes already carry rowid, so selecting id does not require adding another id key merely for coverage. Do not generalize that detail to every primary-key declaration or to WITHOUT ROWID tables.

Index only a useful subset

CREATE INDEX pending_orders_date_idx
ON orders(created_at)
WHERE status = 'pending';

This partial index stores pending rows only. A query can use this index only if SQLite can prove that every row it needs is pending. An explicit pending-status condition makes this possible. SQLite uses limited rules to check the condition, so a logically equivalent expression or an unknown parameter value may prevent use of the index.

Index an expression

CREATE INDEX customers_lower_email_idx
ON customers(lower(email));

SELECT id FROM customers WHERE lower(email) = '[email protected]';

The indexed expression must match the query expression closely; SQLite does not generally infer algebraic equivalence. Functions in the index must be deterministic, meaning they return the same result for the same inputs. The text comparison rules, called collation, also matter. SQLite's built-in lower() handles ASCII letters by default, not full Unicode case folding. Check whether that matches your application's email rules.

Predict a plan change, then measure

Before creating an index, state what it should remove: a full scan, checks of unrelated candidates, table lookups, or a temporary sort. Compare EXPLAIN QUERY PLAN before and after, then time the actual query with the same data and values.

Indexes take disk space and add work to inserts and relevant updates. Building an index writes to the database and can block other work. Test on a copy before building it in production. Run PRAGMA optimize after schema changes according to the maintenance guidance.

A covering scan can be a good plan even when it says SCAN. A SEARCH that finds most of the table may still be expensive. Check the returned rows and timings rather than judging the label alone.

Compare read and write requirements

Exercises

SELECT id, created_at FROM events
WHERE tenant_id = 7
ORDER BY created_at DESC LIMIT 50;

Experiment and sources

Run sqlite3 indexes.db < indexes.sql against a new file. The experiment compares a date index, a customer/date index, and a covering variant.

• Query planning, multicolumn and covering indexes.
• Skip-scan optimization.
• Partial indexes.
• Indexes on expressions.
• SQLite storage types and comparisons.