Indexes

An index is a data structure that allows SQLite to find rows matching a condition without scanning every row in the table. Proper indexing is the single most impactful performance optimisation you can make on a read-heavy SQLite database.

How Indexes Work

Without an index, a query like:

SELECT * FROM orders WHERE customer_id = 42;

requires SQLite to read every row in orders and check whether customer_id = 42 — a full table scan. For a table with millions of rows this is slow.

An index on customer_id is a separate B-tree structure that maps each unique customer_id value to the rowids of matching rows. SQLite can look up 42 in the B-tree in O(log n) time and jump directly to the relevant rows.

Creating an Index

-- Single-column index
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Multi-column (composite) index
CREATE INDEX idx_orders_status_date ON orders (status, created_at);

-- Unique index — also enforces uniqueness like a UNIQUE constraint
CREATE UNIQUE INDEX idx_users_email ON users (email);

-- Conditional (partial) index — only indexes rows matching a WHERE clause
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

When SQLite Uses an Index

SQLite uses the query planner (EXPLAIN QUERY PLAN) to decide whether an index is beneficial:

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 42;

If the output shows SEARCH orders USING INDEX idx_orders_customer_id, the index is being used. If it shows SCAN orders, SQLite is doing a full table scan.

Composite Index Column Order

For composite indexes, column order matters. An index on (status, created_at) is useful for queries that filter on status alone or on both status and created_at, but it is not useful for queries that filter only on created_at:

-- Uses index (status is the leading column)
SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2024-01-01';

-- Does NOT use the composite index effectively
SELECT * FROM orders WHERE created_at > '2024-01-01';

Place the most selective column, or the column most frequently used alone in WHERE clauses, first.

Dropping Indexes

DROP INDEX idx_orders_customer_id;
DROP INDEX IF EXISTS idx_orders_customer_id;

The Cost of Indexes