SQL Interview Prep · Lesson

Composite Index Column Order

The leftmost-prefix rule and choosing the right column order for a workload.

Composite Index Column Order is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Composite Index Question

Once you can explain a single-column index, interviewers raise the stakes: 'You query on customer_id and order_date together. How would you index that?' The expected answer is a composite (multi-column) index and a defense of the column order.

This lesson teaches the leftmost-prefix rule, the one idea that explains almost every composite-index question you will ever get.

What a Composite Index Is

A composite index indexes several columns as an ordered tuple. The entries are sorted first by the first column, then by the second within ties, and so on, exactly like a phone book sorted by last name, then first name.

Order matters enormously, because the sort priority follows the column order you declare.

CREATE INDEX idx_orders_cust_date
  ON orders (customer_id, order_date);

The Leftmost-Prefix Rule

The core rule: a composite index on (A, B, C) can be used for queries that filter on a leftmost prefix of those columns:

A alone, yes
A, B, yes
A, B, C, yes
B alone, NO
B, C, NO

Think of the phone book: you can find everyone named 'Smith', but you cannot efficiently find everyone whose first name is 'John' regardless of surname, because the book is not sorted that way.

Worked Example: Prefix Hits

With the index on (customer_id, order_date), these queries can use it because they lead with customer_id:

The first uses only the leading column. The second uses both, narrowing on customer_id first and then seeking within that customer's date-sorted entries.

-- Uses the index (leading column)
SELECT * FROM orders WHERE customer_id = 42;

-- Uses the index fully (both columns, in order)
SELECT * FROM orders
WHERE customer_id = 42
  AND order_date >= '2026-01-01';

Worked Example: The Prefix Miss

This query filters only on the second column, so the index on (customer_id, order_date) generally cannot drive the lookup, the entries are not globally sorted by order_date.

If filtering on order_date alone is a common pattern, you need a separate index that leads with order_date.

-- Does NOT use idx_orders_cust_date efficiently
SELECT * FROM orders
WHERE order_date >= '2026-01-01';

-- Fix: an index that leads with order_date
CREATE INDEX idx_orders_date ON orders (order_date);

Equality Before Range

A senior-level refinement: put columns used with equality (=) before columns used with a range (<, >, BETWEEN). Once the index hits a range column, it can no longer use later columns to narrow the seek.

For a query like status = 'paid' AND created_at > ?, the right order is (status, created_at), not the reverse.

-- Query: WHERE status = 'paid' AND created_at > '2026-01-01'
-- Good: equality column first
CREATE INDEX idx_orders_status_created
  ON orders (status, created_at);

Choosing the Leading Column

How do you pick which column goes first? Interviewers want a workload-driven answer:

Lead with the column that appears in the most queries, especially as an equality filter.
Prefer a column with high selectivity (many distinct values) so the first step eliminates the most rows.
Consider ORDER BY needs: matching the index order can avoid a sort.

The leading column is the one that does the heaviest filtering work across your real query mix.

Composite Index Helps Sorting

A composite index also serves ORDER BY on a leftmost prefix. Because entries are sorted by customer_id then order_date, a query for one customer's orders sorted by date is essentially free, no separate sort needed.

-- No sort step: the index already supplies this order
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY order_date;

Don't Stack Redundant Indexes

Candidates often over-index. If you already have (customer_id, order_date), a separate index on (customer_id) alone is usually redundant, the composite already covers leading-column queries.

Every extra index costs storage and slows writes, so the goal is the smallest set of indexes that covers your query patterns, not one index per column.

Proving It With EXPLAIN

As always, confirm the planner's choice. After creating a composite index, run EXPLAIN and check whether it appears as an Index Scan and how many rows it estimates.

If a prefix-mismatched query falls back to a sequential scan, that is your evidence the column order does not fit the workload, and exactly what you would explain to an interviewer.

EXPLAIN
SELECT * FROM orders
WHERE customer_id = 42
  AND order_date >= '2026-01-01';

How to Phrase It in the Interview

A crisp summary line:

'A composite index is sorted by its columns left to right, so it can serve any leftmost-prefix of those columns but not a trailing subset. I lead with the most-filtered, high-selectivity column, put equality predicates before range predicates, and match ORDER BY where possible, then verify with EXPLAIN.'

Quick Check

Apply the leftmost-prefix rule.

Recap: Composite Index Order

Key takeaways:

A composite index is sorted left to right; it serves only a leftmost prefix of its columns.
Lead with the most-queried, high-selectivity column.
Place equality columns before range columns.
A matching prefix can also satisfy ORDER BY with no sort.
Avoid redundant single-column indexes already covered by a composite, and verify with EXPLAIN.

Next: covering indexes that eliminate the heap fetch entirely.

Frequently asked questions

Is the “Composite Index Column Order” lesson free?

Yes — the full text of “Composite Index Column Order” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Composite Index Column Order”?

The leftmost-prefix rule and choosing the right column order for a workload. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Composite Index Column Order” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

B-Tree Indexes and How They Help
Composite Index Column Order
Covering Indexes and Index-Only Scans
When Indexes Hurt: Writes and Selectivity

← Back to SQL Interview Prep