The ESR (Equality, Sort, Range) Guideline

Equality

The examples on this page use data from the sample_mflix sample dataset. For details on how to load this dataset into your self-managed MongoDB deployment, see Load the sample dataset. If you made any modifications to the sample databases, you may need to drop and recreate the databases to run the examples on this page.

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

db.movies.find( 
   { title: "Equilibrium" },
   { title: 1, year: 1, cast: 1 }
)

[
  {
    _id: ObjectId('573a13a3f29313caabd0c8d2'),
    year: 2002,
    title: 'Equilibrium',
    cast: [
      'Christian Bale',
      'Dominic Purcell',
      'Sean Bean',
      'Christian Kahrmann'
    ]
  }
]

db.movies.find( 
   { title: { $eq: "Equilibrium" } }, 
   { title: 1, year: 1, cast: 1 }
)

[
  {
    _id: ObjectId('573a13a3f29313caabd0c8d2'),
    year: 2002,
    title: 'Equilibrium',
    cast: [
      'Christian Bale',
      'Dominic Purcell',
      'Sean Bean',
      'Christian Kahrmann'
    ]
  }
]

Index searches make efficient use of exact matches to reduce the number of index keys examined. Equality fields must come first.

An index can have multiple equality keys. They can appear in any order relative to each other, but all equality keys must precede any sort or range fields.

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 supports sort operations on a subset of its keys only when the query includes equality conditions on all prefix keys that precede the sort keys. For more information, see Sort and Non-prefix Subset of an Index.

The following example queries the movies collection for movies where the directors field contains "David Lynch". The output is sorted by year:

db.movies.find( 
   { directors: "David Lynch" }, 
   { title: 1, year: 1 } 
).sort( { year: 1 } )

[
  {
    _id: ObjectId('573a1397f29313caabce77d4'),
    title: 'The Elephant Man',
    year: 1980
  },
  {
    _id: ObjectId('573a1398f29313caabce9091'),
    title: 'Dune',
    year: 1984
  },
  {
    _id: ObjectId('573a1398f29313caabce9e12'),
    title: 'Blue Velvet',
    year: 1986
  },
  {
    _id: ObjectId('573a1399f29313caabced630'),
    year: 1992,
    title: 'Twin Peaks: Fire Walk with Me'
  },
  {
    _id: ObjectId('573a139af29313caabcf00f0'),
    year: 1997,
    title: 'Lost Highway'
  },
  {
    _id: ObjectId('573a139ef29313caabcfbc0e'),
    year: 1999,
    title: 'The Straight Story'
  },
  {
    _id: ObjectId('573a139ef29313caabcfbc36'),
    title: 'Mulholland Drive',
    year: 2001
  },
  {
    _id: ObjectId('573a13b4f29313caabd40a54'),
    title: 'Inland Empire',
    year: 2006
  }
]

To improve query performance, create an index on the directors and year fields:

db.movies.createIndex( { directors: 1, year: 1 } )

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

• year 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.movies.find( 
   { runtime: { $gte: 1000 } }, 
   { title: 1, runtime: 1, year: 1, plot: 1 } 
)

[
  {
    _id: ObjectId('573a1397f29313caabce69db'),
    plot: 'The economic and cultural growth of Colorado spanning two centuries from the mid-1700s to the late-1970s.',
    runtime: 1256,
    title: 'Centennial',
    year: 1978
  },
  {
    _id: ObjectId('573a1399f29313caabcee1aa'),
    plot: 'A documentary on the history of the sport with major topics including Afro-American players, player/team owner relations and the resilience of the game.',
    runtime: 1140,
    title: 'Baseball',
    year: 1994
  }
]

db.movies.find( 
   { year: { $lt: 1900 } }, 
   { title: 1, year: 1, plot: 1 }
)

[
  {
    _id: ObjectId('573a139cf29313caabcf560f'),
    plot: 'Two people kiss.',
    title: 'The Kiss',
    year: 1896
  },
  {
    _id: ObjectId('573a13a0f29313caabd041db'),
    plot: 'Two people kiss.',
    title: 'The Kiss',
    year: 1896
  }
]

db.movies.find( 
   { type: { $ne: "movie" } }, 
   { title: 1, year: 1, type: 1 } 
)

[
  {
    _id: ObjectId('573a1395f29313caabce2f03'),
    title: 'The Forsyte Saga',
    year: 1967,
    type: 'series'
  },
  {
    _id: ObjectId('573a1396f29313caabce520d'),
    title: 'Scenes from a Marriage',
    year: 1973,
    type: 'series'
  },
  {
    _id: ObjectId('573a1396f29313caabce5b86'),
    title: 'Ironiya sudby, ili S legkim parom!',
    year: 1975,
    type: 'series'
  },
  {
    _id: ObjectId('573a1397f29313caabce6378'),
    title: 'Sybil',
    year: 1976,
    type: 'series'
  },
  {
    _id: ObjectId('573a1397f29313caabce6443'),
    title: 'Jesus of Nazareth',
    year: 1977,
    type: 'series'
  }
]

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 fewer than 201 array elements, the elements are expanded and then merged in the sort order specified for the index using a SORT_MERGE stage. This improves performance for small arrays. In this case, $in is similar to an equality predicate with ESR.

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

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

Example

The following query searches the movies collection for movies where the directors field is "David Lynch" and the runtime field is less than 130 minutes. The results are sorted by year:

db.movies.find(
   { 
      directors: "David Lynch", 
      runtime: { $lt: 130 } 
   },
   { title: 1, year: 1, runtime: 1 }
).sort( { year: 1 } )

[
  {
    _id: ObjectId('573a1397f29313caabce77d4'),
    runtime: 124,
    title: 'The Elephant Man',
    year: 1980
  },
  {
    _id: ObjectId('573a1398f29313caabce9e12'),
    runtime: 120,
    title: 'Blue Velvet',
    year: 1986
  },
  {
    _id: ObjectId('573a139ef29313caabcfbc0e'),
    year: 1999,
    title: 'The Straight Story',
    runtime: 112
  }
]

The query contains all the elements of the ESR Guideline:

• directors: "David Lynch" is an equality-based match

• runtime: { $lt: 130 } is a range-based match

• year is used for sorting

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

{ directors: 1, year: 1, runtime: 1 }

Learn More

• Create Indexes to Support Your Queries

• Use Indexes to Sort Query Results

• Create Selective Indexes to Answer Queries Efficiently