MongoDB Performance Optimization: Analyze and Improve Slow Queries

Introduction

MongoDB is designed to handle large amounts of data and provide flexible, scalable database operations. However, as an application grows, some database queries may become slower.

A query that works perfectly with 100 documents may take much longer when the collection contains 1 million documents.

This is where MongoDB Performance Optimization becomes important.

MongoDB provides several tools and techniques to analyze query performance, identify slow operations, create suitable indexes, and improve database response time.

In this tutorial, you will learn how to:

• Understand why MongoDB queries become slow.

• Analyze queries using explain().

• Identify COLLSCAN and IXSCAN.

• Create single-field and compound indexes.

• Optimize sorting, filtering, and projection.

• Monitor slow queries using the Database Profiler.

• Analyze query performance using MongoDB Compass.

By the end, you will be able to identify common query performance problems and apply practical optimization techniques.

---

1. What Is MongoDB Performance Optimization?

MongoDB Performance Optimization is the process of improving database operations so that queries execute efficiently and applications receive data faster.

When MongoDB processes a query, it may need to:

1. Search documents in a collection.

2. Check query conditions.

3. Sort matching documents.

4. Retrieve the required fields.

5. Return the result to the application.

If MongoDB examines too many documents or performs unnecessary operations, the query may become slow.

Example

Suppose your application has a students collection containing 1,00,000 student records.

You execute:

db.students.find({ city: "Raipur" })

If MongoDB does not have a suitable index on the city field, it may need to examine many or all documents to find matching students.

Creating an appropriate index can help MongoDB locate matching records more efficiently.

Important: An index does not guarantee that every query becomes faster. Index selection depends on the query, data distribution, index design, and the amount of data returned.

---

2. Common Reasons for Slow MongoDB Queries

Before optimizing a query, understand what may be causing the performance problem.

Problem	Explanation
Missing Index	MongoDB may scan many documents to find matching records.
Poor Index Design	The existing index may not support the query efficiently.
Large Result Set	Returning too many documents increases processing and network work.
Unnecessary Fields	Retrieving complete documents when only a few fields are required can add overhead.
Inefficient Sorting	Sorting a large result set without suitable index support can be expensive.
Complex Aggregation	Some aggregation stages may require substantial processing or memory.
Unoptimized Application Queries	Repeated or unnecessary database requests can affect overall application performance.

Optimization Tip: Do not start by creating indexes everywhere. First identify the slow query, inspect its execution plan, and then decide what needs improvement.

---

3. Understand MongoDB Indexes

An index is a data structure that helps MongoDB locate documents without examining every document in a collection.

Think of an index like the index section of a textbook. Instead of reading every page to find a topic, you use the index to locate the relevant page.

Without an Index

MongoDB may perform a collection scan to search for matching documents.

With an Index

MongoDB can use an index to locate matching records more efficiently.

Create a Single-Field Index

Suppose you frequently search students by city:

db.students.createIndex({ city: 1 })

Here:

• students is the collection name.

• city is the field being indexed.

• 1 means ascending index order.

For descending order, use -1:

db.students.createIndex({ city: -1 })

Note: Ascending and descending index directions matter particularly when an index supports sorting or when designing compound indexes.

View Existing Indexes

db.students.getIndexes()

This command displays the indexes created on the students collection.

Remove an Index

db.students.dropIndex("city_1")

Use the actual index name returned by getIndexes().

Warning: Do not remove an index from a production database without checking which application queries depend on it.

---

4. What Is explain() in MongoDB?

The explain() method helps developers understand how MongoDB executes a query.

It provides information such as:

• Whether MongoDB uses an index.

• Which query plan MongoDB selects.

• How many documents were examined.

• How many index keys were examined.

• How many documents were returned.

• Execution-time information.

Basic Syntax

db.collection.find({ field: value }).explain()

For detailed execution statistics, use:

db.collection.find({ field: value }).explain("executionStats")

