Prerequisites: Understanding of MongoDB basics. See NOSQL 02 MongoDB Basics and DB 03 B-Tree Index for index fundamentals.

Good news: MongoDB uses B-Tree indexes just like SQL Server. Your indexing knowledge transfers directly!

Part A: Index Fundamentals

1. Why Indexes Matter

Without an index, MongoDB must scan every document (Collection Scan):

graph LR
    subgraph "Without Index"
        Q1[Find age = 25] --> SCAN[Scan ALL documents]
        SCAN --> R1[Return matches]
    end
    
    subgraph "With Index"
        Q2[Find age = 25] --> INDEX[B-Tree Lookup]
        INDEX --> R2[Direct to documents]
    end
    
    style SCAN fill:#e74c3c,color:#fff
    style INDEX fill:#27ae60,color:#fff

2. Creating Indexes

// Single field index
db.users.createIndex({ email: 1 });          // Ascending
db.users.createIndex({ age: -1 });           // Descending

// Unique index
db.users.createIndex({ email: 1 }, { unique: true });

// Check existing indexes
db.users.getIndexes();

// Drop an index
db.users.dropIndex("email_1");

[!NOTE] Background Index Building: In older MongoDB versions, creating an index would lock the collection and block writes. Since MongoDB 4.2+, index builds are performed in the background by default. For large collections in production, you can still use db.collection.createIndex({...}, { background: true }) for compatibility.

3. Explain Plans

Use explain() to see if queries use indexes:

db.users.find({ email: "alice@example.com" }).explain("executionStats");

// Key fields to check:
// - queryPlanner.winningPlan.stage: "IXSCAN" (good) vs "COLLSCAN" (bad)
// - executionStats.totalDocsExamined: should be low
// - executionStats.executionTimeMillis: should be fast

Part B: Compound Indexes

4. What is a Compound Index?

An index on multiple fields — order matters!

// Compound index on status + date
db.orders.createIndex({ status: 1, orderDate: -1 });

graph TD
    subgraph "Compound Index: status + date"
        ROOT[Index Root]
        ROOT --> A[status: active]
        ROOT --> B[status: pending]
        ROOT --> C[status: shipped]
        
        A --> A1[date: 2024-03-15]
        A --> A2[date: 2024-03-14]
        
        B --> B1[date: 2024-03-15]
    end

5. The ESR Rule (Equality, Sort, Range)

The optimal order for compound index fields:

graph LR
    E[Equality<br/>Exact match] --> S[Sort<br/>ORDER BY]
    S --> R[Range<br/>Greater than, etc.]
    
    style E fill:#27ae60,color:#fff
    style S fill:#3498db,color:#fff
    style R fill:#f39c12,color:#fff

Example

Query:

db.orders.find({
  status: "active",        // Equality
  amount: { $gt: 100 }     // Range
}).sort({ orderDate: -1 }); // Sort

Index Design:

// ✅ Good: ESR order
db.orders.createIndex({ status: 1, orderDate: -1, amount: 1 });

// ❌ Bad: Range before Sort
db.orders.createIndex({ status: 1, amount: 1, orderDate: -1 });

6. Prefix Rule

A compound index can support queries on its prefixes:

// Index: { a: 1, b: 1, c: 1 }

// ✅ Covered:
db.col.find({ a: 1 });           // Uses index
db.col.find({ a: 1, b: 2 });     // Uses index
db.col.find({ a: 1, b: 2, c: 3 }); // Uses index

// ❌ Not covered:
db.col.find({ b: 2 });           // Cannot use index
db.col.find({ c: 3 });           // Cannot use index
db.col.find({ b: 2, c: 3 });     // Cannot use index

[!NOTE] Index Intersection vs Compound Index: MongoDB can use multiple single-field indexes together (Index Intersection), but this is usually slower than a well-designed compound index. The query planner must merge results from both indexes, adding overhead. Always prefer a compound index when you frequently query multiple fields together.

