Database Indexing Strategies (Single, Compound, TTL) & Query Optimization0%

Database Indexing Strategies (Single, Compound, TTL) & Query Optimization

Beginner12 min read•Updated: Jul 11, 2026
Study MaterialsCheatsheet

Database Indexing Strategies (Single, Compound, TTL) & Query Optimization

Without indexes, MongoDB must inspect every single document in a collection to satisfy a query—an expensive operation known as a Collection Scan (COLLSCAN). Proper Indexing Strategies turn sluggish multi-second queries into sub-millisecond Index Scans (IXSCAN).

1. Types of MongoDB Indexes

  1. 1
    Single Field Index: Index on a single attribute (db.users.createIndex({ email: 1 })).
  2. 2
    Compound Index: Index on multiple fields (db.courses.createIndex({ category: 1, price: -1 })).
  3. 3
    Unique Index: Guarantees no two documents share the same value ({ unique: true }).
  4. 4
    TTL (Time-To-Live) Index: Automatically deletes documents after a designated lifespan!
  5. 5
    Text Index: Powers full-text keyword searches across text fields.

2. The ESR Rule for Compound Indexes

When designing compound indexes for complex queries containing Equality, Sorting, and Range conditions, follow the ESR (Equality, Sort, Range) Rule:

  1. 1
    E - Equality: Fields queried with exact matches (status: 'ACTIVE') go first.
  2. 2
    S - Sort: Fields used for ordering (sort: { createdAt: -1 }) go second.
  3. 3
    R - Range: Fields queried with range operators (price: { $gte: 100 }) go last.
JavaScript
// Query:
db.orders.find({ status: 'COMPLETED', totalAmount: { $gte: 500 } }).sort({ orderDate: -1 });
 
// Optimal Index following ESR:
db.orders.createIndex({
status: 1, // 1. Equality
orderDate: -1, // 2. Sort
totalAmount: 1 // 3. Range
});

3. TTL (Time-To-Live) Indexes for Auto-Expiring Data

Use TTL indexes to automatically purge temporary verification OTPs, login tokens, or shopping carts:

JavaScript
const tokenSchema = new Schema({
userId: Schema.Types.ObjectId,
token: String,
createdAt: {
type: Date,
default: Date.now,
expires: 3600 // Automatically deleted by MongoDB background thread after 1 hour (3600s)!
}
});

4. Query Profiling with explain("executionStats")

To verify if your query is using an index, run .explain("executionStats"):

JavaScript
const stats = await Course.find({ slug: 'full-stack-mern-mastery' })
.explain('executionStats');
 
console.log('Stage:', stats.executionStats.executionStages.stage);
console.log('Docs Examined:', stats.executionStats.totalDocsExamined);
console.log('Keys Examined:', stats.executionStats.totalKeysExamined);
  • COLLSCAN: Full collection scan (Bad! Means missing index).
  • IXSCAN: Index scan (Good! Direct B-Tree traversal).
Interactive Quiz

Multiple Choice Questions

Question 1
What does the stage COLLSCAN in a MongoDB query explain plan signify?
Question 2
What is the ESR Rule for designing optimal compound indexes in MongoDB?
Question 3
What does a TTL (Time-To-Live) index do in MongoDB?
Question 4
Why should you NOT indiscriminately create dozens of indexes on every single field of a collection?
Question 5
What does the stage IXSCAN followed by a FETCH indicate in an explain plan?

Related Lessons

Previous LessonNext Lesson
Multi-Table Joins with $lookup & Array Flattening with $unwindNone

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous
PrevNext