Make MongoDB Queries Fast by Matching the Plan

A slow MongoDB query is usually a shape problem. The filter, the sort, and the fields you return do not line up with an index, so the server scans documents it will throw away. Adding RAM, or adding an index on every field, does not fix that.

The order below is the one I use on a Node and Mongoose service. Measure the plan, change the query or one compound index, then measure again.

Read the plan before you add an index

Run the query with execution stats, not the default explain. The default only shows the winning plan. Stats show how much work that plan actually did.

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

Look at the winning plan's executionStats:

• nReturned is the number of documents sent back.
• totalDocsExamined is the number of documents the server opened.
• totalKeysExamined is the number of index keys it walked.
• executionTimeMillis is the server time for that one run.

A healthy indexed query examines roughly as many documents as it returns. If totalDocsExamined is tens or hundreds of times nReturned, the predicate is not selective, or the index does not cover the filter. A COLLSCAN stage means there was no usable index at all.

Also check rejectedPlans. If MongoDB considered an index and rejected it, the query shape is probably incompatible with that index: a range in the wrong position, a sort in the opposite direction, or a predicate that transforms the field.

For production, turn the profiler on for slow operations only, and sample it. Logging every query will itself become load.

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

Profiles land in system.profile. Sort by millis and group by ns plus the query shape, not by a single slow sample. One 2-second query during a deploy is noise. The same shape at 50 ms, 200 times a second, is the one to fix.

Build one compound index for the whole query

MongoDB can use a compound index only from the left. An index on { userId: 1, status: 1, createdAt: -1 } supports:

• userId
• userId + status
• userId + status + sort on createdAt

It does not support a query that filters only on status, or only sorts by createdAt. A second single-field index on status is not a substitute. The planner can intersect two indexes, and that plan is usually worse than one compound index that matches the query.

Follow the equality, sort, range order:

1. Equality predicates first (userId, status: "paid").
2. The sort field next (createdAt).
3. Range predicates last (total: { $gte: 100 }).

// query
Order.find({
  userId,
  status: "paid",
  total: { $gte: 100 },
}).sort({ createdAt: -1 });

// index
{ userId: 1, status: 1, createdAt: -1, total: 1 }

Put the range before the sort and the index can filter, but it can no longer provide createdAt order. MongoDB then sorts in memory. Past the in-memory limit (100 MB by default on current server versions), a find sort fails, and an aggregation sort spills to disk only if you set allowDiskUse. An index that already returns rows in order avoids both.

Sort direction has to be consistent along the index. { createdAt: -1, _id: -1 } serves sort({ createdAt: -1, _id: -1 }). Mixing directions without a matching index falls back to an in-memory sort.

Return only the fields you use

A projection does two jobs. It cuts bytes on the wire, and it can turn the query into a covered query, where every field in the filter, sort, and result is in the index. The server never opens the document.

await Order.find({ userId, status: "paid" })
  .select("createdAt total -_id")
  .sort({ createdAt: -1 })
  .lean();

_id is included unless you remove it. If _id is not part of the index, asking for it forces a document fetch and the query is no longer covered. In explain, a covered plan stops at the index: you want totalDocsExamined at 0, and a PROJECTION_COVERED stage rather than FETCH.

Covered queries matter most when documents are large, or when the same small lookup runs on every request. For a single fat document that you actually render, fetch it. Do not contort the index just to avoid one fetch.

Filter in the database

This pattern gets slower in a straight line as the collection grows, even if the page only needs one post:

const posts = await Post.find().select("title slug status").lean();
const post = posts.find((item) => item.slug === slug && item.status === "published");

The server still reads every document. The application then throws most of them away. Push both predicates into the query:

const post = await Post.findOne({ slug, status: "published" })
  .select("title content tags status createdAt")
  .lean();

That findOne wants an index on { slug: 1 }. If almost every read is for published posts, a partial index keeps the index smaller:

PostSchema.index(
  { slug: 1 },
  { unique: true, partialFilterExpression: { status: "published" } }
);

A partial index is eligible only when the query includes the partial predicate. findOne({ slug }) will not use the index above. findOne({ slug, status: "published" }) will.

The same rule applies to loops. Loading an order, then querying its lines once per order, is an application-side nested scan. One query with $in on the indexed foreign key, or a single $lookup after you have already filtered the parent set, stays proportional to the rows you need.

Paginate with a range, not with skip

skip(20000).limit(20) still walks those 20,000 index entries, then discards them. Cost grows with the page number, so page 1 looks fine and page 400 does not.

