How SQL indexes reduce query work

A SQL index stores selected column values so the database can find rows or return them in order. An index that fits the query can reduce scanning or sorting. An unsuitable index uses storage and adds work to writes without improving the query.

3 minute read
Examples run in SQLite

On this page

Column order follows the query

Suppose a query filters one customer and one status, and then asks for the newest orders first. The table already has an index on customer_id alone.

Schema and sample SQL

Schema and sample data

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  created_at TEXT NOT NULL,
  total_cents INTEGER NOT NULL
);
CREATE INDEX idx_orders_customer ON orders(customer_id);

INSERT INTO orders (id, customer_id, status, created_at, total_cents) VALUES
  (1, 7, 'paid', '2026-01-05', 3200),
  (2, 7, 'pending', '2026-01-06', 1100),
  (3, 7, 'paid', '2026-01-02', 1600),
  (4, 8, 'paid', '2026-01-04', 2900);

Before

SELECT id, created_at, total_cents
FROM orders
WHERE customer_id = 7 AND status = 'paid'
ORDER BY created_at DESC;

The index on customer_id can find that customer's rows, but SQLite still has to check status and may sort the matches.

SEARCH orders USING INDEX idx_orders_customer (customer_id=?)Finds the matching rows through an index.
USE TEMP B-TREE FOR ORDER BYUses a temporary structure to sort, group, or remove duplicates.

Open in the Visualizer

After CREATE INDEX idx_orders_customer_status_created

CREATE INDEX idx_orders_customer_status_created
  ON orders(customer_id, status, created_at DESC);

A composite index contains several columns. Here, the two columns checked for equality come before the column used to sort the result.

SEARCH orders USING INDEX idx_orders_customer_status_created (customer_id=? AND status=?)Finds the matching rows through an index.

Open in the Visualizer

The index starts with the columns that the query matches exactly. The next column orders the matching rows. Check the actual query and workload before applying the same column order elsewhere.

When an index may not help

The table is small enough that a scan costs less.
The filter returns most of the table.
The query wraps the indexed column in an expression that the index cannot serve.
In a composite index, the useful columns come after a leading column that the query does not filter on.
A new index repeats an existing index and adds write cost without helping an important query.
The planner's statistics do not describe the current data well.

Key points

Name the query or group of queries that the index serves.
Put equality filters before a range or order column when the workload supports that order.
Compare the plan before and after the index.
Confirm the same rows and order.
Check the cost for inserts, updates, deletes, storage, and maintenance.
Remove a redundant index only after you check the other queries that use it.

Engine choices differ

The examples use SQLite plan records and indexing rules. PostgreSQL and MySQL support different index types and can choose different plans. Match the index columns to the query, then check the plan and performance in the database you use.

Practice this in Module 3: Indexes

Ten exercises on composite index order, ranges and covering indexes. The first one filters by a composite prefix.

See Module 3