MongoDB Indexing & Optimization

Master performance optimization through proper indexing.

Quick Start

Create Indexes

// Single field index await collection.createIndex({ email: 1 });

// Compound index await collection.createIndex({ status: 1, createdAt: -1 });

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

// Sparse index (skip null values) await collection.createIndex({ phone: 1 }, { sparse: true });

// Text index (full-text search) await collection.createIndex({ title: 'text', description: 'text' });

// TTL index (auto-delete documents) await collection.createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 });

List and Analyze Indexes

// List all indexes const indexes = await collection.indexes(); console.log(indexes);

// Drop an index await collection.dropIndex('email_1');

// Drop all non-_id indexes await collection.dropIndexes();

Explain Query Plan

// Analyze query execution const explain = await collection.find({ email: 'test@example.com' }).explain('executionStats');

console.log(explain.executionStats); // Shows: executionStages, nReturned, totalDocsExamined, executionTimeMillis

Index Types

Single Field Index

// Index on one field db.collection.createIndex({ age: 1 })

// Query uses index if searching on age db.collection.find({ age: { $gte: 18 } })

Compound Index

// Index on multiple fields - order matters! db.collection.createIndex({ status: 1, createdAt: -1 })

// Queries that benefit: // 1. { status: 'active', createdAt: { $gt: date } } // 2. { status: 'active' } // But NOT: { createdAt: { $gt: date } } alone

Array Index (Multikey)

// Automatically created for arrays db.collection.createIndex({ tags: 1 })

// Matches documents where tags contains value db.collection.find({ tags: 'mongodb' })

Text Index

// Full-text search db.collection.createIndex({ title: 'text', body: 'text' })

// Query db.collection.find({ $text: { $search: 'mongodb database' } })

Geospatial Index

// 2D spherical for lat/long db.collection.createIndex({ location: '2dsphere' })

// Find nearby db.collection.find({ location: { $near: { type: 'Point', coordinates: [-73.97, 40.77] }, $maxDistance: 5000 } })

Index Design: ESR Rule

Equality, Sort, Range

// Query: find active users, sort by created date, limit age db.users.find({ status: 'active', age: { $gte: 18 } }).sort({ createdAt: -1 })

// Optimal index: db.users.createIndex({ status: 1, // Equality createdAt: -1, // Sort age: 1 // Range })

Performance Analysis

Check if Query Uses Index

const explain = await collection.find({ email: 'test@example.com' }).explain('executionStats');

// IXSCAN = Good (index scan) // COLLSCAN = Bad (collection scan) console.log(explain.executionStats.executionStages.stage);

Covering Query

// Query results entirely from index db.users.createIndex({ email: 1, name: 1, _id: 1 })

// This query is "covered" - no need to fetch documents db.users.find({ email: 'test@example.com' }, { email: 1, name: 1, _id: 0 })

Python Examples

from pymongo import ASCENDING, DESCENDING

Create index

collection.create_index([('email', ASCENDING)], unique=True)

Compound index

collection.create_index([('status', ASCENDING), ('createdAt', DESCENDING)])

Explain plan

explain = collection.find({'email': 'test@example.com'}).explain() print(explain['executionStats'])

Drop index

collection.drop_index('email_1')

Best Practices

✅ Index fields used in $match (early in pipeline) ✅ Use ESR rule for compound indexes ✅ Monitor index size and memory ✅ Remove unused indexes ✅ Use explain() to verify index usage ✅ Index strings with high cardinality ✅ Avoid indexing fields with many nulls ✅ Consider index intersection ✅ Regular index maintenance ✅ Monitor query performance