The executionStats mode runs the winning query plan and returns execution statistics.

Example

db.students.find({
    city: "Raipur"
}).explain("executionStats")

Check the output for:

executionStats

Some important fields are:

Field	Meaning
nReturned	Number of documents returned.
totalDocsExamined	Number of documents examined during execution.
totalKeysExamined	Number of index entries examined.
executionTimeMillis	Execution-time measurement reported by explain.

Important: Explain output and execution stages can vary between MongoDB versions and execution engines. Do not compare execution time from a single test as the only measure of performance.

---

5. Difference Between COLLSCAN and IXSCAN

Two important execution stages to understand are COLLSCAN and IXSCAN.

A. COLLSCAN – Collection Scan

COLLSCAN means MongoDB scans documents in the collection to find matching records.

Example:

db.students.find({
    city: "Raipur"
}).explain("executionStats")

If the query plan contains:

{
    stage: "COLLSCAN"
}

MongoDB is using a collection scan for that part of the query plan.

A collection scan is not automatically wrong. It can be reasonable for small collections or queries that return a large proportion of the collection.

However, for selective queries on large collections, it may indicate that a suitable index could help.

B. IXSCAN – Index Scan

IXSCAN means MongoDB is scanning an index to locate relevant records.

Create an index:

db.students.createIndex({
    city: 1
})

Run the query again:

db.students.find({
    city: "Raipur"
}).explain("executionStats")

Now inspect the winning plan for an IXSCAN stage.

COLLSCAN vs IXSCAN

Feature	COLLSCAN	IXSCAN
Meaning	Collection scan	Index scan
Data examined	May examine many documents	Examines index entries
Index usage	No index used for that scan stage	Uses an index
Best suited for	Small collections or broad queries in some cases	Queries supported efficiently by an index

Remember: The goal is not simply to eliminate every COLLSCAN. The goal is to choose an efficient plan for the actual workload.

---

6. Understand executionStats with a Practical Example

Let's create sample student records.

db.students.insertMany([
    {
        name: "Rahul",
        city: "Raipur",
        course: "MongoDB"
    },
    {
        name: "Amit",
        city: "Delhi",
        course: "Python"
    },
    {
        name: "Priya",
        city: "Raipur",
        course: "Web Development"
    },
    {
        name: "Neha",
        city: "Mumbai",
        course: "MongoDB"
    }
])

Now execute:

db.students.find({
    city: "Raipur"
}).explain("executionStats")

Suppose the output contains:

executionStats: {
    nReturned: 2,
    totalKeysExamined: 0,
    totalDocsExamined: 4
}

This illustrative result tells us:

• nReturned: 2 means two documents matched.

• totalKeysExamined: 0 means no index entries were examined.

• totalDocsExamined: 4 means four documents were examined.

Now create an index:

db.students.createIndex({
    city: 1
})

Run the explain command again:

db.students.find({
    city: "Raipur"
}).explain("executionStats")

Compare the new execution statistics with the earlier result.

Learning Point: An index can reduce the number of documents MongoDB needs to examine. The exact statistics depend on the dataset and selected query plan.

---

7. Create Compound Indexes

A Compound Index contains multiple fields in a single index.

It is useful when your application frequently filters or sorts using more than one field.

Example

Suppose your application searches students by both city and course:

db.students.find({
    city: "Raipur",
    course: "MongoDB"
})

You can create a compound index:

db.students.createIndex({
    city: 1,
    course: 1
})

This index stores both fields in the specified order.

Why Does Field Order Matter?

Consider these indexes:

db.students.createIndex({
    city: 1,
    course: 1
})

And:

db.students.createIndex({
    course: 1,
    city: 1
})

They are not interchangeable for every query.

MongoDB compound indexes support queries on the index's fields and their prefixes. Therefore, choose field order according to your application's actual query patterns.

Best Practice: Review the Equality, Sort, Range (ESR) guideline when designing compound indexes.

---

