How to Evaluate Query Performance on MongoDB
Tags: mongodb, queryevaluation
Published: 2018-04-12 09:53:18

---
title: How to Evaluate Query Performance on MongoDB
published: true
description: Evaluating query performance using explain("executionStats")
tags: mongodb, query-evaluation
---

## Introduction
Hi, my name Hafidz. I am just regular software developer like almost of you. Currently, I have enrolled [MongoDB for Developers](https://university.mongodb.com/courses/M101P/about) course from MongoDB university. This course is great and has awesome instructors. I like to share what I have already learn from this course. It is about how we evaluate query performance on MongoDB using `explain("executionStats")`.


## Motivation
Evaluating query performance 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 explain("executionStats")
You can find detailed explanation of `explain("executionStats")` output from [this documentation](https://docs.mongodb.com/manual/reference/explain-results/#explain.executionStats). To simplify, we only need to focused on some output keys:

* **nReturned**: Number of documents returned by query operation.
* **executionTimeMillis**: Total time in milliseconds needed to complete the operation.
* **totalKeysExamined**: Returns 0 (zero) if no indexes found in the operations, else returns number of scanned index keys in order to complete the operation. 
* **totalDocsExamined**: Total number of examined documents in order to complete the operation.


## The Database and Collection
In this easy tutorial, we used a **school** database and **students** collection contains 1,000,000 documents. All the documents randomly created from javascript file which you can downloaded from [here](https://github.com/Bladefidz/mongodb-analytics/blob/master/mongodb%20for%20developers/chapter_4_performance/create_scores2.js).
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).


## Suggestion
Try to run this explain query:
```
db.students.explain('executionStats').find({'scores.score': {$gte: 90}, 'scores.type': 'exam'})
```
This query differs from the explain query which used in the evaluation because we flip the find parameters. Maybe you will convinced yourself that this query should be run faster than the query we used in the evaluation tutorial. But in fact, its not. Try to look `winningPlan` key on the explain result, you will find out why.

I have created a [simple javascript code which measures means of "executionTimeMillis"](https://github.com/Bladefidz/mongodb-analytics/blob/master/mongodb%20for%20developers/chapter_4_performance/scores2_evaluation.js). Just play around with that code to do statistical analysis, such as measure the standard deviation.