MongoDB Query Slow? Find COLLSCAN and Add the Right Index

A MongoDB or Mongoose query that got slow as data grew is usually a full collection scan. Find the slow query, read explain(), and add a compound index that fixes it.

A MongoDB query that was fast with 1,000 documents and is slow with a million is almost always doing a collection scan (COLLSCAN). Find the slow query in the profiler or Atlas, run explain("executionStats"), and add a compound index in the order equality, sort, range. Then confirm the plan shows IXSCAN and that documents examined are close to documents returned.

This guide uses the Node.js driver and Mongoose, but the shell commands and the index rules are the same for every language.

Step 1: Find the slow query first

Do not guess which query to fix. Let MongoDB tell you.

On Atlas: open the Query Profiler. It plots slow operations so outliers stand out, and the Performance Advisor suggests indexes for slow queries. MongoDB notes that the Query Profiler and the database profiler can add load and use disk, so treat them as a diagnosis tool, not something to leave at full detail forever (MongoDB docs).

Self-hosted or Docker: turn on the database profiler for operations slower than 100 ms:

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

// later: the 5 slowest recent operations
db.system.profile.find().sort({ millis: -1 }).limit(5).pretty()

Each entry shows the namespace, the filter and the plan summary. Entries with planSummary: "COLLSCAN" are your first targets. The default slow threshold is also 100 ms, which is what shows up in the MongoDB log.

Write down the exact filter and sort of the slowest one. You need them in the next step.

Step 2: Run explain on that query

In mongosh, use the executionStats mode:

db.orders
  .find({ userId: "u_123", status: "paid" })
  .sort({ createdAt: -1 })
  .limit(20)
  .explain("executionStats")

Read three things in the output:

Field What it tells you
winningPlan stage names COLLSCAN means a full scan. IXSCAN means an index is used.
totalDocsExamined vs nReturned A big gap means MongoDB read many documents to return a few.
executionTimeMillis The real time on your data.

MongoDB's docs state that a COLLSCAN stage on a filter or sort means the query would benefit from an index, and that a totalDocsExamined far above nReturned points to an ineffective index (source).

Also look for a SORT stage in the plan. It means MongoDB is sorting in memory after reading documents, which is slow and has a memory limit.

Step 3: Add a compound index in the right order

Field order in a compound index decides whether it helps. The MongoDB docs describe the ESR guideline: Equality fields first, then Sort fields, then Range fields (ESR rule).

For the query above, userId and status are equality filters, and createdAt is the sort:

db.orders.createIndex({ userId: 1, status: 1, createdAt: -1 })

Why this order works: MongoDB jumps straight to one user's paid orders and reads them already sorted. No scan, no in-memory sort.

Two things from the docs catch people out:

  • A range filter placed before the sort field forces an in-memory sort, because MongoDB cannot index-sort the results of a range filter.
  • $ne, $nin and $regex count as range operators, not equality.

In Mongoose, declare it on the schema so every environment gets it:

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

Step 4: Build the index without hurting production

Index builds on a large collection use CPU, memory and disk. Do it in a quiet period, on a small team's database that means late evening for your users. On Atlas, watch cluster CPU and memory while it builds. Do not create five indexes at once.

Also remember Mongoose autoIndex. By default Mongoose tries to build schema indexes when the app starts. On a big collection that can slow every deploy and every serverless cold start. Many teams set it off in production and create indexes by a script or migration:

mongoose.connect(uri, { autoIndex: process.env.NODE_ENV !== "production" })

Each index also costs write speed and storage. Add one per real query pattern, not one per field.

Step 5: Run explain again and confirm

Repeat the explain from Step 2. You want:

  • IXSCAN in the winning plan, with no COLLSCAN and no SORT stage.
  • totalDocsExamined close to nReturned.
  • A much lower executionTimeMillis.

If COLLSCAN remains, check that your filter fields match the index prefix exactly, including the same types. A string "123" will not match a number 123.

Step 6: Check the patterns that ignore indexes

Even with indexes, some queries stay slow:

  • Unanchored $regex such as /foo/i cannot use an index well and scans widely. Use a text index or Atlas Search for real search.
  • Deep skip (skip(100000)) still walks past every skipped document. Page with a range on an indexed field instead, such as createdAt < lastSeen.
  • Fetching whole documents you only partly use. Add a projection, and use .lean() in Mongoose for read-only queries to skip building full model objects. This reduces overhead, not scan cost.
  • Unbounded results. Always add .limit() to list endpoints.

The same growth pattern exists in SQL databases. If you also run Postgres, see Postgres query slow in production but fast locally. For the user-facing side, API endpoint getting slower as data grows shows how to find the slow call from the request level.

Step 7: Catch the next one before users do

A missing index does not fail loudly. The endpoint just gets slower each month until a customer complains or requests time out. Keep a short routine:

  1. Review the Atlas Performance Advisor or the profiler once a week.
  2. Alert on a rising p95 latency for your main endpoints, not only on errors.
  3. Check explain on any new query that filters a large collection before you ship it.

How Relia Helps

Relia watches your APIs and your database together. When an endpoint slows down, it joins the slow query, the affected requests and the users who waited into one incident explained in plain words. After you add an index, it checks whether the endpoint recovered. It also offers uptime monitors and reliability targets (SLOs) it proposes from your real traffic, so a slow creep shows up before it turns into an outage. Setup for Node.js apps is npx relia-sdk@latest init. Try the live demo.

FAQ

How do I know if my MongoDB query is using an index?

Run .explain("executionStats") and read the winning plan. IXSCAN means an index is used. COLLSCAN means a full scan.

How slow is "slow" in MongoDB?

The profiler and log default to 100 ms for slow operations. Use your own endpoint targets to decide what matters to users.

Should I index every field I filter on?

No. Each index slows writes and uses memory. Build compound indexes for the query shapes you actually run.

Why is my query still slow after adding an index?

Common reasons: the field order breaks the ESR guideline, a regex or deep skip prevents index use, or the index does not match the filter types. Re-run explain to see which.

Does a connection problem look like a slow query?

Sometimes. If errors are timeouts at connect time, not slow operations, read MongoServerSelectionError on Vercel.

All guides