Indexes and EXPLAIN: making SQL queries fast

Without an index, the database answers WHERE customer_id = 3 by reading every row in the table. This is called a full table scan. An index works like the index at the back of a book: a sorted list of values, each pointing at the rows that contain it.

What an index is

The default index type in SQLite, PostgreSQL and MySQL (InnoDB) is a B-tree. It keeps the indexed values in sorted order, so the database can find one value, or a range of values, by following a few pages instead of scanning millions of rows.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Primary keys and UNIQUE constraints get an index automatically. Foreign key columns do not in PostgreSQL or SQLite, so index them yourself if you join or filter on them. MySQL’s InnoDB creates one for you.

Read the plan with EXPLAIN

EXPLAIN shows the plan the database chose without running the query. Here is the same query in SQLite, before and after creating the index above:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 3;
QUERY PLAN
`--SCAN orders

-- after CREATE INDEX:
QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer_id (customer_id=?)

SCAN means every row is read. SEARCH ... USING INDEX means the index narrows the lookup. Each database names these steps differently:

• SQLite: EXPLAIN QUERY PLAN. Plain EXPLAIN prints low-level bytecode.
• PostgreSQL: EXPLAIN shows estimates; Seq Scan is a full scan, while Index Scan, Index Only Scan and Bitmap Heap Scan use an index. EXPLAIN ANALYZE runs the query and adds real timings, so wrap data-changing statements in a transaction you roll back.
• MySQL: EXPLAIN prints a table; type: ALL means a full scan, and the key column names the index used. MySQL 8.0.18 and later also have EXPLAIN ANALYZE.

When an index helps

• Filters that match a small fraction of rows (selective conditions), such as an id or an email.
• Join columns, especially the foreign key side of a join.
• ORDER BY ... LIMIT on an indexed column, because rows can be read already sorted.
• Covering queries, where every column needed is in the index, so the table itself is never read. SQLite reports USING COVERING INDEX; PostgreSQL reports Index Only Scan.

When it does not help

• Small tables, or conditions that match a large share of rows. Reading the whole table can be cheaper, and the planner knows it.
• A function or arithmetic on the column: WHERE lower(email) = ... or WHERE customer_id + 0 = 3 cannot use a plain index on that column. Create an index on the expression instead, which SQLite, PostgreSQL and MySQL 8.0.13+ support.
• A leading wildcard: LIKE '%@example.com' cannot use a B-tree index, while LIKE 'ada%' sometimes can, depending on the database and collation.
• Type mismatches, such as comparing a text column to a number, which may force a conversion on every row.

Multi-column indexes

CREATE INDEX idx_orders_status_created ON orders (status, created_at);

-- Uses the index: the condition starts with the first column
SELECT id FROM orders WHERE status = 'paid' AND created_at >= '2026-02-01';
SELECT id FROM orders WHERE status = 'paid' ORDER BY created_at;

-- Cannot seek with it: status is not constrained
SELECT id FROM orders WHERE created_at >= '2026-02-01';

A composite index is sorted by the first column, then by the second within each first value, like a phone book sorted by last name and then first name. Put equality conditions first and range conditions last. In SQLite, the last query above may still read the whole index (SCAN ... USING COVERING INDEX), but that is a full scan, not a lookup.

Indexes are not free

Every INSERT, UPDATE and DELETE must also update every index on the table, and each index takes disk space. Add indexes for the queries you actually run, check with EXPLAIN that they are used, and drop ones that never appear in a plan.

Planners rely on statistics about your data. After large imports, run ANALYZE (SQLite, PostgreSQL) or ANALYZE TABLE orders (MySQL) so they can make good choices.