8. Optimize Queries Using Projection

Projection allows you to return only the fields your application needs.

Suppose a student document contains:

{
    name: "Rahul",
    city: "Raipur",
    course: "MongoDB",
    email: "rahul@example.com",
    phone: "9876543210",
    address: "Raipur, Chhattisgarh"
}

If your application only needs the student's name and course, do not retrieve every field unnecessarily.

Without Projection

db.students.find({
    city: "Raipur"
})

With Projection

db.students.find(
    { city: "Raipur" },
    { name: 1, course: 1, _id: 0 }
)

Here:

• name: 1 includes the name field.

• course: 1 includes the course field.

• _id: 0 excludes the default _id field.

Benefit: Projection reduces the amount of data returned to the application. It can also help a query become covered by an index when the index contains all fields needed for filtering and output.

---

9. Optimize Sorting with Indexes

Sorting a large number of documents can require additional processing.

Suppose you want to display students in alphabetical order:

db.students.find().sort({
    name: 1
})

Create an index:

db.students.createIndex({
    name: 1
})

Then inspect the query plan:

db.students.find().sort({
    name: 1
}).explain("executionStats")

An appropriate index may allow MongoDB to return results in the requested order without a separate in-memory sort.

Important: Whether an index supports a sort depends on the index field order, sort direction, query conditions, and other details.

---

10. Use limit() to Reduce Unnecessary Results

If an application only needs the first 10 records, avoid requesting every document.

Without limit()

db.students.find({
    city: "Raipur"
})

With limit()

db.students.find({
    city: "Raipur"
}).limit(10)

The limit() method restricts the maximum number of documents returned by the cursor.

For consistent pagination, use a stable sort and consider range-based pagination for large datasets.

Example:

db.students.find({
    city: "Raipur"
})
.sort({
    _id: 1
})
.limit(10)

Tip: limit() reduces the returned result set, but it does not automatically make every query efficient. Check the execution plan to understand how much work MongoDB performs.

---

11. Monitor Slow Queries Using Database Profiler

MongoDB provides a Database Profiler that can collect information about database operations.

It can help identify slow queries and other operations.

Check Profiling Status

db.getProfilingStatus()

Enable Profiling for Slow Operations

The following example enables profiling for operations slower than 100 milliseconds:

db.setProfilingLevel(1, {
    slowms: 100
})

Here:

• 1 enables profiling for slow operations.

• slowms: 100 sets the slow-operation threshold to 100 milliseconds.

View Profiling Data

db.system.profile.find().sort({
    ts: -1
}).limit(5)

This displays recent profiling records, if available.

Disable Profiling

db.setProfilingLevel(0)

Production Warning: Profiling can add overhead and may expose query information in profiling records or logs. Use it carefully, especially on production databases. Profiling settings may also revert after a server restart unless configured through persistent server settings.

---

12. Analyze Query Performance Using MongoDB Compass

MongoDB Compass provides a graphical interface for exploring collections, running queries, and inspecting query plans.

You can use Compass to understand query performance without reading every part of a raw explain output.

Steps

Step 1: Open MongoDB Compass.

Step 2: Connect to your MongoDB database.

Step 3: Select the database and collection.

Step 4: Enter your filter in the query bar.

Example:

{
    city: "Raipur"
}

Step 5: Click the Explain option.

Step 6: Review the Explain Plan.

Look for:

• Documents Returned

• Documents Examined

• Index Keys Examined

• Query execution stages

• Indexes used

Compass also provides a visual query-plan representation and raw output.

Learning Point: Use Compass to inspect the plan, then use explain("executionStats") in mongosh when you want to reproduce and investigate the query from the command line.

---

13. Best Practices for MongoDB Performance Optimization

Follow these practices when building real-world MongoDB applications.

1. Create indexes for frequently queried fields. Analyze actual query patterns before creating indexes.

2. Use compound indexes carefully. Field order matters, so consider equality filters, sorting, and range conditions.

