Anas Sheikh

Posted on Nov 5

Your MongoDB Query Is Slow and You Are Guessing Why: Read explain() Instead

#database #mongodb #javascript #webdev

The endpoint was fast in development. In production it takes two seconds, and it gets slower every week.

So you do what most of us do. You add .lean(). You add an index on a field that sounds important. You wonder if the server is too small. Maybe the slowdown goes away, maybe it does not, and you never learn why.

MongoDB will tell you exactly why, if you ask. The tool is called explain(), and most developers never open it.

The Query

An orders list, the kind every app has:

const orders = await Order.find({ userId, status: 'paid' })
  .sort({ createdAt: -1 })
  .limit(20);

Twenty results, newest first. Looks harmless. Now ask the database what it did:

const plan = await Order.find({ userId, status: 'paid' })
  .sort({ createdAt: -1 })
  .limit(20)
  .explain('executionStats');

console.log(JSON.stringify(plan, null, 2));

The output is long. You only need a handful of fields.

The Four Fields That Matter

Look inside executionStats and queryPlanner.winningPlan:

Field	What it tells you
`nReturned`	How many documents the query gave back
`totalDocsExamined`	How many documents MongoDB had to read
`totalKeysExamined`	How many index entries it looked at
`winningPlan` stage	`COLLSCAN` (read everything) or `IXSCAN` (used an index)

The one to remember is the ratio between totalDocsExamined and nReturned.

If MongoDB examined 500,000 documents to return 20, the query is doing 25,000 times the work it needs to. That is your slowness. No amount of server size fixes a ratio like that, it only delays the day it hurts.

A healthy query examines roughly as many documents as it returns. If the numbers are close to equal, the query is not your problem.

What a Bad Plan Looks Like

Without a suitable index you will see something like this in the plan:

A COLLSCAN stage, meaning MongoDB read the whole collection.
A SORT stage, meaning it then sorted the matches in memory.

Both are fine on a small collection, which is why this passes every test on your laptop. Both get worse as the collection grows, and they grow together: more documents to read, and more of them to sort.

Add an Index, but the Right One

The tempting fix is an index on each field you filter by:

orderSchema.index({ userId: 1 });
orderSchema.index({ status: 1 });
orderSchema.index({ createdAt: -1 });

This helps less than you would hope. For this query MongoDB will usually pick one of those indexes and then filter or sort the rest by hand. Three separate indexes are not the same as one index built for the query.

The better version is a compound index, one index over several fields in a specific order:

orderSchema.index({ userId: 1, status: 1, createdAt: -1 });

Now the database can jump straight to this user's paid orders, already in newest-first order, and stop after 20. Run explain('executionStats') again and you should see an IXSCAN, no SORT stage, and totalDocsExamined close to 20.

Why the Order of Fields Matters

A compound index is read left to right, like a phone book sorted by last name and then first name. You can look up "Smith" quickly. You cannot look up everyone named "John" quickly, because Johns are scattered across the whole book.

A useful rule for ordering the fields is Equality, Sort, Range:

Equality fields first, the ones you match with an exact value (userId, status).
Sort fields next, the ones in your .sort() (createdAt).
Range fields last, the ones you filter with $gt, $gte or $lt.

Put a range field before the sort field and MongoDB can no longer return results in sorted order straight from the index. It brings the sort stage back.

This is a guideline, not a law. Your data and queries decide. Which is why the next step matters more than the rule.

Always Check Your Work

After adding an index, run explain() again and compare. You are looking for:

COLLSCAN replaced by IXSCAN
No in-memory SORT stage when you sort
totalDocsExamined close to nReturned

An index you never verified is a guess with a storage cost.

Three Traps

More indexes are not free. Every index takes disk space and makes every write slightly slower, because MongoDB updates the index too. Add the indexes your real queries need, not one for every field you can think of. Check for unused indexes occasionally and remove them.

Offset pagination hides the problem. .skip(50000).limit(20) still walks past 50,000 entries before returning anything. If your slow endpoint is a deep page of results, an index alone will not save you. I wrote about the fix, cursor pagination, in a separate post.

Building an index on a big production collection needs planning. Mongoose builds the indexes in your schema on startup by default (autoIndex). On a small database that is convenient. On a large live collection, building an index takes time and resources, so many teams turn automatic building off in production and create indexes deliberately. Check how your own deployment handles it before you add one to a schema and deploy.

A Five-Minute Audit

Pick the three endpoints your users hit most. For each:

Copy the exact query, including the sort and the limit.
Run it with .explain('executionStats') against data of realistic size, not your ten-row dev database.
Compare totalDocsExamined with nReturned.
If the gap is large, look at the plan stage and design one compound index for that query.
Run explain() again to confirm.

Realistic data size is the part people skip. A query plan on 50 documents tells you nothing about a million.

The Rule

Do not guess why a query is slow. Ask the database. Read explain('executionStats'), compare documents examined with documents returned, and build the index for the query you actually run, not for the fields you happen to have.

I build SaaS dashboards and client apps with Next.js and MongoDB, and query plans are one of the first things I check before a release. If you want to see the kind of work I do, or a typed starting point for your own project, you can browse my templates on pixelanas.com.

Get the templates: https://pixelanas.gumroad.com

What is the slowest query in your app right now, and what did explain() say about it? Tell me in the comments.

Anas, full-stack Next.js developer building SaaS products and premium templates. X: @ASheikh69751

DEV Community