MediumExperience level: 3-5 years · 8-15 years1 min read

How does a composite index work, and what is the leftmost-prefix rule?

Reviewed by Gurusankar M.

Asked inAmazonMicrosoftGoogle
#composite index#leftmost prefix#index order#multi-column
Report issue

Short Answer

A composite index on (a, b, c) is sorted by a, then b, then c. The optimizer can use it for predicates that include a left prefix — (a), (a,b), (a,b,c) — but NOT for (b) or (c) alone. Column order must match your query's filter/sort patterns.

On this page

Concept Made Simple

Coffee Chat Question

“How does a composite index work, and what is the leftmost-prefix rule?”

Remember It Faster

Mind Map Answer

Index (a,b,c)
sorted by a, then b, then c
Usable
a / a,b / a,b,c (left prefix)
NOT usable
b alone, c alone

Learn by Doing

Hands-on Keyboard

sql
CREATE INDEX idx ON orders (customer_id, status, created_at);
-- uses idx:  WHERE customer_id=? AND status=?
-- ignores idx: WHERE status=?   (skips leftmost customer_id)

Think Beyond the Expected

What If?

You have an index on (status, customer_id) but queries filter only by customer_id — why no speedup?

customer_id isn't the leftmost column, so the index can't seek on it directly. Reorder to (customer_id, status) to match the dominant query, or add a separate index on customer_id.

Real World

Column ordering in composite indexes is one of the highest-leverage tuning decisions; the wrong order leaves an index 'present but unused' for the queries that matter.

Interviewer's Expectation

Keywords they're listening for:

  • sorted by column order
  • leftmost-prefix rule
  • order by selectivity/query pattern
  • equality before range

Common Mistakes

  • Wrong column order for the dominant query
  • Expecting non-leftmost columns to be seekable
  • Creating many single-column indexes instead of one composite

Best Practices

  • Order columns: equality first, then range, by query pattern
  • Design indexes around real query predicates
  • Verify usage in the execution plan

Follow-up Questions

  1. Why put equality columns before range columns?
  2. How does column order interact with ORDER BY?
  3. When is one composite index better than several single-column ones?

Continue Learning with AI

Take this question deeper with your favourite AI assistant. Pick a depth, copy the prompt, or open it directly — AI is your learning companion, not a shortcut.

Plain-language foundations

I'm preparing for a software engineering interview and want to understand this from scratch, as a beginner.

Topic: Indexes (SQL)
Interview question: "How does a composite index work, and what is the leftmost-prefix rule?"

Please:
1. Explain the core idea in simple, plain language, using an everyday analogy.
2. Define any technical terms you use.
3. Walk through one small, concrete example.
4. Finish with a single sentence I can easily remember.

Keep the tone friendly and assume I'm new to this topic.
Open inChatGPTGeminiClaude

Was this answer helpful?

Affiliate links

Tools for Your Developer Workspace

Equipment for a comfortable, productive setup.

As an Amazon Associate I earn from qualifying purchases.