🔹 What Is a Composite Index?
A composite index is an index on multiple columns, like:
CREATE INDEX idx_user_email_status ON users(email, status);
This index helps queries that filter or sort using email, email AND status, but not just status alone.
🧠 When Composite Indexes Are Better
✅ 1. When Queries Use Multiple Columns Together
-- Good use case
SELECT * FROM users WHERE email = 'a@example.com' AND status = 'active';
The index idx_user_email_status helps here because both columns are used.
✅ 2. When You Have Filtering + Sorting
-- Example
SELECT * FROM orders
WHERE customer_id = 123
ORDER BY order_date DESC;
Use:
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
This speeds up both the filter and the sort.
⚠️ Caveats and Gotchas
❌ 1. Left-to-right prefix rule
Composite indexes only work from left to right:
CREATE INDEX idx(a, b, c)
- Helps:
WHERE a = ?,WHERE a = ? AND b = ? - ❌ Doesn’t help:
WHERE b = ?alone
❌ 2. Not always better than single-column indexes
- If you only ever query by one column, a single-column index is faster and smaller.
❌ 3. More indexes = slower inserts/updates
Every index must be updated during INSERT, UPDATE, or DELETE.
✅ Summary
| Situation | Composite Index | Better? |
|---|---|---|
| Query filters on multiple columns | ✅ Yes | |
| Query uses leftmost columns in WHERE or ORDER BY | ✅ Yes | |
| Query only uses non-leftmost column | ❌ No | |
| Many writes and rarely use multi-column filters | ❌ No | |
| Large table with sorting + filtering | ✅ Yes |