What is a composite index and why does column order matter?
A composite index covers multiple columns together, like (last_name, first_name). It can serve queries filtering on last_name alone, or on both columns together — but not on first_name alone, because the index is physically sorted by the first column first. Column order should match your most common filter patterns, leading with the column most queries filter on.
Answered in
Database Indexing Explained: Which Index Type Actually Fits Your QueryB-tree, hash, composite, and covering indexes each solve a different query shape. Pick the wrong one and you pay index overhead without the speedup.
Read the full analysisOther questions this article answers
More system design questions
- Why does a database need an index at all — why can't it just scan the table?
- When is a hash index better than a B-tree index?
- What's a covering index and why is it faster than a normal index?
- What's the real cost of adding an index, beyond disk space?
- What does ACID actually stand for, and why do all four properties matter together?
- What's the practical difference between pessimistic and optimistic concurrency control?
- What is a race condition in a database transaction, and how does isolation prevent it?
- Why would a database ever allow a weaker isolation level than Serializable?
Every answer on Crashtech is written by the editor of the article it comes from — never auto-summarised. Browse all answers or the System Design beat.