How to Analyze query performance in MongoDB

Reading Time: 2 minutes

Analyze query performance in mongodb may became complicated if we do not really know which part should be measured. Fortunately, MongoDB provides very handy tool which can be used to evaluate query performance: explain("executionStats"). This tool provide us some general measurements such as number of examined document and execution time that can be used to do statistical analysis.

The Database and Collection

In this easy tutorial, we used a school database and students collection contains 1,000 documents.
Below the description of each key in our document:

  • student_id: Unique identification of each student.
  • scores: An array of contains two keys: type (type of score) and score (float value).
  • class_id: Unique identification of each class where student belongs to.

Evaluation

In this section, we want to know how indexing can improve query performances. In order to simplify the case, we focused only on how to optimizing “Read operation”.

Initial evaluation

We need to finds any students that have exam score greater than 90. So the query is:

 db.students.find({'scores.type': 'exam', 'scores.score': {$gte: 90}})

First evaluation: Without indexing.

Run this commands in the shell:

var exp = db.students.explain('executionStats');
exp.find({'scores.type': 'exam', 'scores.score': {$gte: 90}})

we got

...
"executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 343310,
                "executionTimeMillis" : 2843,
                "totalKeysExamined" : 0,
                "totalDocsExamined" : 1000000,
...

Second evaluation: With indexing.

Run this commands in the shell:

db.students.createIndex({'scores.score': -1, 'scores.type': 1})
var exp = db.students.explain('executionStats');
exp.find({'scores.type': 'exam', 'scores.score': {$gte: 90}})

We got:

...
"executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 343310,
                "executionTimeMillis" : 2284,
                "totalKeysExamined" : 399171,
                "totalDocsExamined" : 343310,
...

Conclusion

From the two evaluations results, we knew that the operation to find any students that have exam score greater than 90 will run faster by using compound index. In this case, collection with indexes {'scores.score': -1, 'scores.type': 1} performs faster than without indexes (2284 ms vs 2843 ms). So we can analyze query performance in mongodb using  explain("executionStats").

Reference

https://docs.mongodb.com/manual/tutorial/analyze-query-plan/

Written by 

Munander is a Software Consultant in Knoldus Software LLP. He has done b.tech from IMS Engineering college, Ghaziabad. He has decent knowledge of C,C++,Java,Angular and Lagom. He always tries to explore new technologies. His hobbies include playing cricket and adventure.

Leave a Reply