Composite Index Column Order
The leftmost-prefix rule and choosing the right column order for a workload.
The Composite Index Question
Once you can explain a single-column index, interviewers raise the stakes: 'You query on customer_id and order_date together. How would you index that?' The expected answer is a composite (multi-column) index and a defense of the column order.
This lesson teaches the leftmost-prefix rule, the one idea that explains almost every composite-index question you will ever get.
What a Composite Index Is
A composite index indexes several columns as an ordered tuple. The entries are sorted first by the first column, then by the second within ties, and so on, exactly like a phone book sorted by last name, then first name.
Order matters enormously, because the sort priority follows the column order you declare.
CREATE INDEX idx_orders_cust_date
ON orders (customer_id, order_date);All lessons in this course
- B-Tree Indexes and How They Help
- Composite Index Column Order
- Covering Indexes and Index-Only Scans
- When Indexes Hurt: Writes and Selectivity