Using explain() for Query Optimization in MongoDB
MongoDB provides the explain() method to help developers analyze how queries are executed internally. This is essential when optimizing slow queries or verifying whether indexes are being used effectively.
What is explain()?
explain() shows the execution plan of a query — how MongoDB selects documents, whether it uses indexes or performs a full collection scan, and how many documents are scanned or returned.
It helps you answer key questions like:
- Is this query using an index?
- How many documents is MongoDB scanning?
- Is the index being used efficiently?
How to Use explain()
You can append .explain() to any query or cursor. MongoDB will then return metadata about the query's execution plan.
db.collection.find({ field: value }).explain()
Step-by-Step Example
1. Create a Sample Collection
db.users.insertMany([
{ name: "Alice", age: 30 },
{ name: "Bob", age: 25 },
{ name: "Charlie", age: 35 },
{ name: "David", age: 25 },
{ name: "Eve", age: 40 }
]);
2. Run a Query Without Index
Now query for users aged 25 without any index:
db.users.find({ age: 25 }).explain("executionStats")
Output:
{
...
"executionStats": {
"totalDocsExamined": 5,
"totalKeysExamined": 0,
"executionStages": {
"stage": "COLLSCAN",
...
}
}
...
}
Explanation: The output shows stage: "COLLSCAN" which means the query did a collection scan — it examined all 5 documents to find matches. This is inefficient for large datasets.
Intuition Check
Q: What does it mean if "stage": "COLLSCAN" appears in your explain output?
A: MongoDB is scanning the entire collection because it doesn’t have an index on the field you're querying.
3. Create an Index
db.users.createIndex({ age: 1 })
Output:
{
"createdCollectionAutomatically": false,
"numIndexesBefore": 1,
"numIndexesAfter": 2,
"ok": 1
}
4. Run the Same Query Again
db.users.find({ age: 25 }).explain("executionStats")
Output:
{
...
"executionStats": {
"totalDocsExamined": 2,
"totalKeysExamined": 2,
"executionStages": {
"stage": "IXSCAN",
...
}
}
...
}
Explanation: Now the query uses stage: "IXSCAN" — index scan. MongoDB only examined the 2 documents matching age = 25. Much faster and efficient!
5. Understanding explain() Output
Here are a few key fields to look at in explain("executionStats") output:
- totalDocsExamined: Total documents scanned
- totalKeysExamined: Index entries scanned
- stage: COLLSCAN or IXSCAN
- nReturned: Documents returned by the query
Intuition Check
Q: Should totalDocsExamined always equal nReturned?
A: No. If there’s a filter, MongoDB may examine more documents than it returns.
6. Forcing Index Use (Optional)
If you want MongoDB to use a specific index or test index behavior, you can use .hint():
db.users.find({ age: 25 }).hint({ age: 1 }).explain("executionStats")
Summary
The explain() method is your window into MongoDB's query planner. It helps you:
- Identify slow queries
- Determine if and how indexes are used
- Compare different query strategies
Using explain("executionStats") is an essential habit when optimizing MongoDB queries for performance, especially at scale.
Sign in with Google