Designing Composite Indexes: A Guide to SQL Performance

Previously in this course, we covered the fundamentals of how indexes function in Understanding Indexes: A Guide to Database Optimization. While single-column indexes are useful, they often fail to provide the performance gains needed for complex filtering. Today, we’re moving beyond simple structures to learn how composite indexes can significantly speed up multi-column query filters.

Understanding Composite Indexes

A composite index (also called a multi-column index) is an index defined on two or more columns of a table. While a single-column index helps you find rows based on one attribute, a composite index allows the database to "jump" directly to data that satisfies multiple conditions simultaneously.

Think of it like a library filing system. If you want to find a book by "Genre" and "Author," having two separate physical card catalogs (one for Genre, one for Author) forces you to cross-reference lists. A composite index is like a single catalog sorted first by Genre, and then by Author within each genre. You find the Genre section once, and all the relevant Authors are already grouped together.

Why Column Order Matters

The most critical aspect of designing composite indexes is the Left-Prefix Rule. Databases traverse B-tree indexes from left to right. If your index is defined as (column_a, column_b), the database can use this index for:

1. Queries filtering on column_a
2. Queries filtering on column_a AND column_b

However, if you only filter on column_b, the database cannot efficiently use this composite index because the data isn't sorted by column_b globally—it's only sorted by column_b within the buckets of column_a.

The Rule of Thumb: Place the most selective column (the one that filters out the most rows) first. If your app frequently queries WHERE status = 'active' AND created_at > '2023-01-01', an index on (status, created_at) is superior to one on (created_at, status) because status likely narrows the search space more effectively than a timestamp.

Worked Example: SaaS Activity Logs

In our ongoing SaaS project, imagine we have an activity_logs table. We frequently need to fetch logs for a specific account_id within a certain created_at range:

SQL
SELECT * FROM activity_logs 
WHERE account_id = 42 
AND created_at > '2023-01-01';

If we create two separate indexes, the database will struggle to pick the best one. Instead, we create a composite index:

SQL
CREATE INDEX idx_account_created ON activity_logs (account_id, created_at);

By placing account_id first, the database finds all rows for account 42 in one block. Because created_at is the second column, those rows are already sorted chronologically, allowing for a near-instant range scan.

Hands-on Exercise

Open your development environment and look at the subscriptions table we built in Implementing Subscription Tables: SQL Constraints for SaaS.

1. Identify a query in your application that filters by both account_id and plan_id.
2. Write the DDL to create a composite index on these two columns.
3. Compare this to having two separate single-column indexes. Why does the composite index perform better for that specific query?

Common Pitfalls

• Over-indexing: Creating a composite index for every possible query combination wastes storage and slows down INSERT, UPDATE, and DELETE operations because the database must update the index for every change.
• Ignoring the Left-Prefix Rule: Many developers create an index on (a, b, c) but then wonder why a query on (b, c) is still slow. Always verify your query WHERE clauses match the leading columns of your index.
• Including High-Cardinality columns too late: If you have a column with millions of unique values, put it early in the index to maximize the "pruning" power of the search.

FAQ

Q: Should I just make every index a composite index? A: No. Composite indexes are larger than single-column indexes. Only build them when you have a specific, high-frequency query pattern that requires filtering on multiple columns.

Q: Can a composite index replace a single-column index? A: Yes. If you have an index on (account_id, user_id), you do not need a separate index on account_id. The database can use the first part of the composite index for queries that only filter on account_id.

Recap

We’ve learned that composite indexes are essential for multi-column filtering performance. By mastering the Left-Prefix Rule and selecting the right column order based on selectivity, you can drastically reduce query latency. Always balance the speed of reads against the overhead of writes when designing your schema.

Up next: We will explore Index Maintenance and Trade-offs to ensure your indexes remain performant as your database grows.