Composite Indexes and Column Order

The leftmost-prefix rule says a composite index on (a, b, c) can serve a query that filters on a alone, on a and b, on a and b and c, or on a and c — but not a query that filters only on b or only on c. The index is sorted by a first, so all the entries with the same a are adjacent, and within that, sorted by b. Without a value for a, the entries for a given b are scattered across the whole index, and the engine cannot seek to them.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status VARCHAR(20) NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

CREATE INDEX idx_orders_cust_status ON orders (customer_id, status);

-- Both columns: the index is used as a seek.
EXPLAIN QUERY PLAN
SELECT id FROM orders WHERE customer_id = 1 AND status = 'paid';

-- Leading column only: still a seek.
EXPLAIN QUERY PLAN
SELECT id FROM orders WHERE customer_id = 1;

-- Trailing column only: the index cannot be sought, only scanned.
EXPLAIN QUERY PLAN
SELECT id FROM orders WHERE status = 'paid';

Three plans come back. The first says SEARCH orders USING COVERING INDEX idx_orders_cust_status (customer_id=? AND status=?), and the second says the same with only customer_id=?. The third says SCAN orders USING COVERING INDEX idx_orders_cust_status.

Read the difference carefully. The third plan still mentions the index — because the index happens to contain every column the query needs, so the engine can scan the index instead of the table and avoid touching the table at all. But it is a scan, not a seek: every entry in the index is examined. That is still better than scanning the table, but it is not the O(log n) lookup the other two plans get.

There is one exception worth knowing, and it is the reason the rule is not absolute. If the leading column has very few distinct values, the engine can use a skip scan — SQLite, PostgreSQL and MySQL all have some form of it — where it seeks once per distinct value of the leading column and scans the narrow range within each. It helps when the leading column has two or three values and the table is large. It does not help when the leading column is unique, because then there is no benefit to scanning inside each value.

The practical consequence for schema design is that you should create the composite index with the columns in the order your queries use them, most selective first, and not in the order they happen to appear in the table definition.

When the queries against the table do not actually share a prefix. Two single-column indexes on customer_id and on status can both be used as seeks, which lets the engine pick whichever is more selective for any given query. A composite index on (customer_id, status) can only be sought on customer_id, so a query that filters only on status has to scan.

There are three other reasons to be careful. Write amplification: a composite index has to store every column of the key, so it is wider than either single-column index, and every insert has to write that whole key. Insertion cost: a wide composite index on a table with random primary keys means more page splits and more random I/O on insert than a narrow one. And maintenance: dropping a column that participates in a composite index is a much bigger operation than dropping one that has an index of its own.

The counter-argument, which is the reason composite indexes exist, is the covering case. A single composite index on the columns a query needs lets the engine answer the query entirely from the index — no table access at all — whereas two single-column indexes cannot do that, because each holds only one of the columns. On a wide table with a lot of random I/O, that difference dominates.

So the decision rule is about the query mix. If your queries almost always filter on customer_id and status together, one composite index on (customer_id, status) is strictly better: it serves those queries as a covering seek, and it serves customer_id-only queries as a seek too. If your queries filter on status alone a lot, or on customer_id and status independently in different queries, then you want the composite index plus a separate index on status — which is exactly the set of indexes the planner can choose between.