Composite Index Strategy
A composite index (col1, col2, col3) can be used by queries filtering on col1, col1+col2, or col1+col2+col3 — but NOT col2 or col3 alone.
MySQL
Advanced
5 min read
Composite Index Strategy
Example
-- Index on (status, customer_id, created_at)
CREATE INDEX idx_orders ON orders (status, customer_id, created_at);
-- Uses index (leftmost prefix)
SELECT * FROM orders WHERE status = 'open' AND customer_id = 5;
-- Does NOT use index (skips status)
SELECT * FROM orders WHERE customer_id = 5; Pro Tip
Put high-cardinality columns first in composite indexes.