Database indexing strategies: Mastering composite indexes for speed

Last month, a service in our stack started timing out during peak traffic. The culprit was a simple filtering query that had grown from a few thousand rows to over three million, turning an index scan into a multi-second full table scan.

We often talk about Indexing Strategy for App Developers: Stop Slow Queries as if it’s just about slapping an index on a foreign key. But when you’re dealing with complex filtering—like searching by status, user_id, and created_at—a single-column index isn't enough. You need to understand how to build composite indexes that actually get used by the database engine.

Understanding the Left-Prefix Rule

The most common mistake I see developers make is assuming that adding three individual indexes on status, user_id, and created_at will magically make a query filtering by all three fast. It won't. The database engine will usually pick only one of those indexes and perform a filter on the remaining columns in memory.

A composite index follows the "Left-Prefix" rule. If you have an index on (A, B, C), the database can use that index for queries filtering on:

• A
• A and B
• A, B, and C

It cannot efficiently use that index if you only filter by B and C. The sequence matters immensely. I usually arrange columns by cardinality—putting the most selective column (the one with the most unique values) first—unless a specific query pattern dictates otherwise.

Why Composite Indexes for Multi-Column Query Patterns?

When I look at EXPLAIN plans, I often see "Index Merge" operations. While that sounds like a win, it’s actually a sign that the database is struggling to combine multiple indexes to satisfy a single query. If you’re serious about reading an EXPLAIN plan without panic: A Backend Engineer’s Guide, you’ll notice that an index merge is significantly more expensive than a single range scan on a well-designed composite index.

Let’s look at a concrete example. Suppose we have a notifications table and we frequently run this query:

SQL
SELECT * FROM notifications 
WHERE user_id = 123 
AND status = 'unread' 
ORDER BY created_at DESC;

If I index (user_id, status, created_at), the database engine traverses the B-Tree for user_id, narrows it down by status, and then the data is already sorted by created_at. This avoids a "filesort" operation, which is a common source of high latency in production.

The Trade-off: Write Amplification

Before you go wild creating indexes, remember that every index you add slows down INSERT, UPDATE, and DELETE operations. Each index is a separate data structure that must be updated whenever the underlying table changes.

We once added five different composite indexes to a high-write table to satisfy various reporting dashboards. Our write latency jumped by about 40%. We eventually had to remove two of them and shift the heavy reporting logic to a read-replica. Always balance your read requirements against the write cost. If you're struggling with performance, it might be time to look at when to denormalize your database for production performance rather than just adding more indexes.

Practical Steps to Optimize

1. Identify the Slowest Queries: Don't guess. Use your slow query log or APM tool to find the top 3 offenders.
2. Review the EXPLAIN Plan: Look for type: ALL or Extra: Using filesort. These are your red flags.
3. Design the Index: Arrange columns starting with equality filters (e.g., user_id = 123), followed by range filters (e.g., created_at > '2023-01-01').
4. Test: Never deploy an index to production without testing it against a staging environment that has a representative data set (at least 100k+ rows).

FAQ: Common Questions

Q: Should I index every column used in a WHERE clause? A: No. Too many indexes will kill your write performance. Focus on the queries that are actually slow.

Q: Does the order of columns in a composite index really matter? A: Yes, absolutely. Because of the Left-Prefix rule, (A, B) is not the same as (B, A).

Q: Is it ever better to have two separate indexes? A: Sometimes, yes. If you have two completely different query patterns that don't share a common prefix, separate indexes might be necessary.

I’m still experimenting with partial indexes for some of our larger tables to keep index sizes manageable. It's a balancing act. You have to monitor the Index_usage stats in your database engine and be prepared to prune indexes that aren't providing enough value to justify their storage and write-time costs. Start small, verify with EXPLAIN, and keep an eye on your write latency.