SQLite § indexes

Indexes and query planning

An index is an auxiliary data structure that maps values in one or more columns of a table to the rows that contain those values. Where a SELECT … WHERE col = ? against an unindexed column requires a scan of every row in the table, the same query against an indexed column is an O(log n) B-tree lookup. Indexes are the primary mechanism by which SQL databases obtain query performance, and the choice of which columns to index, and what kind of index to create, is one of the recurring engineering decisions in database work.

This page covers CREATE INDEX, multi-column indexes, partial indexes, expression indexes, the WITHOUT ROWID storage layout’s relationship to indexes, and the EXPLAIN QUERY PLAN diagnostic that reveals which index a query actually uses.

CREATE INDEX

CREATE INDEX ix_customer_email ON customer (email);

This statement creates an index on the email column of the customer table. The convention adopted in this volume is that indexes are named ix_<table>_<columns> for ordinary indexes and ux_<table>_<columns> for unique indexes, but no naming scheme is enforced by the engine; the names appear in sqlite_master, in EXPLAIN QUERY PLAN output, and in error messages.

The index is implemented as a B-tree (specifically a B+-tree) keyed on the index columns and storing, at each leaf, the rowid (or, for WITHOUT ROWID tables, the primary key) of the corresponding row. Lookups consult the B-tree, retrieve the rowid, and then do a separate B-tree lookup on the table to fetch the row. A covering index — one that includes every column the query needs — avoids the second lookup.

CREATE UNIQUE INDEX adds the constraint that no two rows have the same indexed value:

CREATE UNIQUE INDEX ux_customer_email ON customer (email);

A UNIQUE column constraint creates a unique index automatically; CREATE UNIQUE INDEX is the form to use when the index is added later or when the uniqueness is over multiple columns or is conditional.

The IF NOT EXISTS modifier suppresses the error if an index of that name already exists:

CREATE INDEX IF NOT EXISTS ix_customer_email ON customer (email);

DROP INDEX removes an index. A UNIQUE index that backs a column-level UNIQUE constraint cannot be dropped without removing the constraint:

DROP INDEX ix_customer_email;
DROP INDEX IF EXISTS ix_customer_email;

Indexes on multiple columns

An index may cover several columns. The order of the columns matters: an index on (a, b) accelerates queries that filter on a alone or on a and b, but not queries that filter on b alone.

CREATE INDEX ix_order_customer_placed
    ON bouquet_order (customer_id, placed_at);

This index supports:

WHERE customer_id = ?
WHERE customer_id = ? AND placed_at > ?
WHERE customer_id IN (…) AND placed_at BETWEEN ? AND ?
ORDER BY customer_id, placed_at — the index returns rows already sorted

It does not support WHERE placed_at > ? alone; that query must scan the table or use a separate index on placed_at. The leading-prefix rule is a useful mnemonic: a multi-column index is useful for any prefix of its columns.

The conventional design rule for composite indexes is equality columns first, range columns last: columns used with = are placed before columns used with <, >, or BETWEEN. The B-tree can find the equality matches, then traverse the range within them; reversing the order forfeits the second optimisation.

Indexes can be declared with column-specific direction (ASC or DESC), which matters for queries that mix sort directions:

CREATE INDEX ix_log_descending
    ON log (timestamp DESC);

The query planner uses the declared direction when matching an ORDER BY to an index.

Partial indexes

A partial index is an index that covers only the rows matching a WHERE clause. The clause is evaluated at insert and update time; rows that do not match are not entered into the index.

CREATE UNIQUE INDEX ux_user_email_active
    ON user (email)
    WHERE deleted_at IS NULL;

This index enforces unique emails only among non-deleted users. Two rows with the same email are permitted as long as at least one is deleted. The conventional uses of partial indexes are:

Sparse uniqueness: WHERE col IS NOT NULL (see NULL semantics).
Hot paths: indexing only the small subset of rows that queries actually touch — open invoices, active sessions, undelivered messages.
Soft delete: indexing only rows where deleted_at IS NULL.

A partial index is smaller than a full index over the same columns, both on disk and in cache. The smaller footprint translates into faster lookups and lower memory pressure; the trade-off is that a query whose filter does not match the partial-index predicate cannot use the index at all.

-- Useful: matches the partial-index predicate
SELECT * FROM user WHERE email = ? AND deleted_at IS NULL;

-- Cannot use the index; predicate does not match
SELECT * FROM user WHERE email = ?;

The query planner will only use a partial index when the query’s WHERE clause logically implies the index’s predicate. This is a strict requirement: a query with deleted_at IS NULL matches deleted_at IS NULL, but a query with WHERE deleted_at = NULL (which is always false; see NULL semantics) does not — the predicates are syntactically different even if related.

Expression indexes

An expression index is keyed on the result of a deterministic expression rather than on a column directly. The form was added in version 3.9.

CREATE INDEX ix_customer_lower_email
    ON customer (lower(email));

A query that uses the same expression in its WHERE clause can use the index:

SELECT * FROM customer WHERE lower(email) = lower(?);

The conventional uses of expression indexes are:

Case-insensitive lookup: lower(email) or upper(name).
Date-bucketed lookups: strftime('%Y-%m', occurred_at).
JSON path access: json_extract(payload, '$.user_id') for indexed JSON columns.
Computed comparison keys: length(name) for queries that order by string length.

