Section 14: Cheatsheets
🚀 MongoDB Indexing Cheatsheet
Complete guide to indexes, performance optimization, and best practices with live examples
What Are Indexes?
Why indexes are critical for performance
💡 The Book Analogy
Imagine trying to find the word "MongoDB" in a 1000-page book. Without an index, you'd have to read every single page. With an index, you flip to the back, find "MongoDB" in the alphabetical index, and jump directly to page 547. That's exactly what database indexes do!
📖 Without Index vs With Index
Without Index
45ms
• Full collection scan
• Examines every document
• Slow for large collections
• Examines every document
• Slow for large collections
With Index
2ms
• B-tree lookup
• Direct document access
• Scales with data size
• Direct document access
• Scales with data size
Index Types Overview
All MongoDB index types explained
| Index Type | Description | Use Case | Example |
|---|---|---|---|
| Single Field | Index on one field | Simple queries on one field | { email: 1 } |
| Compound | Index on multiple fields | Queries on multiple fields | { age: 1, city: 1 } |
| Multikey | Index on array fields | Queries on array elements | { tags: 1 } |
| Text | Full-text search index | Search in text fields | { content: "text" } |
| Geospatial | Location-based index | Near, within queries | { location: "2dsphere" } |
| Hashed | Hash-based index | Sharding, equality matches | { _id: "hashed" } |
| TTL | Auto-expire documents | Sessions, temporary data | { createdAt: 1 } + TTL |
Single Field Indexes
Most common index type
createIndex({ field: 1 })
Ascending
Create an index on a single field. Use 1 for ascending, -1 for descending.
mongosh
> // Create index on email field
> db.users.createIndex({ email: 1 })
{
numIndexesBefore: 1,
numIndexesAfter: 2,
createdCollectionAutomatically: false,
ok: 1
}
> // Query using the index
> db.users.find({ email: "john@example.com" })
.explain("executionStats")
{
executionStats: {
executionTimeMillis: 2, // Fast!
totalKeysExamined: 1,
totalDocsExamined: 1,
executionStages: {
stage: 'IXSCAN', // Using index ✓
indexName: 'email_1'
}
}
}
Unique Index
Constraint
Enforce uniqueness - prevents duplicate values in the indexed field.
mongosh
> // Create unique index on email
> db.users.createIndex(
{ email: 1 },
{ unique: true }
)
> // Try to insert duplicate email
> db.users.insertOne({ email: "john@example.com" })
// Error!
MongoServerError: E11000 duplicate key error
Index: email_1 dup key: { email: "john@example.com" }
Sparse Index
Optional
Only index documents that have the indexed field (skip nulls/missing).
mongosh
> // Create sparse index (only docs with phone)
> db.users.createIndex(
{ phone: 1 },
{ sparse: true }
)
> // Documents without 'phone' are NOT indexed
> // Saves space if many docs lack the field!
Compound Indexes
Multi-field indexes for complex queries
createIndex({ field1: 1, field2: 1 })
Multi-Field
Index on multiple fields. Field order matters! Supports prefix queries.
mongosh
> // Create compound index
> db.users.createIndex({ age: 1, city: 1 })
> // ✅ Can use index for:
> db.users.find({ age: 25 }) // Prefix match
> db.users.find({ age: 25, city: "NYC" }) // Full match
> // ❌ Cannot use index efficiently:
> db.users.find({ city: "NYC" }) // Not a prefix!
🎯 ESR Rule - Compound Index Order
For optimal compound indexes, follow the ESR Rule:
- Equality - Fields with exact matches first
- Sort - Fields used in sorting next
- Range - Fields with range queries last
ESR Rule Example
> // Query: Find active users in NYC, age 25-35, sort by name
> db.users.find({
status: "active", // E - Equality
city: "NYC", // E - Equality
age: { $gte: 25, $lte: 35 } // R - Range
}).sort({ name: 1 }) // S - Sort
> // ✅ Optimal index (following ESR):
> db.users.createIndex({
status: 1, // E - Equality first
city: 1, // E - Equality second
name: 1, // S - Sort third
age: 1 // R - Range last
})
Text Search Indexes
Full-text search capabilities
createIndex({ field: "text" })
Search
Enable full-text search with stemming, stop words, and scoring.
mongosh
> // Create text index on title and description
> db.posts.createIndex({
title: "text",
description: "text"
})
> // Search for posts containing "mongodb"
> db.posts.find({ $text: { $search: "mongodb" } })
> // Search with exact phrase
> db.posts.find({ $text: { $search: ""NoSQL database"" } })
> // Exclude words with minus
> db.posts.find({ $text: { $search: "mongodb -relational" } })
> // Get relevance score
> db.posts.find(
{ $text: { $search: "mongodb indexing" } },
{ score: { $meta: "textScore" } }
).sort({ score: { $meta: "textScore" } })
Weighted Text Index
Priority
Assign different importance to different fields.
mongosh
> // Title 3x more important than description
> db.posts.createIndex(
{ title: "text", description: "text" },
{ weights: { title: 3, description: 1 } }
)
> // Matches in title will score higher!
⚠️ Text Index Limitations
- Only ONE text index per collection
- Cannot be used with hint() for other queries
- Slower than regular indexes for exact matches
- For advanced search, consider Atlas Search
Geospatial Indexes
Location-based queries
2dsphere Index
GeoJSON
For Earth-like sphere coordinates using GeoJSON format.
mongosh
> // Create 2dsphere index
> db.places.createIndex({ location: "2dsphere" })
> // Insert location (GeoJSON format)
> db.places.insertOne({
name: "Central Park",
location: {
type: "Point",
coordinates: [-73.9654, 40.7829] // [longitude, latitude]
}
})
> // Find places near a point (within 1000 meters)
> db.places.find({
location: {
$near: {
$geometry: {
type: "Point",
coordinates: [-73.9712, 40.7831]
},
$maxDistance: 1000 // meters
}
}
})
> // Find within a polygon area
> db.places.find({
location: {
$geoWithin: {
$geometry: {
type: "Polygon",
coordinates: [[
[-74.0, 40.7],
[-73.9, 40.7],
[-73.9, 40.8],
[-74.0, 40.8],
[-74.0, 40.7] // Close polygon
]]
}
}
}
})
💡 GeoJSON Format
- Always
[longitude, latitude]- NOT latitude first! - Longitude range: -180 to 180
- Latitude range: -90 to 90
- Distances in meters by default
Indexing Strategies
Best practices and patterns
✅ When to Create Indexes
- Fields used frequently in queries
- Fields used in sorting
- Fields used for uniqueness constraints
- Fields in join operations ($lookup)
- High cardinality fields (many unique values)
❌ When NOT to Create Indexes
- Low cardinality fields (e.g., gender: M/F)
- Fields rarely queried
- Small collections (< 1000 docs)
- Write-heavy collections (indexes slow writes)
- Fields with frequent updates
Covered Queries
⚡ Ultra Fast
Query that can be satisfied entirely from the index without examining documents.
mongosh
> // Create index on email and name
> db.users.createIndex({ email: 1, name: 1 })
> // ✅ Covered query - only needs index!
> db.users.find(
{ email: "john@example.com" },
{ _id: 0, email: 1, name: 1 } // Must exclude _id!
).explain("executionStats")
{
executionStats: {
totalDocsExamined: 0, // 🚀 Zero documents examined!
totalKeysExamined: 1, // Only read from index
executionTimeMillis: 0 // Instant!
}
}
💡 Index Selectivity
High Selectivity (Good): Field with many unique values (email, username, SSN)
Low Selectivity (Bad): Field with few unique values (gender, boolean flags)
Example:
• email field: 10,000 unique values / 10,000 docs = 100% selectivity ✓
• gender field: 2 unique values / 10,000 docs = 0.02% selectivity ✗
• email field: 10,000 unique values / 10,000 docs = 100% selectivity ✓
• gender field: 2 unique values / 10,000 docs = 0.02% selectivity ✗
Performance Optimization
Analyze and improve query performance
explain()
Analysis
Analyze query execution and index usage.
mongosh
> // Get execution statistics
> db.users.find({ age: { $gte: 25 } })
.explain("executionStats")
{
executionStats: {
executionTimeMillis: 2,
totalKeysExamined: 187,
totalDocsExamined: 187,
nReturned: 187,
executionStages: {
stage: 'IXSCAN', // ✓ Using index
indexName: 'age_1',
direction: 'forward'
}
}
}
> // Key metrics to watch:
> // • IXSCAN = good, COLLSCAN = bad
> // • totalDocsExamined should be close to nReturned
> // • executionTimeMillis should be low
$indexStats
Monitoring
Monitor index usage in production.
mongosh
> // Get index usage statistics
> db.users.aggregate([{ $indexStats: {} }])
[
{
name: 'email_1',
key: { email: 1 },
host: 'mongodb-server:27017',
accesses: {
ops: 1547, // Times used
since: ISODate('2024-01-01...')
}
},
{
name: 'age_1_city_1',
key: { age: 1, city: 1 },
accesses: {
ops: 23, // Rarely used - consider dropping?
since: ISODate('2024-01-01...')
}
}
]
> // Drop unused indexes to improve write performance
✅ 10-Point Optimization Checklist
- Index frequently queried fields - Speed up reads
- Use compound indexes wisely - Follow ESR rule
- Create covered queries - Read from index only
- Monitor with explain() - Watch for COLLSCAN
- Drop unused indexes - Improve write speed
- Avoid indexes on low-selectivity fields - Waste of space
- Use partial indexes - Index subset of documents
- Background index builds - Don't block writes
- Regular index maintenance - Rebuild fragmented indexes
- Test before production - Always verify performance