SQLite Indexes Explained

An index is a separate data structure that lets SQLite find rows without scanning the whole table. Every table and every index in a SQLite database is stored as its own B-tree in the database file. The table B-tree is keyed by rowid; an index B-tree is keyed by the indexed columns and carries the rowid so SQLite can jump back to the full row.

Without an index, finding WHERE email = 'ada@example.com' means reading every row (a full scan). With an index on email, SQLite descends the B-tree directly to the match. The tradeoff is that each index adds write cost and storage, because every INSERT, UPDATE, and DELETE must maintain it.

Creating an Index

CREATE INDEX idx_users_email ON users (email);

You can enforce uniqueness at the same time:

CREATE UNIQUE INDEX idx_users_email ON users (email);

SQLite creates indexes automatically in two cases: for PRIMARY KEY and for UNIQUE constraints. An INTEGER PRIMARY KEY column is special: it becomes an alias for the rowid itself, so it needs no separate index at all.

Composite Indexes and Column Order

An index on multiple columns is ordered by the first column, then the second within each value of the first, and so on. This left-to-right ordering is the rule that governs when the index helps.

CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);

This index serves:

• WHERE customer_id = 42
• WHERE customer_id = 42 AND order_date > '2026-01-01'

It does not help a query that filters only on order_date, because order_date is not the leading column. The rule of thumb: put the column you filter for equality first, and a range-scanned column last. For more on how multiple conditions combine, see our joins guide.

Partial Indexes

A partial index covers only the rows matching a WHERE clause. Supported since SQLite 3.8.0 (2013), they are smaller and cheaper to maintain when you only ever query a subset.

CREATE INDEX idx_open_orders ON orders (customer_id)
  WHERE status = 'open';

If most rows are closed and you almost always query open ones, this index indexes a fraction of the table. A query can use it only when its WHERE clause implies the index's condition, for example WHERE customer_id = 42 AND status = 'open'.

Expression Indexes

You can index the result of an expression rather than a raw column. These have been available since SQLite 3.9.0 (2015).

CREATE INDEX idx_users_email_lower ON users (lower(email));

A query that uses the identical expression can then use the index:

SELECT * FROM users WHERE lower(email) = 'ada@example.com';

This is the standard way to index case-insensitive lookups and JSON paths. The expression in the query must match the expression in the index exactly, or the planner ignores the index. See the JSON guide for indexing json_extract paths.

Covering Indexes

If an index contains every column a query needs, SQLite can answer the query from the index alone and never touch the table B-tree. This is a covering index, and EXPLAIN QUERY PLAN marks it with USING COVERING INDEX.

CREATE INDEX idx_orders_cover ON orders (customer_id, order_date, amount);
 
SELECT order_date, amount
FROM orders
WHERE customer_id = 42;

Because customer_id, order_date, and amount are all in the index, the lookup skips the extra step of fetching full rows. Covering indexes trade extra width and storage for fewer reads on hot queries.

Reading EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN shows the strategy SQLite picked. For each table it prints a line beginning with either SCAN or SEARCH.

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'ada@example.com';

QUERY PLAN
`--SEARCH users USING INDEX idx_users_email (email=?)

SEARCH ... USING INDEX means the index is doing its job. SCAN users with no index named means a full-table scan, which is fine for small tables but a red flag on large ones. Run EXPLAIN QUERY PLAN whenever you add an index, to confirm the planner actually uses it. The dedicated EXPLAIN QUERY PLAN guide covers the output in depth.

When an Index Does Not Help

Indexes are not free and not always used:

• Small tables. A full scan of a few hundred rows is faster than B-tree descents plus row lookups.
• Low selectivity. An index on a column with two values (status of 'open'/'closed') rarely helps if half the rows match; SQLite may scan instead.
• Functions on the column. WHERE lower(email) = ... cannot use a plain index on email; you need an expression index.
• Leading-column mismatch. A composite index is useless to a query that skips its first column.

Run ANALYZE periodically so SQLite has up-to-date statistics about value distribution. Without it the planner guesses, and it may pick a worse plan.

Common Mistakes

Indexing every column "just in case" -- each index slows writes and grows the file. Index the columns you actually filter, join, or sort on.

Forgetting the leading column rule -- a composite index (a, b) does nothing for a query that filters only on b.

Wrapping the indexed column in a function -- WHERE date(created_at) = '2026-06-02' defeats an index on created_at. Rewrite as a range, or build an expression index.

Not verifying with EXPLAIN QUERY PLAN -- adding an index does not guarantee it is used. Check the plan rather than assuming.

Exploring These Queries

Mako connects to SQLite with AI autocomplete and surfaces EXPLAIN QUERY PLAN output as you write. Try it at mako.ai.