MongoDB Indexing: Make Your Queries Fast

Slow MongoDB queries are almost always an indexing problem. Understanding how MongoDB chooses indexes, when it falls back to a collection scan, and what the numbers inside explain() actually mean will turn guesswork into a systematic debugging process.

How MongoDB Finds Documents Without an Index

Without an index, MongoDB performs a collection scan (COLLSCAN): it reads every document in the collection to find matches. On a small collection this is fine. On a collection with millions of documents it is unacceptable — and the performance cliff arrives faster than most developers expect.

An index stores a sorted subset of field values alongside a pointer to the document. MongoDB can binary-search the index instead of reading the whole collection, turning an O(n) scan into an O(log n) lookup.

Single-Field Indexes

The simplest index targets one field:

js
// Create an ascending index on the email field
db.users.createIndex({ email: 1 })

// Descending index (useful when sorting in that direction)
db.users.createIndex({ createdAt: -1 })

Direction matters for sort operations but not for equality matches. If your query is { email: "a@b.com" } both 1 and -1 work equally well. If your query sorts by createdAt DESC, an index with -1 avoids an in-memory sort step.

Compound Indexes and the ESR Rule

Compound indexes cover multiple fields and are where most of the tuning work happens. MongoDB's ESR rule is the practical ordering guide:

• Equality fields first
• Sort fields second
• Range fields last

js
// Query: find active users in a region, sorted by createdAt
db.users.createIndex({ status: 1, createdAt: -1, region: 1 })
// status = equality, createdAt = sort, region = range

A compound index on { a: 1, b: 1, c: 1 } satisfies queries on a, a + b, and a + b + c — the prefix rule. A query on b alone cannot use this index. This is the most common compound index mistake: assuming an index on { b: 1, a: 1 } is equivalent.

Covered Queries

A covered query is one where MongoDB satisfies the entire query — including projection — from the index without touching any documents. These are the fastest possible queries.

js
db.orders.createIndex({ userId: 1, status: 1, total: 1 })

// This query is covered: all fields in the filter and projection exist in the index
db.orders.find(
  { userId: "u123", status: "shipped" },
  { _id: 0, total: 1 }
)

If _id is not excluded in the projection, the query is no longer covered because _id is not in the index. This is a subtle and common coverage breaker.

Reading explain()

explain("executionStats") is the ground truth for what MongoDB actually did:

js
db.orders.find({ userId: "u123" }).explain("executionStats")

Key fields to check:

• winningPlan.stage — should be IXSCAN (index scan), not COLLSCAN.
• totalDocsExamined — should be close to totalDocsReturned. A ratio of 10:1 or worse signals a bad index.
• executionTimeMillis — the wall-clock time; use this as your baseline before and after index changes.
• indexName — confirms which index was selected. If MongoDB chose the wrong one, use a hint to force the correct index.

js
// Forcing a specific index
db.orders.find({ userId: "u123" }).hint({ userId: 1, status: 1 })

Partial and Sparse Indexes

For collections where most documents share a common value, a partial index dramatically reduces index size:

js
// Only index documents where status is "pending" — not the millions of "archived" ones
db.orders.createIndex(
  { createdAt: -1 },
  { partialFilterExpression: { status: "pending" } }
)

A sparse index skips documents where the indexed field is missing — useful when a field is optional and null values should not appear in the index.

Index Maintenance Costs

Every write — insert, update, delete — must update all relevant indexes. An over-indexed collection suffers write amplification. A practical rule: audit indexes with db.collection.getIndexes() and remove any that are never chosen as the winningPlan index across your most frequent queries.

The MongoDB Atlas Performance Advisor automates this by logging slow queries and suggesting missing indexes, which pairs well with a well-structured project layout where your data-access layer is isolated and easy to instrument.

Compound Indexes vs Multiple Single Indexes

MongoDB's query planner can intersect two single-field indexes to answer a compound predicate, but index intersection adds overhead. A single compound index tailored to your query shape is almost always faster than relying on intersection. Use multiple single indexes only when the fields are genuinely queried independently with high frequency.

What to Index First

Start with the queries that run most frequently and return the most data. Use pagination patterns to limit working sets, add indexes on the fields that appear in find() filters and sort() calls, verify with explain(), and measure again. Avoid premature indexing — an unused index still costs you on every write.

For a broader view of where MongoDB fits in a production backend, see the API design guide for Next.js and the database transactions guide for when your indexed queries need to run inside multi-document operations.

Conclusion

MongoDB indexing is not a one-time task — it is an ongoing process of reading explain(), measuring real query latency, and removing indexes that no longer earn their write-time cost. Single-field indexes are the starting point; compound indexes with ESR ordering and covered queries are where significant gains live. Build the habit of checking explain() before shipping any new query to production.