Part C: Specialized Indexes

7. Geospatial Index (2dsphere)

For location-based queries like “Find restaurants within 1km”:

// Store location as GeoJSON
db.restaurants.insertOne({
  name: "Pizza Palace",
  location: {
    type: "Point",
    coordinates: [121.5654, 25.0330]  // [longitude, latitude]
  }
});

// Create geospatial index
db.restaurants.createIndex({ location: "2dsphere" });

// Find within 1km
db.restaurants.find({
  location: {
    $near: {
      $geometry: {
        type: "Point",
        coordinates: [121.5654, 25.0330]
      },
      $maxDistance: 1000  // meters
    }
  }
});

Common Geo Operators

8. TTL Index (Time-To-Live)

Documents automatically delete after a specified time:

// Sessions expire after 1 hour
db.sessions.createIndex(
  { createdAt: 1 },
  { expireAfterSeconds: 3600 }  // 1 hour
);

// Insert a session
db.sessions.insertOne({
  userId: "user123",
  token: "abc...",
  createdAt: new Date()  // Will be deleted after 1 hour
});

Use Cases:

• Session tokens
• Verification codes
• Temporary logs
• Cache entries

9. Text Index

Full-text search within string fields:

// Create text index
db.articles.createIndex({ title: "text", content: "text" });

// Search
db.articles.find({ $text: { $search: "mongodb tutorial" } });

// With score
db.articles.find(
  { $text: { $search: "mongodb" } },
  { score: { $meta: "textScore" } }
).sort({ score: { $meta: "textScore" } });

10. Partial Index

Index only documents matching a filter — saves space and improves performance:

// Only index active orders (skip inactive ones)
db.orders.createIndex(
  { createdAt: 1 },
  { partialFilterExpression: { status: "active" } }
);

// Only index documents with the field present
db.users.createIndex(
  { email: 1 },
  { partialFilterExpression: { email: { $exists: true } } }
);

Use Cases:

• Index only “active” records in a large table
• Skip documents with null/missing values
• Reduce index size by 50%+ in many cases

Part D: Sharding (Horizontal Scaling)

10. What is Sharding?

Distributing data across multiple servers:

graph TD
    subgraph "Single Server (Before)"
        S1[Server: 1TB Data]
    end
    
    subgraph "Sharded Cluster (After)"
        R[Router: mongos]
        R --> SH1[Shard 1: 333GB]
        R --> SH2[Shard 2: 333GB]
        R --> SH3[Shard 3: 333GB]
    end
    
    style S1 fill:#e74c3c,color:#fff
    style SH1 fill:#27ae60,color:#fff
    style SH2 fill:#27ae60,color:#fff
    style SH3 fill:#27ae60,color:#fff

11. Sharding Architecture

graph TD
    APP[Application] --> MONGOS[mongos Router]
    
    MONGOS --> CONFIG[Config Servers<br/>Metadata]
    
    MONGOS --> SH1[Shard 1<br/>Replica Set]
    MONGOS --> SH2[Shard 2<br/>Replica Set]
    MONGOS --> SH3[Shard 3<br/>Replica Set]

[!TIP] Chunk Balancer: MongoDB automatically moves chunks (ranges of shard key values) between shards to keep data evenly distributed. The Balancer runs in the background but can impact performance during migration. For production systems, configure a Balancer Window to run during off-peak hours:

// Set balancer window (only run 2-6 AM)
db.settings.update({ _id: "balancer" }, { $set: { activeWindow: { start: "02:00", stop: "06:00" } } }, { upsert: true });

12. Shard Key

The field used to distribute documents across shards:

// Enable sharding on database
sh.enableSharding("mydb");

// Shard a collection
sh.shardCollection("mydb.orders", { customerId: "hashed" });

Shard Key Strategies

// Hashed: Good distribution, poor range queries
sh.shardCollection("mydb.logs", { timestamp: "hashed" });

