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/
