Improving MongoDB Performance | Indexes and Explain Plan in MongoDB Compass
Discover more on YouTube: If you’re looking for video content on technology, programming, and other topics, check out my YouTube channel! Subscribe to stay updated and learn more about these subjects.
In this article, we will delve into the art of optimizing MongoDB queries through the strategic use of indexes and analyzing their results using the Explain Plain feature in MongoDB Compass. The article is structured as follows:
- What are MongoDB Indexes?
- Preparing the Scenario — Data Import and Setup
- MongoDB Compass — Explain Plan
- Conclusion
- References
What are MongoDB Indexes?
One of the easiest ways to improve the performance of a slow query is to create indexes on the data that is used most often. Without indexes, MongoDB must scan each and every document in a collection to retrieve query results, which can lead to performance problems in certain situations.
There are various types of indexes available in MongoDB, but for the purpose of this discussion, we will concentrate on two fundamental categories:
1 — Single Fied Indexes
- An index that supports efficient querying against one field
- MongoDB creates a single field index on the _id field by default
2 — Compound Indexes
- Index on multiple fields
- The recommended order of indexed fields in a compound index is Equality, Sort, and Range.
For additional details, please visit the official page at:
Preparing the Scenario — Data Import and Setup
To demonstrate our scenario, we’ll use a football results dataset and import it into our database. I’ve opted to import it into my Atlas Cluster, but you’re welcome to use your own if you prefer.
Section 1: Download the dataset
Kaggle.com is an online platform that hosts data science competitions and offers datasets for exploration. Download the football Json:
Section 2: Importing dataset to MongoDB Atlas
After downloading the file, proceed to import it into your database. In my case, I’m using MongoDB Tools to import the dataset into my Atlas cluster.
As you can see, I’ve successfully imported the dataset into a database named “football_db,” stored within a collection named “results.”:
For more detailed instructions on using MongoDB tools and importing data into Atlas, you can refer to my other article available here.
MongoDB Compass — Explain Plan
To illustrate the Explain plan and the creation of indexes, I’ve divided this section into four parts:
- Establishing a Compass Connection
- Understanding the Explain Plan
- Optimizing through Index Creation
- Examining Explain Plan Results
Establishing a Compass Connection
Compass is a free interactive tool designed for querying, optimizing, and analyzing your MongoDB data. Now, it’s time to establish a connection to our Atlas Cluster using this tool:
Once connected, we can verify the existence of our created database/collection and total Documents:
Excellent, as we can observe, our database has been successfully imported, containing 45,100 documents in the “results” collection. This collection encompasses various types of games, including friendly matches, tournaments, and more.
Understanding the Explain Plan
Let’s say we want to filter only the World Cup matches in which the country is Qatar to discover all the matches from the last World Cup.
[
{
$match:
{
tournament: "FIFA World Cup",
country: "Qatar",
},
},
]To do this, we’ll use the Aggregation operations with the $match stage to filter tournament and country fields:
Now, let’s analyze the results of the Explain query. To do this, click the “Explain” button as shown in the image above:
Here are some important points to consider:
- The “COLLSCAN” stage indicates that a collection scan is performed, meaning no indexes are utilized.
- The query had to examine all 45,100 documents.
- The query took 35 milliseconds to execute.
Optimizing through Index Creation
3.1 — Single Field Index
Initially, we will create a Single Filed index for the “tournament” field. When you create an index, you specify:
- The field on which to create the index.
- The sort order for the indexed values (ascending
1or descending-1).
db.<collection>.createIndex( { <field>: <sortOrder> } )Open MONGOSH and run the command:
Now, let’s revisit the “Explain” stage and examine the results.
Here are some important points to consider:
- The “FETCH” stage indicates documents are being read from the collection.
- The “IXSCAN” stage indicates the query is using an index and what index is being selected.
- The query took 3 milliseconds to execute.
- The query had to examine 964 Documents.
3.2— Compound Index
Our query’s performance is improving, but it’s not perfect yet. To further enhance it, we need to include one more field in our index. Rather than creating another Single Field index, we will create a compound index with both “tournament” and “country” fields.
Before proceeding, let’s first remove the recently created index and then create the compound index. To do this, start by checking the existing indexes:
db.results.getIndexes()And then proceed with dropping them.
db.results.dropIndex('tournament_1')
Now, let’s proceed to create a new compound index:
Examining Explain Plan Results
Now, let’s revisit the “Explain” stage and examine the results.
As we can see, our query is now returning exactly 64 games, and the execution time has been reduced to 1 ms. For a more detailed analysis, you can execute the explain() function in MONGOSH.
db.results.explain().find({
tournament: 'FIFA World Cup',
country: 'Qatar'
})
Conclusion
In summary, this article practically demonstrated what indexes are and how to use them in a scenario with a large number of records. Optimization was achieved by using Single Field and Compound indexes.
Do you like MongoDB? Check out my other articles:
I hope this has been informative and helpful. Until next time! 👋📊
