The ESR (Equality, Sort, Range) Guideline

Equality

"Equality" refers to an exact match on a single value. The following exact match queries scan the cars collection for documents whose model field exactly matches Cordoba.

db.cars.find( { model: "Cordoba" } )
db.cars.find( { model: { $eq: "Cordoba" } } )

Index searches make efficient use of exact matches to reduce the number of index keys examined. Equality fields must come first. When you use the first part of the index for equality, the rest of the index stays in sorted order.

An index may have multiple keys for queries with exact matches. The index keys for equality matches can appear in any order. However, to satisfy an equality match with the index, all of the index keys for exact matches must come before any other index fields. MongoDB's search algorithm eliminates any need to arrange the exact match fields in a particular order.

The more selective the equality matches, the more efficient the indexed query.

Sort

"Sort" determines the order for results. To avoid in-memory sorts, put sort fields before range in the index.

An index can support sort operations when the query fields are a subset of the index keys. Sort operations on a subset of the index keys are only supported if the query includes equality conditions for all of the prefix keys that precede the sort keys. For more information, see Sort and Non-prefix Subset of an Index.

The following example queries the cars collection. The output is sorted by model:

db.cars.find( { manufacturer: "GM" } ).sort( { model: 1 } )

To improve query performance, create an index on the manufacturer and model fields:

db.cars.createIndex( { manufacturer: 1, model: 1 } )

• manufacturer is the first key because it is an equality match.

• model is indexed in the same order ( 1 ) as the query.

Range

"Range" filters scan fields. The scan doesn't require an exact match, which means range filters are loosely bound to index keys. To improve query efficiency, limit the range bounds and use equality matches to reduce the number of documents to scan.

Range filters resemble the following:

db.cars.find( { price: { $gte: 15000} } )
db.cars.find( { age: { $lt: 10 } } )
db.cars.find( { priorAccidents: { $ne: null } } )

If the range predicate in your query is very selective, place it before the sort fields to reduce the number of sorted documents and allow an in-memory sort.

To avoid an in-memory sort, place the range filter after the sort predicate. For more information on in-memory sorts, see cursor.allowDiskUse().

Additional Considerations

• Inequality operators such as $ne or $nin are range operators, not equality operators.

• $regex is a range operator.

• $in:

  • When $in is used alone, it is an equality operator that performs a series of equality matches.

  • When $in is used with .sort():

    • If $in has less than 200 array elements, the elements are expanded and then merged in the sort order specified for the index. This improves performance for small arrays. $in is similar to an equality predicate with ESR.

    • If $in has 200 elements or more, the elements are ordered like a range operator. In this scenario, the performance improvement for small arrays isn't realized. It isn't possible for the subsequent fields in the index to provide a sort, and $in is similar to a range predicate with ESR.

    • If you typically use $ins with small arrays, include $ins earlier in the index specification. If you typically use large arrays, include $ins where you would include a range predicate.

Example

The following query searches the cars collection for vehicles manufactured by Ford that cost more than $15,000 dollars. The results are sorted by model:

db.cars.find(
   {
       manufacturer: 'Ford',
       cost: { $gt: 15000 }
   } ).sort( { model: 1 } )

The query contains all the elements of the ESR Guideline:

• manufacturer: 'Ford' is an equality based match

• cost: { $gt: 15000 } is a range based match, and

• model is used for sorting

Following the ESR guideline, the optimal index for the example query is:

{ manufacturer: 1, model: 1, cost: 1 }

Learn More

• Create Indexes to Support Your Queries

• Use Indexes to Sort Query Results

• Create Selective Indexes to Answer Queries Efficiently