// Ranged: Good for range queries, risk of hot spots
sh.shardCollection("mydb.logs", { timestamp: 1 });

13. Choosing a Good Shard Key

Examples

// ✅ Good shard keys
{ customerId: "hashed" }     // High cardinality, even distribution
{ region: 1, timestamp: 1 }  // Compound for query isolation

// ❌ Bad shard keys
{ status: 1 }                // Low cardinality (active/inactive)
{ createdAt: 1 }             // Monotonic = hot shard

[!WARNING] Monotonic Shard Key Trap: Using a monotonically increasing field (like createdAt or auto-increment _id) as a ranged shard key causes all new writes to go to the same shard (the one handling the highest range). This creates a Write Hotspot — one shard is overloaded while others sit idle. Use hashed sharding for monotonic fields, or choose a different shard key with natural distribution.

14. Zone Sharding (Geographic Distribution)

Bind specific shard key ranges to specific shards — useful for data residency requirements (e.g., GDPR):

// Tag shards by region
sh.addShardTag("shard-eu", "EU");
sh.addShardTag("shard-us", "US");

// Assign ranges to zones
sh.updateZoneKeyRange(
  "mydb.users",
  { region: "EU", minKey: MinKey },
  { region: "EU", maxKey: MaxKey },
  "EU"
);

// Now all EU users stay on EU shard!

Use Cases:

• GDPR compliance (keep EU data in EU)
• Low-latency access (users connect to nearest shard)
• Tiered storage (hot data on fast SSDs, cold data on HDDs)

Part E: Index Best Practices

14. When to Create Indexes

15. Index Overhead

Every index has a cost:

graph LR
    subgraph "Trade-off"
        READ[Faster Reads]
        WRITE[Slower Writes]
        SPACE[More Storage]
    end
    
    INDEX[Add Index] --> READ
    INDEX --> WRITE
    INDEX --> SPACE
    
    style READ fill:#27ae60,color:#fff
    style WRITE fill:#e74c3c,color:#fff
    style SPACE fill:#f39c12,color:#fff

16. Covered Queries

A query is covered when the index contains all requested fields:

// Index
db.users.createIndex({ email: 1, name: 1 });

// ✅ Covered query (only uses index, no FETCH)
db.users.find(
  { email: "alice@example.com" },
  { name: 1, _id: 0 }
);

// ❌ Not covered (needs FETCH for age)
db.users.find(
  { email: "alice@example.com" },
  { name: 1, age: 1, _id: 0 }
);

[!TIP] Why Covered Queries Are the Performance Ceiling: In a covered query, MongoDB returns results directly from the index without ever loading the actual document from disk. This eliminates disk I/O entirely — the biggest performance bottleneck. For high-volume queries on large collections, designing covered queries can improve throughput by 10x or more.

Summary

Index Type Cheat Sheet

ESR Rule Summary

E - Equality fields first (exact match)
S - Sort fields second (ORDER BY)
R - Range fields last (>, <, $in)

Sharding Decision Guide

💡 Practice Questions

Conceptual

1. Explain the ESR rule (Equality, Sort, Range) for compound index design.

2. What is a covered query and why is it faster?

3. What is a Shard Key and what makes a good one vs a bad one?

4. Compare TTL indexes vs manual deletion. When would you use each?

Hands-on

// Given this query pattern:
db.orders.find({ status: "active", amount: { $gt: 100 } }).sort({ createdAt: -1 });

// Design the optimal compound index using ESR rule.

// E (Equality): status
// S (Sort): createdAt
// R (Range): amount

db.orders.createIndex({ status: 1, createdAt: -1, amount: 1 });

Scenario

1. Scaling Decision: Your MongoDB database is 500GB and growing 50GB per month. Read latency is fine but write throughput is maxed out. Would you recommend sharding? If yes, how would you choose the shard key?