How to Optimize MongoDB Query Performance with Indexes

Not all slow MongoDB queries are bad queries.

Sometimes the query is fine, but MongoDB does not have a good index to help with your filtering, sorting, and retrieving of the data

In this tutorial, we'll use payments as an example of our database. The collection starts without a useful index for our query. We'll identify the slow operation, learn about the recommended index, explain why the compound index works, and manage it visually in VisuaLeaf.

The workflow is simple:

slow query -> query profiler -> index recommendation -> compound index -> index manager

You can use the same workflow when your own MongoDB collections start to feel slow.

On this page

1. The Slow Query Problem
2. The Payments Query We Want to Optimize
3. Find the Slow Query in VisuaLeaf
4. Read the Index Recommendation
5. Why This Compound Index Works
6. Check and Manage Indexes
7. Indexing Mistakes to Avoid
8. FAQ

The Slow Query Problem

When the size of a database is small, queries executed against it yield fast results.

However, as the database size increases, the same query will take more time. This could be due to the necessity to scan several documents, sort a huge set of returned values, or evaluate non-indexed fields.

This additional processing step causes slow queries.
For instance, consider this query:

db.payments.find({
  status: "paid"
})

If there is no useful index for the "status" field, MongoDB may perform a full scan of the collection.

Not all collection scans are necessarily harmful. It does not matter for small collections. However, on big collections that your application works with each day, this becomes significant.

In case you analyze a query plan, several steps matter:

totalDocsExamined: 50000
Returned: 25

That means MongoDB checked 50,000 documents to return only 25.

The goal is not just to make the query look cleaner. The aim is to reduce the workload of MongoDB.

This technical index functionality is detailed in the official MongoDB Indexes documentation.

The Payments Query We Want to Optimize

Let's try with a more realistic one.

Suppose you have payments stored in your database, and you frequently require paid USD payments above a particular amount, sorted in descending order of payment dates.

The query will be like:

db.payments.find({
  currency: "USD",
  status: "paid",
  amount: { $gte: 100 }
}).sort({
  paidAt: -1
})

This query does four things:

If your payments collection has no useful index for this pattern, so MongoDB has to work harder than necessary.

A single-field index can help with simple filters, but this query uses multiple fields.

That is why a compound index makes more sense here. It can support the filter, sort, and range condition together.

We will let VisuaLeaf recommend the exact index after we inspect the slow query.

MongoDB documents compound indexes in the official Compound Indexes documentation.

Find the Slow Query in VisuaLeaf

Do not create indexes because a field looks important.

Find the slow query first. Then index the query pattern.

Use the same payments query from the previous section in the Visual Query Builder. Add the filters for currency, status, and amount, then sort by paidAt descending.

In VisuaLeaf, the Explain view shows the execution plan, scanned documents, returned documents, execution time, and index usage.

If MongoDB scans many documents and returns only a few, the query probably needs a better index.

Use AI Explain for a Quick Summary

The Explain view provides technical details, but sometimes you may want a quick summary in plain language.

In VisuaLeaf, AI Explain can read the query analysis and explain why the query is slow. In this `payments` example, it detects a collection scan and shows that MongoDB examined 298 documents to return only 15.

It also suggests creating a compound index for the fields used in the query.

This is a helpful first suggestion, but for our full query, we also sort by paidAt, so the final recommendation includes paidAt as part of the compound index.

You can also run the same check manually with explain():

db.payments.find({
  currency: "USD",
  status: "paid",
  amount: { $gte: 100 }
}).sort({
  paidAt: -1
}).explain("executionStats")

When you read the result, check these values:

Look for IXSCAN when MongoDB uses an index, and COLLSCAN when MongoDB scans the collection. For one query, the Explain view is enough.

For repeated slow operations across collections, use VisuaLeaf Query Profiling. The profiler helps you see slow operations over time, not just one query you are testing manually.

MongoDB also provides database profiling features for slow operations. You can read more in the official Database Profiler documentation.

Read the Index Recommendation

After you find the slow query, check which index would actually help.

In this example, the payments collection has no useful index for the query. VisuaLeaf detects repeated collection scans and recommends a compound index based on the fields used by the query.

The recommendation for the compound index is:

db.payments.createIndex({
  currency: 1,
  status: 1,
  paidAt: -1,
  amount: 1
})

This is useful because the recommendation does not only say “add an index.”

It shows the exact fields and the order.

That order is the important part.

Why This Compound Index Works

The recommended index corresponds to how the query filters and organizes the data.
Once you have created the recommended index, re-run the same query from the Explain view.

Now MongoDB uses the compound index instead of scanning the full collection.

The query initially filters based on currency and status fields, then sorts by paidAt, along with applying a range filter on amount.

In other words:

Check and Manage Indexes

Once you’ve indexed something, take a look at what is already there in the collection.
Indexing is simple to do, and it’s easy to neglect.
A collection may accumulate unnecessary indexes, redundant indexes, or even indexes made for queries that no longer run.
Check out your collection in the Index Manager

The Index Manager helps you check the index name, fields, type, size, usage, properties, and status.

This helps you avoid creating the same index twice.

It also helps you review old indexes when your application queries change.

You can combine this workflow with the Visual Query Builder, MongoDB Shell, Aggregation Pipeline Builder, and Charts and Dashboards when you need to test, analyze, and present your MongoDB data.

If you prefer, you can create the index visually from the Index Manager instead of writing the command manually.

Indexing Mistakes to Avoid

Indexes help when they match your workload.

They create problems when you add them without a reason.

Creating indexes for every field

Do not index every field in the collection.

Each index needs storage. Each index also adds work when MongoDB inserts, updates, or deletes documents.

Create indexes for queries your application actually runs.

Ignoring the sort

A query may filter quickly but still sort slowly.

Example:

db.payments.find({
  status: "paid"
}).sort({
  paidAt: -1
})

A useful index is:

db.payments.createIndex({
  status: 1,
  paidAt: -1
})

This supports the filter and the sort.

Putting fields in the wrong order

A compound index is not only about choosing the right fields. The order matters too.

For the payments query, starting with the amount is not as useful as starting with the currency and status, because the amount is a range condition.

Same fields. Different order. Different performance.

Keeping indexes you no longer use

Your application changes.

Your queries change.

Your indexes should change too.

Review unused indexes from time to time and remove the ones that no longer support real queries.

Conclusion

MongoDB query optimization starts with the query pattern.

Do not guess. Find the slow query first. Check whether MongoDB scans too many documents. Then create an index that matches how your query filters, sorts, and ranges over the data.

For simple filters, a single field index may be enough.

For queries like the payments example, a compound index is usually better.

FAQ