--- title: "Composite Indexes: One Ordering, Not Three" description: "Column order decides which queries a composite index can serve. The rule follows from the structure, and so do the cases where it does not hold." url: https://sqlpress.com/blog/composite-index-column-order/ category: SQL Fundamentals tags: ["indexing", "query-optimization", "schema-design"] author: "Elias Rowe" published: 2026-09-27T00:00:00.000Z measured: false --- # Composite Indexes: One Ordering, Not Three ## Key takeaways - A composite index is sorted by its first column, then by its second within each value of the first, and so on — every rule about column order follows from that. - An index on (a, b, c) can seek on a, on a and b, or on all three; it cannot seek on b alone, because b is only ordered within a given a. - Put equality predicates before range predicates: once a range opens, the columns after it are no longer in a usable order. ## Test environment - System: PostgreSQL 18 - System: MySQL 8.4 - System: SQL Server 2022 - System: Oracle Database 23ai - Workload: Illustrative (examples verified, nothing timed) - Notes: Engine-specific claims here are skip scan support, which differs across these four, and MySQL's invisible indexes. 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](/blog/index-not-used-estimate-not-index/) — 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: ```sql title="order-matters.sql" -- 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. ## FAQ ### Why can't my query use the second column of a composite index? A composite B-tree index is sorted by its first column, then by the second within each value of the first. A predicate on the second column alone has no contiguous range to descend into, because those values are scattered across every value of the leading column. The engine can still read the whole index and filter, but that is a scan rather than a seek, and it does not scale the way an index is supposed to. ### Should equality or range columns come first in a composite index? Equality first. Once a predicate opens a range, every column after it is spread across that range and can only be used as a filter, not to narrow the seek. An index on status then created_at serves a query for one status inside a date window by seeking twice: one status value, then a contiguous date range within it. The same index with the columns reversed narrows on the date alone. ### When does the leftmost-prefix rule not apply? Skip scan is the main exception. When the leading column holds very few distinct values, an engine can loop over each of them and seek on the remaining columns. MySQL added this as the Skip Scan Range Access Method in 8.0.13, Oracle Database has had index skip scan far longer, and PostgreSQL 18 added it for B-tree indexes. It depends on low cardinality in the leading column, so it does not rescue a badly ordered index in general. ### Can a composite index satisfy ORDER BY without a sort? An index on (a, b) can satisfy ORDER BY a, b without a sort, because that is already the order its entries are stored in. It cannot satisfy ORDER BY b, a, since b is only ordered within a given a. Mixed ascending and descending directions depend on the engine supporting descending index columns. ## Sources - [PostgreSQL 18 documentation — Multicolumn Indexes](https://www.postgresql.org/docs/18/indexes-multicolumn.html) (docs) - [MySQL 8.4 Reference Manual — Multiple-Column Indexes](https://dev.mysql.com/doc/refman/8.4/en/multiple-column-indexes.html) (docs) - [MySQL 8.4 Reference Manual — Skip Scan Range Access Method](https://dev.mysql.com/doc/refman/8.4/en/range-optimization.html) (docs) — The mechanism and the conditions under which the optimizer applies it. - [MySQL 8.0.13 release notes](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html) (release notes) — The Skip Scan access method was added in this release. - [PostgreSQL 18 release notes](https://www.postgresql.org/docs/18/release-18.html) (release notes) — Skip scans of B-tree indexes were added in this release. - [Oracle Database 23ai SQL Tuning Guide — Optimizer Access Paths](https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/optimizer-access-paths.html) (docs) — Index skip scan, and why it depends on few distinct values in the leading key. - [Microsoft Learn — Showplan Logical and Physical Operators Reference](https://learn.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference) (docs) — SQL Server's index access operators; no skip-scan equivalent appears in the list. - [MySQL 8.4 Reference Manual — Invisible Indexes](https://dev.mysql.com/doc/refman/8.4/en/invisible-indexes.html) (docs) — Maintained but hidden from the optimizer, so removal can be tested without dropping.