⚡ MongoDB — Lesson 6: Indexes & Performance

Without an index, MongoDB performs a Collection Scan (COLLSCAN) — it reads every single document to find matches. With an index, it performs an Index Scan (IXSCAN) — it reads only the relevant index entries and fetches matching documents directly.

// Without an index on "email"
db.users.find({ email: "alice@example.com" })
// MongoDB reads ALL 10 million users — very slow!

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

// Now the same query
db.users.find({ email: "alice@example.com" })
// MongoDB reads 1 index entry, fetches 1 document — microseconds!

// Single field index — ascending
db.users.createIndex({ email: 1 })
db.users.createIndex({ age: 1 })

// Single field index — descending
db.users.createIndex({ createdAt: -1 })
// For single fields, 1 and -1 perform identically (can traverse either direction)

// Name your indexes for easy management
db.users.createIndex(
  { email: 1 },
  { name: "idx_users_email" }
)

// Background option (deprecated in v4.2 — all indexes now build in background)
db.users.createIndex({ phone: 1 }, { background: true })

// List all indexes on a collection
db.users.getIndexes()

// Drop a specific index by name
db.users.dropIndex("idx_users_email")

// Drop a specific index by key pattern
db.users.dropIndex({ email: 1 })

// Drop ALL indexes (except _id)
db.users.dropIndexes()

explain() shows how MongoDB executes a query. Always run this to verify your indexes are being used.

// Basic explain — shows query plan
db.users.find({ email: "alice@example.com" }).explain()

// Execution stats — shows actual performance metrics
db.users.find({ email: "alice@example.com" }).explain("executionStats")

// allPlansExecution — shows all plans MongoDB considered
db.users.find({ email: "alice@example.com" }).explain("allPlansExecution")

Reading explain() Output

// Key fields to look at:
{
  queryPlanner: {
    winningPlan: {
      stage: "FETCH",           // FETCH = good (using index)
      inputStage: {
        stage: "IXSCAN",         // IXSCAN = using index ✓
        // stage: "COLLSCAN"    // COLLSCAN = NOT using index ✗
        indexName: "email_1",   // which index was used
        direction: "forward"
      }
    }
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 1,              // documents returned
    totalKeysExamined: 1,      // index entries scanned — should be ≈ nReturned
    totalDocsExamined: 1,      // documents fetched — should be ≈ nReturned
    executionTimeMillis: 0     // query duration in ms
  }
}

// BAD: no index (totalDocsExamined >> nReturned)
// {
//   stage: "COLLSCAN",
//   totalKeysExamined: 0,
//   totalDocsExamined: 1000000,   // scanned 1M docs
//   nReturned: 1,                 // but only returned 1
//   executionTimeMillis: 843      // 843ms — very slow
// }

Compound indexes span multiple fields. The order of fields matters. Use the ESR rule: Equality first, Sort second, Range last.

// Compound index on multiple fields
db.products.createIndex({ category: 1, price: 1 })
db.orders.createIndex({ userId: 1, status: 1, createdAt: -1 })

// ESR Rule Example:
// Query: find active users in USA, sorted by joinDate, where age is 25-35
db.users.find({
  status: "active",            // Equality
  country: "USA",             // Equality
  age: { $gte: 25, $lte: 35 } // Range
}).sort({ joinDate: -1 })     // Sort

// ESR index order: Equality → Sort → Range
db.users.createIndex({
  status: 1,   // E - equality
  country: 1,  // E - equality
  joinDate: -1, // S - sort
  age: 1        // R - range
})

// Prefix rule — compound index supports prefix queries
// Index: { a:1, b:1, c:1 }
// Supported: find({a}), find({a,b}), find({a,b,c})
// NOT supported: find({b}), find({c}), find({b,c})

// Covered query example
db.users.createIndex({ email: 1, name: 1 })

