Composite Indexes and Column Order
What You'll Learn
You'll discover how multi-column indexes work and why the order of columns in those indexes dramatically affects which queries can use them efficiently.
The Core Idea
A composite index (or multi-column index) covers multiple columns together, like creating an index on (customer_id, order_date) rather than separate indexes on each column. Think of it like a phone book sorted first by last name, then by first name—you can quickly find "Smith, John" but not "all Johns" efficiently.
The critical insight: column order matters immensely. An index on (A, B, C) works great for queries filtering on:
- Just
A AandBA,B, andC
But it's nearly useless for queries filtering only on B or C without A.
Why Order Matters
Indexes are stored in sorted order. With (customer_id, order_date), rows are sorted by customer_id first, then by order_date within each customer. The database can efficiently scan to a specific customer and then find dates within that customer's records. But if you query for "all orders on a specific date" without filtering by customer, the database must scan the entire index because matching dates are scattered throughout.
Choosing Column Order
Place the most selective (highest filtering power) columns first, and consider your most frequent query patterns:
- Columns used in equality conditions before range conditions
- Columns appearing in more queries first
- Most selective columns (fewest duplicates) typically first
Key Takeaway: Composite indexes are powerful but order-dependent; design them with your WHERE clause patterns in mind, remembering that the leftmost columns must appear in queries for the index to be useful.