Indexing in SQL

How SQL indexes reduce query work

A SQL index keeps selected column values in an order the database can search. The right index can replace a broad scan or a temporary sort. The wrong index can take space, slow writes, and still miss the query you need to improve.

Column order follows the query

Suppose the query filters one customer and one status, then asks for newest orders first:

Schema

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);

Sample data

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 query

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

An index on customer_id alone can narrow the customer, but SQLite still has to test status and may sort the matches:

Before plan

SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
USE TEMP B-TREE FOR ORDER BY

A composite index can follow the equalities and then the requested order:

Change

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

After query

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

After plan

SEARCH orders USING INDEX idx_orders_customer_status_created (customer_id=? AND status=?)

The index starts with the columns that the query matches exactly. The next column supplies the order inside that matched group. This pattern is useful, but it is not a rule to apply without the real query and workload.

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 the index cannot serve.
The useful columns appear after a missing leading column in a composite index.
A new index duplicates an existing index and adds write cost without helping an important query.
The planner's statistics do not describe the current data well.

Review an index change

Name the query or group of queries 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 insert, update, delete, storage, and maintenance cost.
Remove a redundant index only after checking other queries that use it.

Engine choices differ

This guide uses SQLite plan records and rules. PostgreSQL and MySQL have different index types, statistics, cost models, and plan output. The useful habit is to connect the index column order to the query, then verify the database's actual choice.