Keyset pagination asks for the next page after the last item you already showed. The sort field needs a tie-breaker, because createdAt is not unique:

const query = cursor
  ? {
      userId,
      $or: [
        { createdAt: { $lt: cursor.createdAt } },
        { createdAt: cursor.createdAt, _id: { $lt: cursor._id } },
      ],
    }
  : { userId };

const page = await Order.find(query)
  .sort({ createdAt: -1, _id: -1 })
  .limit(20)
  .lean();

The matching index is { userId: 1, createdAt: -1, _id: -1 }. Each page examines about 20 keys, regardless of how deep the user scrolls. The tradeoff is that you cannot jump to an arbitrary page number. Infinite scroll and "next" links fit this model. Offset pages do not.

Keep aggregation stages from defeating the index

An aggregation can use an index for the first $match and a following $sort, same as a find. Later stages see whatever that first stage emitted, and they do not get a second chance at the collection index.

Put the selective $match first. Sort immediately after it if the index can satisfy both. Narrow fields with $project before a $lookup or a $group, so those stages are not hauling full documents around.

Order.aggregate([
  { $match: { userId, status: "paid" } },
  { $sort: { createdAt: -1 } },
  { $limit: 50 },
  { $project: { userId: 1, total: 1, createdAt: 1 } },
  {
    $lookup: {
      from: "users",
      localField: "userId",
      foreignField: "_id",
      as: "user",
    },
  },
]);

$lookup uses an index on the foreign field (users._id here, which you already have). It does not fix a $match that scanned the whole orders collection before the join. Limit first, then join.

Predicates that transform the indexed field will not use a normal index:

// walks the index
{ createdAt: { $gte: new Date("2026-01-01") } }

// applies a function to every value
{ $expr: { $eq: [{ $year: "$createdAt" }, 2026] } }

Store a bucket you can match with equality, such as createdMonth: "2026-01", if you constantly filter by calendar parts. Do not wrap the raw timestamp in $year and hope the planner figures it out.

A few predicates that silently skip indexes

Anchored prefixes can use an index. Unanchored search cannot.

{ email: /^rayden/ }   // index on email can serve this
{ email: /rayden/ }    // collection or index scan, no prefix bound
{ email: /^rayden/i }  // case-insensitive; a normal index will not serve it

Case-insensitive match needs an index built with the same collation as the query. If the collation differs, the planner ignores the index.

Array fields are multi-key: one index entry per element. A compound index can include one array field, not two. If both tags and categories are arrays, index them separately or stop querying them as a pair.

Negation is a poor leading predicate. { status: { $ne: "deleted" } } matches almost everything, so the index does little work. A positive predicate (status: "published") or a partial index that only contains live documents is the better shape.

Indexes are not free

Every index is updated on insert, update, and delete. A collection with eight overlapping single-field indexes pays that cost on every write, and the planner spends longer choosing among them.

Check which indexes are actually used before you add another:

db.orders.aggregate([{ $indexStats: {} }]);

accesses.ops near zero over a representative window means the index is a write tax. Drop it after you confirm no infrequent job depends on it. Build the replacement first, watch the plan change, then drop the old one. On current MongoDB versions you do not pass background: true; the server already builds indexes without the old foreground lock. Do the build when write traffic is lowest anyway, because it still consumes I/O.

What the application can speed up, and what it cannot

lean() skips Mongoose document hydration. For a read path that only renders JSON, that is often a large CPU win. It does not change totalDocsExamined. A COLLSCAN with lean() is still a COLLSCAN.

A cache in front of the query, such as unstable_cache around a read that rarely changes, removes repeated trips. It is the right tool when the same result is served to many requests and a short stale window is acceptable. It hides a bad plan until the cache misses, and it does nothing for a query whose parameters are different every time.

Fix the plan first. Cache the result only after the uncached call is cheap enough to survive a cold start.

A loop you can reuse

1. Capture explain("executionStats") for the slow shape.
2. Compare nReturned with totalDocsExamined and totalKeysExamined.
3. Rewrite the filter so equality fields lead, the sort matches the index, and the range is last.
4. Project the fields the caller uses. Cover the query when those fields are few and the call is hot.
5. Move filtering and pagination into the server. Replace deep skip with a range on the sort key plus _id.
6. Read $indexStats, and delete indexes this query shape does not use.
7. Run explain again on a realistic data volume. A plan that wins on 200 documents often loses on 2 million.