Composite Indexes: One Ordering, Not Three

An index on (customer_id, created_at, status) serves some queries and not others, and which is which is not arbitrary. It follows entirely from how the entries are ordered on disk.

How is a composite index sorted?

A composite B-tree index is sorted by the first column. Within each value of the first column, entries are sorted by the second. Within each pair, by the third. There is one ordering, not three.

That is the whole model. A phone book sorted by last name, then first name, is the same structure: you can find everyone named Rowe, and among them everyone named Elias, but you cannot use it to find everyone named Elias regardless of surname. The Eliases are scattered through the book in as many places as there are surnames.

What follows

You can seek on a leading prefix. (a), (a, b), and (a, b, c) are all usable. Each narrows the range further within the previous one.

You cannot seek on a non-leading column alone. A predicate on b with nothing on a has no contiguous range to descend into. The engine may still read the whole index and filter — cheaper than the table if the index is much narrower, and a choice the planner makes from its row estimate — but that is a scan, not a seek, and it does not scale the way an index is supposed to.

Equality before range. This is the rule people get wrong most often. Once a predicate opens a range, everything after it is spread across that range:

-- Index on (created_at, status): the range opens first.
WHERE created_at >= '2026-01-01' AND status = 'pending'
-- Only created_at narrows the seek; status is a filter over everything after it.

-- Index on (status, created_at): equality first.
WHERE status = 'pending' AND created_at >= '2026-01-01'
-- Both narrow the seek: one status value, then a contiguous date range inside it.

Same predicates, same two columns, different amount of work.

Ordering comes free in one direction. An index on (a, b) can satisfy ORDER BY a, b without a sort. It cannot satisfy ORDER BY b, a. Mixed directions depend on the engine supporting descending index columns.

The exceptions

Skip scan. When the leading column has very few distinct values, an engine can loop over each of them and perform a seek on the rest. MySQL 8.0.13 added this as the Skip Scan Range Access Method, and Oracle Database has had index skip scan for much longer. It makes the leftmost-prefix rule a strong default rather than a law — but it depends on low cardinality in the leading column, so it does not rescue a badly ordered index in general. PostgreSQL 18 added B-tree skip scan; SQL Server’s Showplan operator reference lists no equivalent access method.

Full index scan as a narrow table. If the index is far smaller than the table and the query touches only indexed columns, reading the entire index and filtering can beat reading the table, even with no usable prefix. This is a real optimization and a bad thing to design for.

What order should composite index columns go in?

Take the queries the index is meant to serve. List the columns used with equality first, ordered by how many queries share them. Add the range column next. Add columns needed only for output last, and consider whether they belong as key columns at all — which is the subject of the next part.

One index in the right order usually replaces two in the wrong order, and each index you avoid creating is write throughput you keep. MySQL can make the redundant one invisible to the optimizer before it is dropped, which makes the removal a reversible test rather than a destructive change.

Limits

This describes B-tree indexes. Hash, GIN, and BRIN indexes have different structures and different rules; a GIN index over a composite type does not behave like a leftmost prefix at all. The reasoning here transfers only as far as the sorted-tuple structure does.