Indexes and explain

Introduction

Without indexes, MongoDB scans every document in a collection—a COLLSCAN. As data grows, unindexed queries become slow and expensive. This chapter creates single-field, compound, unique, and partial indexes, reads explain("executionStats"), and applies the ESR rule (Equality, Sort, Range) for compound index design.

Prerequisites

• Find Queries and Projection
• Query Operators
• Sample hello_demo.posts data — re-seed from chapter 5 if empty

Why Indexes Matter

Indexes are B-tree structures (by default) that map field values to document locations—similar in purpose to MySQL indexes, but support documents and multi-key (array) paths.

Trade-offs:

Create and Inspect Indexes

Index names default to field_direction concatenation.

Unique Index

Enforce one document per slug:

db.posts.createIndex({ slug: 1 }, { unique: true })

Duplicate insert/update raises E11000 duplicate key error.

Partial unique (only published posts):

db.posts.createIndex(
  { slug: 1 },
  {
    unique: true,
    partialFilterExpression: { published: true }
  }
)

Compound Indexes and ESR Rule

For query:

db.posts.find({ published: true, viewCount: { $gte: 50 } })
  .sort({ createdAt: -1 })

Prefer index field order:

1. Equality — published
2. Sort — createdAt
3. Range — viewCount

Example index:

db.posts.createIndex({ published: 1, createdAt: -1, viewCount: 1 })

Not every query needs a perfect index—optimize the top slow queries from logs.

Multikey Indexes (Arrays)

Index on array field indexes each array element:

db.posts.createIndex({ tagIds: 1 })
db.posts.find({ tagIds: ObjectId("665f0123456789abcdef01234") })

One compound index can include at most one array field (multikey limitation).

Covered Queries

Query satisfied entirely from index—no document fetch:

db.posts.createIndex({ slug: 1, title: 1 })
 
db.posts.find(
  { slug: "mongodb-basics" },
  { _id: 0, slug: 1, title: 1 }
).explain("executionStats")

Look for totalDocsExamined: 0 in winning plan when fully covered.

explain("executionStats")

db.posts.find({ published: true }).sort({ createdAt: -1 }).explain("executionStats")

Key fields in output:

Bad plan example:

stage: COLLSCAN
totalDocsExamined: 100000

Add or fix index until IXSCAN with small totalDocsExamined.

Text Index (Simple Search)

db.posts.createIndex({ title: "text", body: "text" })
 
db.posts.find({ $text: { $search: "mongodb indexing" } })

One text index per collection. For advanced search consider Atlas Search or Elasticsearch.

Partial Index

Index subset of documents—smaller, faster:

db.posts.createIndex(
  { createdAt: -1 },
  { partialFilterExpression: { published: true } }
)

Only published posts appear in index.

TTL Index (Review)

From Delete and TTL—index on date + expireAfterSeconds. Do not duplicate TTL and query index without planning—often combine one TTL field index.

Index Anti-Patterns

Workflow for Slow Query

1. Reproduce with explain("executionStats")
2. Identify COLLSCAN or high totalDocsExamined
3. Add compound index matching filter + sort
4. Re-run explain; verify IXSCAN
5. Monitor in production — Performance

Post-Chapter Checklist

• createIndex on slug (unique) and query patterns you use
• Ran explain before and after index
• You can read IXSCAN vs COLLSCAN
• You understand index write overhead

FAQ

_id index enough?

_id helps find({ _id }) only. Add indexes for slug, userId, etc.

Index every field?

No—index fields that appear in selective filters and sorts.

hideIndex vs dropIndex?

hideIndex (4.4+) test drop impact before removing.

Sparse index?

Indexes only docs with field present—largely replaced by partial indexes.

Same as Redis?

Redis has no user-defined B-tree indexes on documents—different product — Redis.