Module Review: Indexing

[!NOTE] This module explores the core principles of Module Review: Indexing, deriving solutions from first principles and hardware constraints to build world-class, production-ready expertise.

1. Key Takeaways

  • Genesis: Indexes trade Disk Space and Write Latency for Read Speed. They convert an O(N) linear scan into an O(log N) B-Tree traversal.
  • The ESR Rule: For compound indexes, always order fields by Equality, then Sort, then Range. Range fields before Sort fields cause In-Memory Sorts.
  • Covered Queries: If an index contains all fields in the query filter AND projection, MongoDB never touches the disk (totalDocsExamined: 0).
  • Profiling: Always check nReturned vs totalDocsExamined. A healthy query has a 1:1 ratio.
  • Bottlenecks: Watch out for COLLSCAN (no index) and SORT (in-memory sort > 100MB).

2. Flashcards

What is the "ESR Rule"?
Equality, Sort, Range. The optimal order for fields in a compound index to support filtering and sorting without an in-memory sort.
What is a Covered Query?
A query where all requested fields are present in the index itself, so MongoDB doesn't need to read the document from disk.
What happens if you index an Array field?
It becomes a Multikey Index. MongoDB creates a separate index entry for EVERY element in the array, potentially exploding index size.
What is the default Sort Limit in MongoDB?
100 MB. If a query requires an in-memory sort larger than this, it fails unless `allowDiskUse: true` is set.
What does `totalDocsExamined` >> `nReturned` mean?
Inefficient Indexing. The database is reading many documents from disk but only returning a few. The index is not selective enough.
What is a Sparse Index?
An index that only contains entries for documents that actually have the indexed field.

3. Cheat Sheet

Concept Command / Syntax Purpose
Create Index db.coll.createIndex({ field: 1 }) Create an ascending index.
Compound db.coll.createIndex({ A: 1, B: -1 }) Sort by A (asc), then B (desc).
TTL Index createIndex({ time: 1 }, { expireAfterSeconds: 3600 }) Auto-delete docs after 1 hour.
Explain .explain("executionStats") View query plan and stats.
Force Plan .hint({ field: 1 }) Force MongoDB to use a specific index.
Covered Projection excludes _id and non-indexed fields Zero Disk I/O query.
ESR Rule Equality → Sort → Range Optimal compound index order.
List Indexes db.coll.getIndexes() Show all indexes on collection.
Hide Index db.coll.hideIndex("name") Temporarily disable index (for testing).

4. Next Steps

You have mastered single-server performance. But what happens when your data grows beyond the capacity of one machine? In the next module, we explore Replication & Sharding—the architecture of distributed systems.

MongoDB Glossary