Composite Index
In one line
Composite indexes cover multiple columns — speed up queries filtering on several columns at once.
In simple words
A composite (multi-column) index indexes multiple columns together. The column order matters — it determines which queries can use the index.
CREATE INDEX idx ON orders(customer_id, order_date)
This index helps:
WHERE customer_id = 5— uses first columnWHERE customer_id = 5 AND order_date > '2024-01-01'— uses both columnsWHERE customer_id = 5 ORDER BY order_date— uses both (sorting)
But NOT:
WHERE order_date > '2024-01-01'— can't use index (order_date is second)WHERE order_date > '2024-01-01' AND customer_id = 5— partially (only customer_id part)
Rule: put the most selective column first, and columns used in equality before range. The leftmost prefix rule determines index usage.
Example
-- Composite index on (customer_id, order_date, status)
CREATE INDEX idx_orders_comp ON orders(customer_id, order_date, status);
-- Uses all 3 columns (optimal):
SELECT * FROM orders
WHERE customer_id = 42 AND order_date = '2024-01-15' AND status = 'shipped';
-- Uses first 2 columns:
SELECT * FROM orders WHERE customer_id = 42 AND order_date = '2024-01-15';
-- Uses first 1 column only:
SELECT * FROM orders WHERE customer_id = 42;
-- CANNOT use index (skips customer_id):
SELECT * FROM orders WHERE order_date = '2024-01-15';
-- Must scan the whole table or use another indexComposite index follows the leftmost prefix rule: it can use columns from left to right, but can't skip the first column. Order matters!
Common confusions
Confused with: single-column index
The difference: Single-column indexes help queries on one column. Composite indexes help queries on multiple columns simultaneously. A composite index on (A, B) is not the same as two single indexes on A and B — the composite can filter on both at once, but the singles can't be combined efficiently.
Related terms
Related Resources
Related Lessons
- Database Indexes
Composite indexes.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.