Increase performance with ESR rule in Mongodb

ESR stands for Equality, Sort, Range. In a compound index Mongodb, the order of the keys can have a significant impact on query performance. The ESR rule provides a guideline for selecting the order of keys in a compound index that can optimize query performance.

Data that we will be use for testing different index setups

[
  {
    "name": "Mark",
    "age": 20,
    "creditLimit": 2000
  },{
    "name": "Andy",
    "age": 35,
    "creditLimit": 5000
  },{
    "name": "Claire",
    "age": 18,
    "creditLimit": 7000
  },{
    "name": "Andy",
    "age": 30,
    "creditLimit": 1000
  }
]

1. Equality before sort rule. Define a query

find({"name":"Andy"}).sort({"age":1})

• Create an index with wrong order of keys in index

{ "age": 1, "name": 1 }

• Lets trigger query and see the results and performance summary

{
 "name" : "Andy",
 "age" : 30,
 "creditLimit" : 1000
},
{
 "name" : "Andy",
 "age" : 35,
 "creditLimit" : 5000
}

Two documents returned and four index keys examined, this is not so good situation. See…