Optimizing MongoDB Queries with Compound Indexes: Applying the Equality‑Sort‑Range Rule

What You Need to Know

When a query filters on one field, sorts on another, and applies a range condition on a third, a single compound index can eliminate both the collection scan and the in‑memory sort. The key is placing the fields in the order Equality‑Sort‑Range (ESR) so that MongoDB can use the index for all three operations.

How the ESR Rule Works

A compound index is a single B‑tree that stores multiple fields in a defined order. MongoDB can walk the tree once to satisfy an equality filter, then continue to a sort, and finally apply a range predicate. The index must be built so that each operation can be performed sequentially without backtracking.

Equality First

The field used in an equality predicate (e.g., status = "active") must be the leftmost part of the index. This lets MongoDB narrow the search to a single leaf node.

Sort Next

The field used for sorting (e.g., createdAt DESC) follows the equality field. MongoDB can then read the sorted documents directly from the index without an additional sort stage.

Range Last

Any range predicate (e.g., amount > 1000) must come after the sort field. If a range field appears before the sort field, the sort cannot be satisfied by the index and MongoDB falls back to a blocking sort.

Concrete Example

Suppose you have a collection orders with the following fields:

• status – low cardinality (e.g., "active", "completed")
• createdAt – timestamp used for ordering
• amount – numeric field used in a range filter

Create the index in the ESR order:

// Run in the mongo shell or a driver session
const indexSpec = {
  status: 1,      // equality
  createdAt: -1,  // sort (descending)
  amount: 1       // range
};
await db.orders.createIndex(indexSpec, { name: "status_createdAt_amount" });

Execute a representative query:

db.orders.find({
  status: "active",
  amount: { $gt: 1000 }
}).sort({ createdAt: -1 }).limit(20);

Verify that the index is used:

const explain = await db.orders.find({
  status: "active",
  amount: { $gt: 1000 }
}).sort({ createdAt: -1 }).limit(20).explain("executionStats");
printjson(explain.queryPlanner.winningPlan);

The output should contain a stage named IXSCAN and the totalSortSort field should be 0, indicating no blocking sort.

Common Pitfalls and Limitations

• Wrong Field Order: Placing amount before createdAt forces MongoDB to perform a sort, negating the benefit of the index.
• High‑Cardinality Range Field: If the range field has many distinct values, the index may become large and exceed RAM, causing the query planner to use a COLLSCAN.
• Write Overhead: Each insert, update, or delete must modify the index, so adding a compound index can slow write operations. Balance read performance with write load.
• Prefix Support: An index on { status, createdAt, amount } supports queries on { status } or { status, createdAt }, but NOT on { createdAt } alone. Ensure you create separate indexes if you need those patterns.
• Covered Queries: If the projection matches the index keys, MongoDB can return results directly from the index, further improving throughput. Verify with explain and check the indexOnly flag.

Practical Checklist

1. Identify the equality, sort, and range fields in your query.
2. Build a compound index in ESR order.
3. Run explain('executionStats') to confirm IXSCAN and zero totalSortSort.
4. Monitor write latency; add or drop indexes as needed.
5. Use db.collection.indexes() to verify index definitions.

Summary

By ordering compound index fields according to the Equality‑Sort‑Range rule, you enable MongoDB to filter, sort, and apply range predicates in a single index scan. This reduces CPU and I/O costs, eliminates blocking sorts, and can turn a slow query into a fast, covered operation. Always validate with explain and be mindful of write impact and index size.