Compound indexes in MongoDB: what developers should know

Building an efficient MongoDB query layer means understanding how your indexes actually work. Compound indexes in MongoDB: what developers should know is the foundation for optimizing multi-field queries, and getting them wrong can silently tank your application's performance. We'll walk through the real-world rules that govern how MongoDB uses these indexes and show you how to validate that your queries are actually using them.

Why compound indexes matter in production

When your application queries multiple fields together, a single well-designed compound index can serve dozens of query patterns. Without it, MongoDB falls back to collection scans or uses suboptimal indexes that only cover part of your filter criteria. In a trading system processing thousands of orders per second, the difference between a bad index strategy and a good one is the difference between 50ms queries and 2-second timeouts.

A compound index is simply an index on two or more fields, created in a specific order. That order is everything.

Field order matters: the equality-range-sort rule

The most important rule for compound indexes is the equality-range-sort (ERS) rule. Field order should follow this pattern: equality fields first, then range fields, then sort fields.

Let's say you're building a ride-hailing system and frequently query rides like this:

db.rides.find({
  status: "completed",
  createdAt: { $gte: new Date("2024-01-01"), $lt: new Date("2024-02-01") },
  rating: 4
}).sort({ distance: -1 })

A naive index like db.rides.createIndex({ status: 1, createdAt: 1, rating: 1, distance: -1 }) won't serve your sort efficiently. Instead, follow ERS:

// Correct compound index following ERS rule
db.rides.createIndex({
  status: 1,        // equality
  createdAt: 1,     // range
  distance: -1      // sort
})

Why? MongoDB can't use an index for sorting if there's a range condition on an earlier field. By putting status (equality) first, then createdAt (range), MongoDB efficiently narrows down the result set before attempting the sort on distance.

If you include fields that aren't part of your query at all—like rating in this example—you're wasting index space and potentially confusing the query planner.

Prefixes can be used by other queries

A powerful feature of compound indexes is index prefix optimization. If you create an index on { user_id: 1, status: 1, createdAt: 1 }, MongoDB can use that index for queries on:

• { user_id: 1, status: 1, createdAt: 1 } — the full index
• { user_id: 1, status: 1 } — first two fields
• { user_id: 1 } — first field only

But it cannot use this index for queries on just { status: 1 } or { createdAt: 1 }, because those don't start at the beginning of the index.

This is why field order is critical. In an e-commerce system, imagine these queries:

// Query A: find orders by user and status
db.orders.find({ user_id: ObjectId("..."), status: "shipped" })

// Query B: find all orders with high value
db.orders.find({ total_amount: { $gt: 1000 } })

// Query C: find orders by status across all users
db.orders.find({ status: "pending" })

If you create an index on { user_id: 1, status: 1, total_amount: 1 }, Query A gets full index support. Query C doesn't benefit at all because status isn't at the prefix. Query B can't use this index either.

The solution: think about your most common queries and index for those. Query A is probably your most frequent, so build the index around it. For Query C, if it's also critical, consider a separate index on just { status: 1 }. Index storage is cheap compared to slow queries.

Sort direction matters for multi-field sorts

When your query sorts on multiple fields, the direction you specify in the index must match. This is subtle but crucial.

Consider a travel booking system querying destinations:

db.destinations.find({
  country: "Spain",
  price: { $lt: 200 }
}).sort({ rating: -1, reviews: -1 })

Your index should be:

db.destinations.createIndex({
  country: 1,     // equality
  price: 1,       // range
  rating: -1,     // sort, descending
  reviews: -1     // sort, descending
})

Both sort fields must go the same direction in the index. If you had created { country: 1, price: 1, rating: 1, reviews: 1 } (all ascending), MongoDB cannot use it for this query because the sort direction doesn't match.

However, there's an exception: if you're sorting on a single field after equality and range filters, MongoDB can traverse the index in either direction. So an index with all ascending fields can still serve .sort({ rating: -1 }) efficiently—MongoDB just reads it backward. This breaks down once you have multiple sort fields.

Use explain() to confirm index usage

Never assume your index is working. Use explain() to verify.

db.orders.find({ user_id: ObjectId("..."), status: "completed" })
  .explain("executionStats")

Look for these signals:

• "stage": "COLLSCAN" — MongoDB scanned the entire collection. Your index isn't being used.
• "stage": "IXSCAN" — MongoDB used an index scan. Good. Check the executionStages.executionStages tree to see if sorting happened in-memory or in the index.
• "SORT" stage inside executionStages — MongoDB sorted in-memory after the index scan. This is expensive for large result sets.
• "totalDocsExamined" vs "nReturned" — these should be close. If MongoDB examined 10,000 docs to return 50, your index isn't selective enough.

Here's a real example from a CRM system:

const query = db.contacts.find({
  account_id: ObjectId("..."),
  created_at: { $gte: new Date("2024-01-01") }
}).sort({ last_contacted: -1 }).limit(20)

const stats = query.explain("executionStats")
console.log("Docs examined:", stats.executionStats.totalDocsExamined)
console.log("Docs returned:", stats.executionStats.nReturned)
console.log("Execution stages:", stats.executionStats.executionStages)

If totalDocsExamined is much higher than nReturned, your range filter isn't tight enough, or your index doesn't cover the range efficiently. If you see a SORT stage, add the sort field to your compound index.

Common mistakes

Mistake 1: Creating indexes without understanding your queries. Blindly indexing every field you filter on results in too many indexes and no real performance gain. Profile your workload first. Use MongoDB's built-in profiler or application logs to identify slow queries, then build indexes for those.

Mistake 2: Ignoring index direction on compound sorts. Developers often create all-ascending indexes, then wonder why multi-field sorts still cause in-memory sorting. Match your index direction to your sort direction for compound sorts.

Mistake 3: Putting range fields before equality fields. An index like { created_at: 1, user_id: 1 } can't efficiently filter by user_id first, then narrow by date range. Always equality first.

Mistake 4: Forgetting that prefixes work. Teams sometimes create overlapping indexes like { user_id: 1, status: 1 } and { user_id: 1 } separately, not realizing the first covers both queries. One compound index is enough.

Mistake 5: Not using explain() regularly. Code changes, query patterns shift, and what worked in staging doesn't always work in production under real load. Make explain() part of your code review process for data access layers.

Key takeaways

• Compound indexes serve multiple query patterns via prefix matching, but field order determines which queries benefit.
• Follow the equality-range-sort rule: put equality filters first, range filters second, sort fields last.
• Sort direction in the index must match your query sort direction when sorting on multiple fields.
• Always validate index usage with explain(); check for COLLSCAN stages, compare totalDocsExamined to nReturned, and look for in-memory sorting.
• One well-designed compound index beats several single-field indexes for common multi-field queries.
• Profile your actual workload before indexing; index what's slow, not what feels important.