How the ESR rule transformed my MongoDB query optimization workflow

MongoDB is a powerful database, but poorly optimized queries can quickly become a bottleneck as your dataset and users grow. In this post, I’ll share how I tackled performance issues in a system that relied heavily on dynamic filters, complex sorting, and range-based queries.

You’ll learn:

• What the ESR Rule is and how it helps optimize MongoDB queries.
• How to design queries and indexes that work together efficiently.
• Practical examples of how query performance improves with and without indexes.
• Additional tips for optimizing MongoDB queries.

By the end, you’ll have a clear understanding of how to align your MongoDB queries and indexes for better performance, especially in dynamic environments with large datasets.

The struggle: A personal journey

In a previous role as a full-stack engineer, I worked on multiple parts of the platform, including the front office and back office applications. However, the customer dashboard—the most query-intensive part of the system—was where performance challenges became most apparent.

This dashboard provided real-time data to administrators and property managers, allowing them to filter, sort, and paginate data. From what I can recall (it has been more than two years), the MongoDB collections supporting the dashboard stored about 1 million documents. While the flexibility of the dashboard was a strength, poorly optimized queries led to performance bottlenecks as the dataset grew.

Challenges we faced

The main problems stemmed from the types of queries powering the customer dashboard:

• Dynamic Filtering: Users could filter with a combination of multiple fields
• Complex Sorting: Sorting by multiple field created inefficiencies.
• Range Filters: Users often wanted results limited to specific timeframes (e.g., "users created in the last 6 months").

Performance bottlenecks

From what I remember:

• Documents examined: Hundreds of thousands per query.
• Documents returned: Only a few hundreds.
• Execution time: 5–10 s.

What caused these inefficiencies?

1. Ineffective Indexing: Our indexes didn’t align with the query structure, leading to full collection scans.
2. Multi-Field Sorting: Sorting on non-indexed fields forced MongoDB to perform expensive in-memory operations.
3. Range Filters: Applying a range filter without an appropriate index exacerbated the problem.

The training that changed everything

A MongoDB expert conducted a training session for our backend team, introducing us to several techniques to optimize query performance:

• The ESR Rule: We learned to structure both queries and indexes to follow the Equality → Sort → Range pattern for maximum efficiency.
• Dynamic Index Design: For systems with dynamic queries, creating multiple compound indexes for common query patterns was recommended. MongoDB’s query planner automatically selects the best index for a query.
• Explain Plan: We explored how to use MongoDB Compass and the explain() method to analyze query performance and validate index usage.
• Balancing Index Creation: While multiple indexes are helpful for read operations, excessive indexing can slow down writes and increase storage costs. We learned to balance indexing based on performance needs.

These lessons completely changed how we structured both queries and indexes, allowing us to address performance bottlenecks effectively.

What is the ESR rule?

The ESR rule is a framework for designing queries and indexes to work efficiently together. It emphasizes the following order:

1. Equality (E): Start with equality filters (field = value). These are the fastest way to narrow down datasets using indexes.
2. Sort (S): Apply sorting next. Sorting smaller, filtered datasets avoids in-memory operations.
3. Range (R): Apply range filters (e.g., field > value, field < value) last. These filters are less efficient and should always follow equality and sorting.

Why follow the ESR rule?

• Reduce the Number of Documents Scanned: Queries structured to follow this rule ensure MongoDB processes only the necessary documents.
• Avoid Blocking Sorts: Aligning queries and indexes prevents MongoDB from falling back to in-memory sorting.

Both queries and indexes must follow the same Equality → Sort → Range order to achieve full optimization.

Practical example: try it yourself

To demonstrate the ESR rule in action, let’s use a Books Library dataset. This dataset was generated using Mockaroo, a tool I frequently use as a software engineer. (Read more about my use of productivity tools in this blog post)

You can pull the dataset from my GitHub repository:
GitHub - MongoDB ESR Example Dataset.

We’ll evaluate the following cases:

1. Queries without an index
2. Queries with a basic index
3. Queries with an index following the ESR rule

Here’s a sample query that a librarian might use to retrieve highly rated Fiction books:

db.books
  .find({
    deleted: false,
    available: true,
    genre: "Fiction",
    price: { $gte: 5, $lte: 40 },
    rating: { $gte: 3.5 }
  })
  .sort({ publication_date: -1, rating: -1 });

