Why does column order matter in a composite index?

Q: Why does column order matter in a composite index?

**Target Audience:** Developers who create multi-column indexes and want to predict which queries they help; candidates facing the classic "will this index be used?" quiz. **Difficulty:** Hard **Engines:** SQLite 3.45.0 through sql.js 1.10.2 (runnable plans) and PostgreSQL 16.15 (200,000-row plans). Skip-scan support in MySQL and PostgreSQL 18 is cited, not run. > **How to read this doc:** Plain language first, then a `📌 Interview term:` callout with the exact vocabulary. Every plan and count is from a real run; the **Verified** section quotes each example output in full. ## 1. Why This Even Matters — A Story First A phone book is sorted by surname, and within each surname by first name. Finding "Sharma, Priya" is instant: jump to Sharma, then to Priya. Finding every "Sharma" is easy too: they are all together. But finding everyone called "Priya", whatever the surname, means reading the whole book, because the Priyas are spread across every surname. Nothing about the book changed

The problem

Question presented to candidate: "We have an index on orders (customer_id, status, created_at). Which of these queries can use it, and how well: filter on customer_id; on customer_id and status; on status alone; on customer_id with a date range; and 'customer 10, paid, newest first'? If you could rebuild the index, how would you choose the column order?"

What a strong answer should cover:

A composite B-tree index is sorted by the first column, then by the second within equal first values, and so on, like a phone book sorted by surname then first name
The leftmost-prefix rule: the index can be searched on any leading prefix (a, a,b, a,b,c); skipping the first column usually means a full scan of the table or of the index
Equality columns first, then one range column; columns after the first range can no longer narrow the search, they only filter
The index order also serves ORDER BY (including backwards scans); mixed directions need matching ASC/DESC in the index
Engine details: skip scan (Oracle, MySQL 8.0.13+, SQLite with statistics, PostgreSQL 18) can use a missing leading column only when it has few distinct values; PostgreSQL 16 cannot. "Most selective column first" is a myth when all columns are compared with =

Clarifying questions expected:

Which queries run most often, and which use equality versus ranges on each column?
Do the queries also sort or paginate, and in which direction?
Which database and version (skip-scan support differs)?

Code / implementation expected: Optional — EXPLAIN output for each query shape, showing which columns become seek conditions.

Schema used by the examples:

composite-index

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers who create multi-column indexes and want to predict which queries they help; candidates facing the classic "will this index be used?" quiz. Difficulty: Hard Engines: SQLite 3.45.0 through sql.js 1.10.2 (runnable plans) and PostgreSQL 16.15 (200,000-row plans). Skip-scan support in MySQL and PostgreSQL 18 is cited, not run.

How to read this doc: Plain language first, then a 📌 Interview term: callout with the exact vocabulary. Every plan and count is from a real run; the Verified section quotes each example output in full.

1. Why This Even Matters — A Story First

A phone book is sorted by surname, and within each surname by first name. Finding "Sharma, Priya" is instant: jump to Sharma, then to Priya. Finding every "Sharma" is easy too: they are all together. But finding everyone called "Priya", whatever the surname, means reading

The solution is hidden so you can try first

15 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLLeftmost prefix: which WHERE clauses become seek conditions on (customer_id, status, created_at)

SQLThe index order also serves ORDER BY, but only when the columns before it are fixed by equality

SQLSQLite skip-scan: after ANALYZE, a missing low-cardinality first column can be skipped

PostgreSQL, 200,000 orders: index entries inside the scanned range for each column order

CREATE TABLE big_orders AS
SELECT g AS id,
       1 + g % 2000 AS customer_id,
       DATE '2024-01-01' + (g % 366) AS created_at,
       (g * 37) % 500 AS amount
FROM generate_series(1, 200000) AS g;
CREATE INDEX big_orders_cust_created ON big_orders (customer_id, created_at);
CREATE INDEX big_orders_created_cust ON big_orders (created_at, customer_id);
VACUUM ANALYZE big_orders;

SELECT relname AS index_name, relpages AS index_pages,
       CASE relname
         WHEN 'big_orders_cust_created' THEN
           (SELECT count(*) FROM big_orders WHERE customer_id = 42 AND created_at >= DATE '2024-12-01')
         ELSE
           (SELECT count(*) FROM big_orders WHERE created_at >= DATE '2024-12-01')
       END AS entries_in_scanned_range
FROM pg_class
WHERE relname IN ('big_orders_cust_created', 'big_orders_created_cust')
ORDER BY relname;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.