How does a composite index work, and what is the leftmost-prefix rule?
⚡ 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.
☕Coffee Chat Question
Concept Made Simple
“How does a composite index work, and what is the leftmost-prefix rule?”
🧠Mind Map Answer
Remember It Faster
⌨️Hands-on Keyboard
Learn by Doing
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)🔥What If?
Think Beyond the Expected
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:
⚠️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
- 1Why put equality columns before range columns?
- 2How does column order interact with ORDER BY?
- 3When is one composite index better than several single-column ones?
🧩Related Technologies
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.
Was this answer helpful?
⭐ Featured Products
Support our platform by exploring our recommended products.
As an Amazon affiliate, purchases through these links may earn us a small commission — at no extra cost to you. It helps keep Full Stack Interview Guru free.