// This is a COVERED QUERY — no document fetch needed!
db.users.find(
  { email: "alice@example.com" },
  { email: 1, name: 1, _id: 0 }  // only indexed fields, exclude _id
)
// explain() shows: "totalDocsExamined": 0  ← amazing!

// Create a text index on one field
db.posts.createIndex({ title: "text" })

// Text index on multiple fields
db.posts.createIndex({
  title: "text",
  body: "text",
  tags: "text"
})

// Text index with field weights (higher = more important)
db.posts.createIndex(
  { title: "text", body: "text" },
  { weights: { title: 10, body: 1 } }
)
// Matches in title score 10x higher than matches in body

// Search using $text
db.posts.find({ $text: { $search: "mongodb performance" } })

// With relevance score projection and sort
db.posts.find(
  { $text: { $search: "mongodb" } },
  { score: { $meta: "textScore" } }
).sort({ score: { $meta: "textScore" } })

// One collection can only have ONE text index (can span multiple fields)

TTL (Time To Live) indexes automatically delete documents after a specified time. Perfect for sessions, tokens, logs, or cache collections.

// Delete documents 24 hours after their "createdAt" date
db.sessions.createIndex(
  { createdAt: 1 },
  { expireAfterSeconds: 86400 }  // 86400s = 24 hours
)

// Delete documents 7 days after "expiresAt" date
db.passwordTokens.createIndex(
  { expiresAt: 1 },
  { expireAfterSeconds: 0 }  // 0 = delete at the exact expiresAt time
)
// Insert with a specific expiry:
db.passwordTokens.insertOne({
  token: "abc123",
  userId: ObjectId("..."),
  expiresAt: new Date(Date.now() + 3600000)  // expires in 1 hour
})

// TTL cleanup runs every 60 seconds (not immediate)
// Field must be a Date or array of Dates

Unique Index

// Unique index — prevents duplicate values
db.users.createIndex({ email: 1 }, { unique: true })

// Compound unique index
db.enrollments.createIndex(
  { studentId: 1, courseId: 1 },
  { unique: true }
)
// Prevents a student from enrolling in the same course twice

// Trying to insert a duplicate will throw WriteError
// E11000 duplicate key error collection: email_1

Sparse Index

// Sparse index — only indexes documents that HAVE the field
// Documents without the field are excluded from the index
db.users.createIndex({ phone: 1 }, { sparse: true })

// Useful for optional unique fields
// (normal unique index would prevent multiple docs with null phone)
db.users.createIndex({ githubUsername: 1 }, { unique: true, sparse: true })

Partial Index

// Partial index — only indexes documents matching a filter
// More efficient than sparse — you control exactly which docs are indexed

// Only index active users
db.users.createIndex(
  { email: 1 },
  { partialFilterExpression: { active: true } }
)

// Index posts with high view count (often queried)
db.posts.createIndex(
  { views: -1 },
  { partialFilterExpression: { views: { $gt: 1000 } } }
)

// Query MUST include the partial filter for the index to be used
db.users.find({ email: "alice@example.com", active: true })
// ↑ Will use the partial index

db.users.find({ email: "alice@example.com" })
// ↑ Will NOT use the partial index (filter not included)

// List all indexes
db.users.getIndexes()
// Output example:
// [
//   { v:2, key:{_id:1}, name:"_id_" },
//   { v:2, key:{email:1}, name:"email_1", unique:true },
//   { v:2, key:{createdAt:-1}, name:"createdAt_-1" }
// ]

// Drop index by name
db.users.dropIndex("email_1")

// Drop index by key specification
db.users.dropIndex({ email: 1 })

// Drop all non-_id indexes
db.users.dropIndexes()

// Rebuild indexes (use after bulk data operations)
db.users.reIndex()

// Check index size
db.users.stats().indexSizes

// Find unused indexes — look for queries doing COLLSCAN
db.setProfilingLevel(2)  // log all operations
db.system.profile.find({ millis: { $gt: 100 } }).sort({ millis: -1 })