MongoDB ESR Rule Link to heading

In MongoDB, the ESR (Equality, Sort, Range) Rule describes ways of arranging index fields to optimize [[mongodb-compound-indexes|compound indexes]].

Equality Link to heading

Index searches make efficient use of match operators to limit the number of documents scanned. Place fields that require exact matches first in your index. This is crucial to reduce the work on the following Sort, and Range stages.

Sort Link to heading

An index can support sorting when the query fields are a subset of the index fields.

Range Link to heading

Range filters are loosely bound to index keys, since they don’t require an exact match. To improve query efficiency, make the range bounds as tight as possible.

Mongo cannot do an index sort after the range, so sorts should be placed before ranges.

Considerations Link to heading

  • Inequality operators such as $ne or $nin are range operators, not equality operators.
  • $regex is a range operator
  • $in can be a range operator or an equality operator.
    • When $in is used alone, it is an equality operator that performs a series of equality matches.
    • When $in is used with .sort(), $in can act like a range operator.

Example Link to heading

db.cars.find({
  manufacturer: 'Ford',
  cost: { $gt: 15000 }
}).sort({ model: 1 })
  • Equality match for manufacturer.
  • Range match for cost.
  • Sort based on model

Based on ESR, the optimal index for the example query is:

{manufacturer: 1, model: 1, cost: 1}
  • [[mongodb-compound-indexes|MongoDB Compound Indexes]]