[quick-revisit]: MongoDB Indexes & B-Trees

The Mental Model

A MongoDB collection without an index is like a cookbook without an index section—finding a single recipe requires flipping through all 500 pages. An index extracts a small subset of fields, sorts them, and maps them to exact disk locations so MongoDB can turn directly to the target page and jump straight to the document.

---

The Basics

An index is a specialized, memory-friendly data structure that stores a tiny slice of your collection's data in a pre-sorted format.

Key characteristics to keep in mind:

• The Structure: MongoDB uses B-Trees to organize index keys, allowing logarithmic lookup times O(log N).
• Field Ordering: Index entries are stored strictly ordered by field value (ascending 1 or descending -1).
• The Default Index: Every collection automatically gets a unique index on _id. This index cannot be dropped and enforces primary key uniqueness.

---

4 Rules That Prevent Silent Database Bugs

1. Know Your Execution Stages (COLLSCAN vs IXSCAN)

Always run .explain("executionStats") on queries you suspect are slow. Look at the stage field in the output:

• COLLSCAN (Collection Scan): MongoDB inspected every single document in the collection. This is a massive red flag in production.
• IXSCAN (Index Scan): MongoDB used a B-Tree index to locate only the target documents.

⚠️ The Ratio Gotcha: Pay attention to totalDocsExamined vs nReturned. If totalDocsExamined is 100,000 and nReturned is 2, your query spent 99.98% of its effort reading irrelevant data off disk.

---

2. The ESR Rule for Compound Indexes (Equality, Sort, Range)

When indexing multiple fields (e.g., { status: 1, age: 1, createdAt: -1 }), key order matters immensely. Follow the ESR Rule:

1. Equality: Place exact match fields first (e.g., status: "ACTIVE").
2. Sort: Place sorting fields second (e.g., createdAt: -1).
3. Range: Place range filter fields last (e.g., age: { $gte: 21 }).

// ❌ POOR INDEX: Range before Equality forces in-memory sorting
db.users.createIndex({ age: 1, status: 1 });

// ✅ OPTIMAL INDEX: Equality first, Range/Sort last
db.users.createIndex({ status: 1, age: 1 });

---

3. The Embedded Document Trap

Single-field indexes work on embedded sub-documents, but only when querying the entire object verbatim.

// Given a document: { user: { name: "Mitha", role: "admin" } }
db.logs.createIndex({ "user": 1 });

// ❌ FAILS TO USE INDEX: Querying a specific sub-field skips the index
db.logs.find({ "user.name": "Mitha" }); 

// ✅ USES INDEX: Must query dot-notation field directly instead
db.logs.createIndex({ "user.name": 1 });

---

4. Respect The "Write Penalty"

Indexes take up memory and need to be maintained. Every time you run insertOne(), updateOne(), or deleteOne(), MongoDB must modify the collection AND rebalance every B-Tree index on that collection.

• High Read / Low Write: Index aggressively.
• High Write / Low Read (e.g., IoT logs, telemetry): Keep indexes to an absolute minimum. Excess indexes on write-heavy collections will choke IOPS and create storage bloat.

---

Quick Reference: Query Patterns vs. Index Choice

When your query looks like...	Use this Index...	What MongoDB actually does
find({ email: "[e-mail]" })	Single Field { email: 1 }	B-Tree lookup on one sorted field. Instant O(log N) match.
find({ status: "ACTIVE" }).sort({ date: -1 })	Compound { status: 1, date: -1 }	Navigates to "ACTIVE" group, then reads pre-sorted dates directly. Avoids in-memory sort.
find({ tags: "node" }) (where tags is an array)	Multikey { tags: 1 }	Creates B-Tree index keys for every individual array element automatically.
find({ $text: { $search: "database" } })	Text { description: "text" }	Builds an inverted index of stemmed words to enable full-text keyword search.
find() on temporary data (e.g. JWT tokens, OTPs)	TTL (Time-To-Live) { createdAt: 1 }, { expireAfterSeconds: 3600 }	Background task checks timestamp and auto-deletes expired documents after $N$ seconds.