Composite Indexes
Overview
If a user runs a query searching for a specific last_name AND first_name, having two separate single-column indexes isn't highly efficient; the database has to scan both and mathematically merge the results. A Composite Index is a single B-Tree that stores multiple columns together (e.g., sorting by Last Name first, then First Name). The absolute most critical concept in Composite Indexes is the 'Left-Most Prefix Rule'.
Syntax
-- Creating a Composite (Multi-Column) Index
-- Order matters entirely! It sorts by last_name FIRST, then first_name.
CREATE INDEX idx_name_composite ON employees (last_name, first_name);
-- 1. SUPER FAST (Follows Left-Most Rule)
SELECT * FROM employees WHERE last_name = 'Smith';
-- 2. SUPER FAST (Follows Left-Most Rule)
SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';
-- 3. TERRIBLE / SLOW (Breaks Left-Most Rule!)
-- The database CANNOT use the index because we skipped the first column!
SELECT * FROM employees WHERE first_name = 'John';Common Pitfalls
- Breaking the Left-Most Prefix rule. A Composite Index is like a physical telephone book. The book is sorted by Last Name, then First Name. If I ask you to find all people whose First Name is 'John', the telephone book is completely useless to you; you have to scan every single page. If your queries often search by
first_nameALONE, you must create a separate, independent index for it. - Making the composite index too wide. Including 5 or 6 columns in an index creates a massive data structure that eats up RAM and slows down write operations.
Interview Questions
(city, status, age), will the index be utilized for the query: SELECT * FROM users WHERE city = 'Seattle' AND age = 30?Partially. Because the Left-Most rule is followed (city), the index will quickly jump to the 'Seattle' section. However, because the middle column (status) was omitted from the query, the index cannot optimize the age lookup. It will have to scan all Seattle rows manually to find the 30-year-olds.
Real-World Example
Optimizing a highly specific, high-traffic SaaS query: 'Find all unread notifications for a specific user, sorted by date'.
-- The ultimate optimized index for this exact query:
CREATE INDEX idx_user_unread_notifs
ON notifications (user_id, is_read, created_at DESC);
-- The Query that runs hyper-fast:
SELECT * FROM notifications
WHERE user_id = 42 AND is_read = FALSE
ORDER BY created_at DESC;Check Your Knowledge
Test your understanding of Composite Indexes with these quick questions.