indexing • Intermediate

Composite Indexes & The Leftmost Prefix Rule

A composite index on (A, B, C) can satisfy queries on (A), (A, B), and (A, B, C), but CANNOT be used efficiently for queries filtering only on (B) or (C).

Intuitive Mental Model

The Phonebook Alphabetical Ordering: Phonebooks are sorted by (LastName, FirstName). You can instantly find "Smith, John", or all "Smiths", but searching for anyone with first name "John" requires reading the entire book cover to cover.

SQL DDL / DML QueryPostgreSQL / MySQL

-- Composite Index definition:
CREATE INDEX idx_users_country_status_created 
ON users(country, status, created_at);

-- ✅ Uses full index:
SELECT * FROM users WHERE country = 'US' AND status = 'active' AND created_at > '2026-01-01';

-- ✅ Uses index on country:
SELECT * FROM users WHERE country = 'US';

-- ❌ CANNOT use index (Violates Leftmost Prefix):
SELECT * FROM users WHERE status = 'active';

Key Architectural Takeaways

•The Leftmost Prefix Rule: Index columns must be filtered in order from left to right without gaps.
•Equality First, Range Last: Place equality columns first (country = "US") and range columns last (created_at > ...).
•A composite index on (A, B) eliminates the need for a separate index on (A).

Common Production Mistake

Placing range filter columns before equality columns in composite index definition: ON (created_at, status).