3. Avoid unnecessary fields. Use projection to return only the data required by your application.

4. Analyze slow queries. Use explain("executionStats") to inspect query plans and execution statistics.

5. Review COLLSCAN operations. Investigate collection scans on large collections when the query is selective.

6. Use limit() when appropriate. Avoid returning more documents than the application needs.

7. Monitor slow operations. Use database profiling and diagnostic logs carefully.

8. Avoid excessive indexes. Every additional index consumes storage and can increase the work required for inserts, updates, and deletes.

9. Test with realistic data. A query that performs well on a small test collection may behave differently with production-scale data.

10. Measure before and after optimization. Compare execution statistics and application-level response times.

---

14. Common MongoDB Performance Optimization Mistakes

Mistake	Better Approach
Creating indexes on every field	Index fields based on real query patterns.
Ignoring explain output	Inspect the winning plan and execution statistics.
Returning complete documents unnecessarily	Use projection where suitable.
Creating compound indexes without planning	Consider field order and query patterns.
Ignoring slow operations	Monitor logs and profiler data carefully.
Assuming IXSCAN always means a fast query	Compare keys examined, documents examined, and returned results.
Keeping unused indexes	Review index usage before removing indexes.

---

15. Practical Exercises

Try these exercises in MongoDB Shell or MongoDB Compass.

Exercise 1: Create a students collection and insert at least 10 documents.

Exercise 2: Search students using the city field.

Exercise 3: Run explain("executionStats") and inspect the query plan.

Exercise 4: Create an index on the city field.

Exercise 5: Run the same query again and compare the execution statistics.

Exercise 6: Create a compound index using city and course.

Exercise 7: Use projection to return only name and course.

Exercise 8: Sort students by name and inspect the explain plan.

Exercise 9: Use limit(5) to return only five documents.

Exercise 10: Explore MongoDB Compass Explain Plan and identify whether your query uses COLLSCAN or IXSCAN.

---

16. Frequently Asked Questions (FAQs)

Q1. What is MongoDB Performance Optimization?

MongoDB Performance Optimization is the process of improving query execution, index usage, and database operations to make applications more efficient.

Q2. What is explain() in MongoDB?

The explain() method displays information about how MongoDB executes a query, including its query plan and execution statistics.

Q3. What is the difference between COLLSCAN and IXSCAN?

COLLSCAN represents a collection scan, while IXSCAN represents an index scan. The best execution plan depends on the query and the data.

Q4. How do I find slow queries in MongoDB?

You can investigate slow operations using MongoDB diagnostic logs and the Database Profiler. You can also use explain("executionStats") to analyze individual queries.

Q5. Does creating more indexes always improve performance?

No. Indexes can improve supported read queries, but they also consume storage and add maintenance work to write operations.

Q6. What is a compound index in MongoDB?

A compound index is an index created on multiple fields. It can support queries that use those fields, depending on field order and query conditions.

Q7. What is executionStats in MongoDB?

executionStats is an explain verbosity mode that provides detailed statistics about the winning query plan, including documents returned, documents examined, and index keys examined.

Q8. Can MongoDB Compass analyze query performance?

Yes. MongoDB Compass provides an Explain Plan view that displays query execution stages and performance-related statistics.

---

Conclusion

MongoDB Performance Optimization is an important skill for every MongoDB developer.

As your database grows, queries that were once fast may become slower. By understanding indexes, analyzing execution plans, monitoring slow operations, and optimizing filters, sorting, and returned fields, you can improve the efficiency of your MongoDB applications.

In this tutorial, you learned how to use explain(), understand COLLSCAN and IXSCAN, create single-field and compound indexes, optimize queries using projection and limit, and investigate slow operations.

Keep practicing these techniques with realistic datasets. Always measure query performance before and after making changes.

Practice. Analyze. Optimize. Build Better MongoDB Applications!

Param Classes – Learn Programming with Practical Examples.