Composite Indexes
A composite index is an index on multiple columns together. The order of columns matters: the leftmost column principle determines which queries benefit. Composite indexes can make a single index serve many query patterns efficiently.
Composite Index Rules
- CREATE INDEX name ON table(col1, col2, col3)
- Leftmost prefix rule: index on (col1, col2) helps queries filtering by col1 alone, or (col1 AND col2) - but NOT col2 alone
- (col1, col2) index: beneficial for WHERE col1=x, WHERE col1=x AND col2=y, ORDER BY col1, ORDER BY col1, col2
- NOT beneficial for WHERE col2=y alone
- Put high-selectivity, most-often-filtered column first
- Composite index also acts as a single-column index on its first column
Composite Index Examples
-- Index for common query: WHERE user_id = ? AND status = ?
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Uses index: ✅
SELECT * FROM orders WHERE user_id = 1 AND status = 'pending';
SELECT * FROM orders WHERE user_id = 1; -- leftmost prefix
SELECT * FROM orders WHERE user_id = 1 ORDER BY status;
-- Does NOT use index: ❌ (status alone - not leftmost)
SELECT * FROM orders WHERE status = 'pending';
-- For this query, create a separate index on (status)
-- Index covers ORDER BY too
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);
-- Helps: WHERE user_id = 1 ORDER BY created_at DESC (instant, no sort!)
-- Covering composite index
CREATE INDEX idx_orders_full ON orders(user_id, status)
INCLUDE (total_amount, created_at);
-- Query answered entirely from index:
SELECT total_amount, created_at FROM orders
WHERE user_id = 1 AND status = 'delivered';
-- Check column order matters
-- Scenario: 1M orders, 1K users, 5 statuses
-- Most selective first: WHERE user_id = 1 (1K/1M = 0.1%)
-- Less selective: WHERE status = 'pending' (200K/1M = 20%)
-- Correct order: (user_id, status) NOT (status, user_id)Quick Quiz
Tip
Tip
Practice Composite Indexes in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Index WHERE, JOIN, ORDER BY columns. Don't over-index.
Common Mistake
Warning
A common mistake with Composite Indexes is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Composite Indexes from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- A composite index is an index on multiple columns together.
- CREATE INDEX name ON table(col1, col2, col3)
- Leftmost prefix rule: index on (col1, col2) helps queries filtering by col1 alone, or (col1 AND col2) - but NOT col2 alone
- (col1, col2) index: beneficial for WHERE col1=x, WHERE col1=x AND col2=y, ORDER BY col1, ORDER BY col1, col2