The expression must be deterministic: the SQLite engine refuses to index an expression that calls a non-deterministic function (random, now-style functions). The expression is also recomputed on every insert and update, so the cost of the index includes the computation cost.

A frequent and powerful idiom: indexing a json_extract to make a virtual column queryable.

CREATE TABLE event (
    id      INTEGER PRIMARY KEY,
    payload TEXT NOT NULL CHECK (json_valid(payload))
);

CREATE INDEX ix_event_user
    ON event (json_extract(payload, '$.user_id'));

SELECT * FROM event
WHERE json_extract(payload, '$.user_id') = 42;

The query above uses the index even though the column itself is opaque JSON.

Covering indexes

A covering index contains every column needed by the query, allowing the engine to answer the query without consulting the underlying table. The query planner detects this case and reports SEARCH … USING COVERING INDEX … in EXPLAIN QUERY PLAN.

CREATE INDEX ix_order_customer_placed
    ON bouquet_order (customer_id, placed_at, bouquet_id);

SELECT bouquet_id, placed_at
FROM bouquet_order
WHERE customer_id = ?;
-- Uses the covering index; no table-row reads.

The trade-off is that wider indexes are larger on disk and slower to maintain; the rule of thumb is that a covering index is worthwhile when the underlying query is on a hot path and reads several columns that fit naturally into a small index. For a query that reads many columns, a covering index is rarely the right answer.

EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN reports the strategy the query planner has chosen for a statement. The output is a small tree, one node per access:

EXPLAIN QUERY PLAN
SELECT name FROM customer WHERE email = 'ada@example.com';
-- 4|0|0|SEARCH customer USING INDEX ix_customer_email (email=?)

The fields are an internal id, parent id, and execution order, followed by a textual description. The vocabulary of descriptions includes:

Phrase	Meaning
`SCAN <table>`	Full table scan; no index used.
`SEARCH <table> USING INDEX <name>`	Index lookup, then row fetch.
`SEARCH <table> USING COVERING INDEX <name>`	Index-only access.
`SEARCH <table> USING INTEGER PRIMARY KEY (rowid=?)`	Direct rowid lookup.
`USE TEMP B-TREE FOR ORDER BY`	A separate sort step is required.
`USE TEMP B-TREE FOR GROUP BY`	A separate group step is required.

The conventional debugging pattern: run EXPLAIN QUERY PLAN against the slow query, look for SCAN (likely indicates a missing index) and USE TEMP B-TREE (likely indicates an ORDER BY or GROUP BY that could be served by an index). The planner is influenced by ANALYZE (described below); after creating an index, running ANALYZE ensures the planner uses it.

ANALYZE

ANALYZE collects statistics about the distribution of values in tables and indexes. The statistics are stored in the system tables sqlite_stat1 (and, with PRAGMA optimize enabled, sqlite_stat4) and inform the planner’s choice of index when several are available.

ANALYZE;                 -- collect statistics for the entire database
ANALYZE customer;        -- collect statistics for one table

ANALYZE is safe to run at any time. The recommendation is to run it after any large data import, after creating new indexes, and periodically during the life of a long-running application. The PRAGMA optimize form (added in 3.18) re-analyses tables whose statistics may be stale and is the recommended autoanalyze invocation:

PRAGMA optimize;

This is a no-op for tables whose data has not substantially changed since the last analysis; running it on connection close is a sensible default.

WITHOUT ROWID storage

A WITHOUT ROWID table (see Tables and rowid) is itself organised as a B-tree on the primary key — there is no separate rowid layer. The implication for indexing is that lookups by primary key are slightly faster (one fewer B-tree traversal) and that secondary indexes refer to the primary key, not the rowid. The choice between WITHOUT ROWID and a conventional rowid table is a storage-layout decision that interacts with index design; for tables with non-integer primary keys and small row sizes, WITHOUT ROWID is often the right answer.

When indexes hurt

Indexes are not free. Each index doubles the work of an INSERT, UPDATE, or DELETE on its columns: the engine must update the index B-tree as well as the table B-tree. Indexes also consume disk space and cache memory. A schema with too many indexes is slower than one with too few; the rule of thumb is that an index should pay for itself in measurable query speedup on a workload that runs more reads than writes against the indexed column.

The diagnostic for an over-indexed schema is that INSERT and UPDATE performance degrades while reads remain fast; the remedy is to drop the indexes that are not actually used. The pragma PRAGMA index_list(<table>) and the system table sqlite_master enumerate indexes; running EXPLAIN QUERY PLAN against the application’s queries reveals which are consulted.

Designing indexes

The recommendations applicable to a typical schema:

Index every foreign-key column. Foreign-key checks consult the index; without one, every cascade operation is a full scan.
Index columns that appear in WHERE, ORDER BY, and GROUP BY clauses of frequently-run queries.
Use composite indexes for queries that filter on multiple columns; place equality columns before range columns.
Use partial indexes for hot paths that touch only a small fraction of the table.
Use expression indexes for case-insensitive comparisons, JSON access, and other transformed columns.
Run ANALYZE after creating indexes and after substantial data changes.
Use EXPLAIN QUERY PLAN to verify that a query uses the index you expect, and to find queries that would benefit from a new index.
Drop indexes that profiling shows are unused.

The right index set is workload-specific and discovered empirically; the schema should not aim for completeness but for fitting the actual queries the application runs.