This query retrieves Fiction books that:

1. Are available and not deleted (Equality conditions).
2. Cost between $5 and $40 (Range condition).
3. Have a rating of at least 3.5 stars (Range condition).
4. Are sorted by the most recent publication date and then by highest rating (Sorting).

Run this query in MongoDB Compass and analyze its performance using Explain Plan.

1. Query without an Index

Execution results:

• Documents examined: 200,000 (entire collection).
• Returned documents: 602 (matching books).
• Execution time: 251ms (this will vary based on dataset size and hardware).
• Index usage: None.
• Is sorted in memory: Yes

What happens without an index?

Without any index, MongoDB will:

1. Full collection scan: MongoDB scans every document in the collection to check for matches indicated by the COLLSCAN stage
2. In-memory sorting: Since there’s no index to support sorting, MongoDB sorts the results in memory.
3. Slow execution: This process is resource-intensive and slows down as the dataset grows.

2. Query with a basic index

Create the Index:

db.books.createIndex({
  deleted: 1,
  available: 1,
  genre: 1
});

Rerun the query in Compass and observe the changes.

Execution results:

• Documents examined: 10,030
• Returned documents: 602 (matching books).
• Execution time: 59ms.
• Index usage: Yes - (partial)
• Is sorted in memory: Yes

What happens with a partial index?

1. Index scan stage (IXSCAN): MongoDB uses the index to filter documents based on equality conditions (deleted, available, genre).
2. FETCH stage: Range filters (price, rating) are applied during this stage, as they are not covered by the index.
3. SORT stage: Sorting by publication_date and rating is done in memory, as the index does not include these fields.

This demonstrates that while a basic index can improve performance, it doesn’t fully optimize the query.

3. Query with an index following the ESR rule

To fully optimize the query, we need to create an index that aligns with the ESR rule:

1. Equality filters first.
2. Sorting fields next.
3. Range filters last.

Creating the ESR-aligned index

Here’s how to create the index:

db.books.createIndex({
  deleted: 1, 
  available: 1,
  genre: 1,
  publication_date: -1, // Sort field
  rating: -1,           // Sort field
  price: 1              // Range filter
});

Rerun the query in Compass and analyze the performance using Explain Plan.

Execution results:

• Documents examined: 602 (matching books).
• Returned documents: 602 (matching books).
• Execution time: 38ms.
• Index usage: Fully utilized.
• Is sorted in memory: No

In Compass, you’ll see that MongoDB used the index efficiently to handle equality, sorting, and range conditions. There’s no COLLSCAN, and sorting is performed directly via the index.

What happens with an ESR-aligned index?

1. MongoDB filters documents using equality conditions efficiently.
2. Sorting is handled directly by the index, eliminating in-memory operations.
3. Range filters (price, rating) are applied using the index, improving speed significantly.

Summary of query performance

Key takeaways

1. ESR Rule Matters: Structuring indexes and queries to follow the Equality → Sort → Range pattern results in dramatic performance improvements.
2. Avoid In-Memory Sorting: Aligning indexes with sorting fields eliminates the need for expensive in-memory operations.
3. Experiment and Validate: Use MongoDB Compass to test query performance and ensure indexes are being fully utilized.

Additional tips for optimizing MongoDB queries

1. Optimize $or Clauses: Index all fields used in $or conditions.
2. Analyze Queries: Use explain() or MongoDB Compass to identify performance bottlenecks.
3. Fetch Only Needed Fields: Use projections to minimize data transfer and processing.
4. Leverage Aggregation Pipelines: For complex transformations, aggregation pipelines are more efficient than ad-hoc queries.

For more details on improving MongoDB queries, explore:

• MongoDB Documentation on ESR Rule
• Tips and Tricks for Querying and Indexing in MongoDB

Let's wrap up

Optimizing MongoDB queries is not just about adding indexes—it’s about aligning queries and indexes for efficiency. The ESR rule helps minimize resource usage by structuring equality, sorting, and range operations effectively. By applying these principles and using tools like MongoDB Compass for analysis, I transformed slow queries into fast, efficient operations. Try these technique and see the difference for yourself—I’d love to hear your results in the comment section!