MongoDB Query Optimization and Indexing

TL;DR — Run explain("executionStats") on slow queries. Aim for IXSCAN, not COLLSCAN. Index your filter + sort fields. Use projection. Prefer cursor-based pagination over large skip().

Slow queries? Your user lookup takes 2 seconds. Your feed times out. The fix is usually the right indexes—not a bigger server. This guide shows you how.

Why Indexes Matter

Without an index, MongoDB does a collection scan (COLLSCAN): it reads every document. With an index, it jumps to matches. Much faster.

Goal: See IXSCAN in explain, and keep totalDocsExamined close to nReturned.

1. Index Your Filter and Sort Fields

Create an index that includes the fields you filter and sort on.

JavaScript

// For: find({ status: "active" }).sort({ createdAt: -1 })
db.collection.createIndex({ status: 1, createdAt: -1 });

// For: find({ userId: "u1" }).sort({ createdAt: -1 })
db.events.createIndex({ userId: 1, createdAt: -1 });

2. Compound Indexes — Order Matters

MongoDB uses a compound index for queries that use a prefix of the index. So { a: 1, b: 1, c: 1 } helps:

{ a: 1 } ✓
{ a: 1, b: 1 } ✓
{ a: 1, b: 1, c: 1 } ✓
{ b: 1 } ✗ (no prefix)

Put the most selective or frequently used fields first.

3. Use Explain

Run explain("executionStats") to see what's happening:

JavaScript

db.collection.find({ status: "active" }).sort({ createdAt: -1 }).explain("executionStats");

Look for:

stage: "IXSCAN" — Good. Using index.
stage: "COLLSCAN" — Bad. Full collection scan.
totalDocsExamined vs nReturned — Should be close.

4. Don't Over-Index

Each index costs writes. Index only what you query or sort on. Drop unused indexes.

JavaScript

db.collection.getIndexes();
db.collection.dropIndex("indexName");

5. Use Projection

Return only the fields you need. Less data, faster response.

JavaScript

db.collection.find(
  { status: "active" },
  { name: 1, email: 1, _id: 1 }
);

6. Cursor-Based Pagination

skip(N) for large N is slow—the server still walks through N documents. Use cursor-based pagination instead:

JavaScript

const lastId = req.query.after;
const filter = lastId ? { _id: { $lt: new ObjectId(lastId) } } : {};
const docs = await db.collection("posts")
  .find(filter)
  .sort({ _id: -1 })
  .limit(21)
  .toArray();

const hasMore = docs.length > 20;
const items = hasMore ? docs.slice(0, 20) : docs;
const nextCursor = hasMore ? items[items.length - 1]._id : null;

7. Partial Indexes

Index only documents that match a condition. Saves space when you often query a subset:

JavaScript

db.orders.createIndex(
  { userId: 1, createdAt: -1 },
  { partialFilterExpression: { status: "active" } }
);

Quick Checklist

Start with explain. Add indexes that match your real query patterns. For more backend patterns, see Backend Best Practices.