Any database must have an index, thus MongoDB is not exceptional in this regard. MongoDB features a tonne of interesting index types with fantastic properties based on the use cases it was created for.

It has been a very long time since I considered writing a series of blogs about the MongoDB index at all skill levels. Finally, we have arrived, and I hope you will enjoy reading my blog.

In the first session of this series, I’ll walk you through the MongoDB indexes and the guidelines for building them.

Indexes

Indexes are used to store the value of a specific field or set of fields, ordered by the value of the field. The ordering of the index entries supports efficient equality matches and range-based query operations. In addition, MongoDB can return sorted results by using the ordering in the index.

Indexes support the efficient execution of queries in MongoDB. Without indexes, MongoDB must perform a collection scan, i.e. scan every document in a collection, to select those documents that match the query statement.

In order to work with indexes, We need to learn the index’s types and properties.

ESR Rule

The ESR (Equality, Sort, Range) Rule is a guide to creating indexes that support your queries efficiently.

Equality

“Equality” refers to an exact match of a single value. The following exact match queries scan the collection for documents whose name field exactly matches XYZ.

 
db.people.find({name: XYZ})
	

Index searches make efficient use of exact matches to limit the number of documents that need to be examined to satisfy a query. Place fields that require exact matches first in your index.

Sort

“Sort” determines the order of results. Sort follows equality matches because equality matches reduce the number of documents that need to be sorted. Sorting after the equality matches also allows MongoDB to do a non-blocking sort.

The following example queries the people collection. The output is sorted by age.

 
db.people.find({name:"xyz"}).sort({age:-1})
	

To improve query performance, create an index on the name and age fields.

 
db.people.createIndex( {name:1, age:-1},{background:true} )
	

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, make the range bounds as tight as possible and use equality matches to limit the number of documents that must be scanned.

Range filters resemble the following

 
 db.people.find({salary:{$gte:15000}})
	

Now, let us discuss the example with all the fields.

 
db.people.find({name:xyz,salary:{$gte:15000}).sort({age:-1})
	

To improve the query performance, we suggest using compound indexes (name, age, salary) in this order.

 
db.people.createIndex( {name:1, age:-1, salary:1},{background:true} )
	

MongoDB ESR Hidden  Rule

For example,

 
find({name: {$in: ["xyz","abc"]}}) and find({$or: [{name: 2}, {name: 3}]}) 
	

are equivalent, and both belong to E of the ESR rule.

But if used with sort, then $in is treated as a range match, i.e. R. For instance

 
find({name: {$in:["xyz","abc"]}}).sort({age: 1})
	

Here, the name is treated as R, so to meet such a query, the index to be created should be (age, name) instead of (name, age).

After reading this article, I hope you got an adequate understanding of indexes and the guidelines for creating them in MongoDB. In the second session of this series, We will see different types of indexes and properties.

Supercharge Your MongoDB Performance! Mydbops offers expert MongoDB database management services to optimize your queries and indexes. We provide comprehensive solutions to empower your MongoDB deployments. Contact us today!

{{cta}}