Composite Indexes and Column Order
You'll learn how multi-column indexes work and why the order of columns dramatically affects which queries benefit from them.
What is a Composite Index?
A composite index (also called a multi-column or compound index) spans multiple columns in a single index structure. Instead of indexing just last_name, you might create an index on (last_name, first_name) or (user_id, created_at).
Why Column Order Matters
Think of a composite index like a phone book sorted by last name, then first name. This organization helps you quickly find "Smith, Alice" or all the Smiths. But if you only know someone's first name ("Alice"), the phone book isn't helpful—you'd still scan every page.
The leftmost prefix rule: A composite index on (A, B, C) can efficiently support queries that filter on:
AaloneAandBA,B, andC
But it cannot efficiently support queries filtering only on B, only on C, or B and C together. The index is physically ordered by the first column, then the second within each first-column value, and so on.
Practical Design Decisions
When creating INDEX (user_id, created_at):
- Fast:
WHERE user_id = 123 AND created_at > '2024-01-01' - Fast:
WHERE user_id = 123 - Slow:
WHERE created_at > '2024-01-01'(can't use this index)
Order matters most: Place the most selective (highest-filtering) column first, or the column most frequently used alone. If you query by user_id alone often, but rarely by created_at alone, put user_id first.
Key Takeaway:
Composite indexes support queries that filter on the leftmost columns in the index definition—column order determines which queries can benefit, so design indexes based on your actual query patterns.