How to Optimize a Query in MongoDB

Introduction

In this tutorial we’ll show you how to analyze the performance of a MongoDB query and then how to optimize it using indexing. We’ll be using a simple example with a small dataset for simplicity’s sake but the concept is the same with large datasets. In this day and age users will not put up with a laggy website and you need to do everything you can to make your website performant. One of those things is taking a look at all your database queries, figuring out which ones occur frequently, and how you can improve those queries to be most efficient. Please continue reading and we’ll show you an example of a query and how we’d go about optimizing it.

Prerequisites

• You should have MongoDB installed and running.

• If you have it installed, you can run a single instance with the command mongod.

• Although not required we recommend you have some experience using the command line.

• If you’d like to follow along with our example, you’ll need to go through “How to Import a CSV into MongoDB” because we’ll be using the products collection created in that tutorial.

• You should have the products collection in the test database. The products collection should have 10 documents within it. Below was our collection at the end of that tutorial:

Analyzing the Query

We’ll be using the MongoDB shell to execute the query we’ll eventually optimize so let’s open the MongoDB shell:

Now let’s start by first executing the query normally:

We get back two results:

Now let’s run the same command but using the explain method using the verbose flag like so:

This explain command returns to us information about how efficient the query was. Now this is a lot of information so let’s pick one useful piece of data:

This means that the query had to check every document to see if the Quantity was 3. Well that may be ok for this tiny dataset but what happens when this dataset becomes 12 Terabytes, do you still want to scan every document? Let’s see a couple options for improving the performance, namely let’s see if we can get the docsExamined number down.

Limit

If our query doesn’t need ALL the results, for example if you will only show the top ten results, then you can use limit function like so:

Now totalDocsExamined was 1, which means it found a result on the first document and because it was limited to finding one document it returned right away instead of searching the rest of the documents.

Use an Index

Another great option is to use an index on the Quantity field.

You should see a response similar to below:

This creates on index on the Quantity field and the 1 parameter will tell it to sort that index in ascending order.

Now let’s see if our performance improved:

The response will show us that "winningPlan" or the method by which it found the documents was “IXSCAN” which indicates that it used our index instead of checking every document.

On larger datasets you should look at the executionTimeMillis and verify that the query becomes more efficient.

Conclusion

In this tutorial we went through a couple different ways to optimize a query in MongoDB including limiting the query results and creating an index. We introduced the explain() function which will give you vital information on how your query is performing. If you need help improving the efficiency of your database or want us to recommend a more efficient solution please don’t hesitate to reach out to us at Object Rocket.