Index Types and Advanced Features

Advanced Indexing Features—Master TTL, partial, and ESR rules to build a production-grade indexing system.

1. What You'll Learn

• Unique Index (unique)
• Sparse index
• TTL Index (Automatic Expiration)
• partial: partial index
• ESR Rule (Equality/Sort/Range)
• Best Practices for Index Design

graph TB
    A[Index Types] --> B[unique<br/>Unique Index]
    A --> C[sparse<br/>Sparse Index]
    A --> D[TTL<br/>Automatic Expiration]
    A --> E[partial<br/>Selected Indexes]
    A --> F[compound<br/>Composite Index]

    B --> B1[Ensure Uniqueness<br/>Repeated Throw Errors]
    C --> C1[Skip null<br/>Space-saving]
    D --> D1[Automatic Deletion<br/>Scheduled Cleanup]
    E --> E1[Indexed subset only<br/>Performance + Savings]

    style D fill:#d4edda
    style E fill:#d4edda

graph TB
    A[Index Types] --> B[unique<br/>Unique Index]
    A --> C[sparse<br/>Sparse Index]
    A --> D[TTL<br/>Automatic Expiration]
    A --> E[partial<br/>Selected Indexes]
    A --> F[compound<br/>Composite Index]

    B --> B1[Ensure Uniqueness<br/>Repeated Throw Errors]
    C --> C1[Skip null<br/>Space-saving]
    D --> D1[Automatic Deletion<br/>Scheduled Cleanup]
    E --> E1[Indexed subset only<br/>Performance + Savings]

    style D fill:#d4edda
    style E fill:#d4edda

2. Unique Index (unique)

Concept Explanation: A unique index ensures that the values in the indexed field are unique across the entire collection, providing database-level assurance of data integrity. Unlike application-level validation, a unique index is enforced by the database engine—duplicate values are rejected regardless of which client writes them.

How It Works: A unique index enforces uniqueness constraints within a B+ tree. Each time an insert or update occurs, the engine first checks whether a key with the same value already exists in the index; if so, it throws a E11000 duplicate key error exception. A composite unique index requires that the combination of fields be unique (individual fields may be duplicated).

Use Cases:

• User email address, mobile phone number (globally unique identifier)
• SKU Number (Unique Product Identifier)
• Composite uniqueness: For example, (userId, productId) ensures that each user can rate the same product only once.
• Not suitable: Fields that allow duplicate values (such as category, status)

// === Create a unique index ===
db.users.createIndex({ email: 1 }, { unique: true });
// email Field values must be unique.

// === Composite Unique Index ===
db.products.createIndex({ sku: 1, variant: 1 }, { unique: true });
// (sku, variant) The combination must be unique.

// === Unique Index + Selected Fields ===
db.users.createIndex(
  { email: 1 },
  { unique: true, partialFilterExpression: { email: { $exists: true } } }
);
// Only for existing email Apply a unique constraint to the field's documentation

Key Points Analysis:

1. A unique index allows the value null to exist, but there can be only one null in the entire set (null is considered the same value).
2. Using partialFilterExpression allows the unique constraint to apply only to some documents, resolving the null uniqueness issue.
3. Before creating a unique index, the collection must not contain any duplicate values; otherwise, creation will fail.

3. Sparse Indexes

Concept Explanation: A sparse index only indexes documents that have the indexed field and where that field is not null, skipping documents where the field is missing or null. A regular index indexes all documents (treating missing fields as null), while a sparse index saves space by excluding invalid entries.

How It Works: When creating a sparse index, the engine checks whether the indexed field exists and is not null after inserting a document; it inserts an index entry into the B+Tree only if these conditions are met. During a query, if a sparse index is used, the results will not include documents with missing fields (because they are not in the index).

Use Cases:

• Optional fields (such as discount and middleName); this field is missing from most documents
• Unique index + sparse = allows multiple documents to lack this field while still maintaining the uniqueness of existing values
• Not suitable: Queries that require documents with missing fields (sparse indexes cannot cover these documents)

graph LR
    subgraph "Collection of Documents"
        D1["{sku:'A', discount:10}"]
        D2["{sku:'B'}"]
        D3["{sku:'C', discount:null}"]
        D4["{sku:'D', discount:20}"]
    end

    subgraph "Regular Index"
        I1["A→D1, B→D2, C→D3, D→D4"]
    end

    subgraph "Sparse Index"
        I2["10→D1, 20→D4<br/>(only 2 entries)"]
    end

    style I2 fill:#d4edda

graph LR
    subgraph "Collection of Documents"
        D1["{sku:'A', discount:10}"]
        D2["{sku:'B'}"]
        D3["{sku:'C', discount:null}"]
        D4["{sku:'D', discount:20}"]
    end

    subgraph "Regular Index"
        I1["A→D1, B→D2, C→D3, D→D4"]
    end

    subgraph "Sparse Index"
        I2["10→D1, 20→D4<br/>(only 2 entries)"]
    end

    style I2 fill:#d4edda

// === Sparse Index：Skip null Field ===
db.products.createIndex({ discount: 1 }, { sparse: true });
// Index only discount Field Documentation

// === Sparse Index vs Regular Index ===
// Regular Index：All documents are indexed（Including null）
// Sparse Index：Only non- null Indexing Documents

Key Points Analysis:

1. Sparse indexes do not cover documents with missing fields; find({discount: null}) will not return documents that are missing that field.
2. Sparse + Unique Combination: Allows multiple documents to lack this field while ensuring that existing values are unique
3. Partial indexes are a superset of sparse indexes (and more flexible); it is recommended to use partial indexes first.

4. TTL Indexes (Automatic Expiration)

Concept Explanation: The TTL (Time-To-Live) index is MongoDB’s automatic expiration and deletion mechanism, which automatically deletes documents that have exceeded a specified time period based on a date field. It requires no manual cleanup or scheduled tasks; the database engine handles this automatically in the background—making it a powerful tool for session management and log cleanup.

How It Works: The TTL index adds a background cleanup thread to the B+ tree. This thread runs every 60 seconds, scans the date fields in the index, calculates currentTime - fieldValue > expireAfterSeconds, and deletes all expired documents. The deletion operation itself incurs write overhead, and a large number of expired documents can cause a sudden surge in write pressure.

sequenceDiagram
    participant App as Applications
    participant TTL as TTLBackground Threads
    participant IX as TTLIndexB+Tree
    participant DOC as Collection of Documents

    App->>DOC: Insert {token:'abc', createdAt: T1}
    DOC->>IX: Index Entries createdAt=T1

    Note over TTL: Every 60 seconds

    loop Every 60 seconds
        TTL->>IX: Scan createdAt values
        IX-->>TTL: Back to All Entries
        TTL->>TTL: Calculate the current time - createdAt
        alt Expired ( > expireAfterSeconds )
            TTL->>DOC: Delete Expired Documents
            DOC->>IX: Delete the corresponding index entry
        else Not expired
            Note over TTL: Skip
        end
    end

sequenceDiagram
    participant App as Applications
    participant TTL as TTLBackground Threads
    participant IX as TTLIndexB+Tree
    participant DOC as Collection of Documents

    App->>DOC: Insert {token:'abc', createdAt: T1}
    DOC->>IX: Index Entries createdAt=T1

    Note over TTL: Every 60 seconds

    loop Every 60 seconds
        TTL->>IX: Scan createdAt values
        IX-->>TTL: Back to All Entries
        TTL->>TTL: Calculate the current time - createdAt
        alt Expired ( > expireAfterSeconds )
            TTL->>DOC: Delete Expired Documents
            DOC->>IX: Delete the corresponding index entry
        else Not expired
            Note over TTL: Skip
        end
    end

Use Cases:

⚠️ TTL Limit:

// === Create TTL Index（30 Automatically Deleted by the Queen）===
db.sessions.createIndex(
  { createdAt: 1 },
  { expireAfterSeconds: 30 * 24 * 60 * 60 }
);

// === Edit TTL ===
db.runCommand({
  collMod: 'sessions',
  index: { keyPattern: { createdAt: 1 }, expireAfterSeconds: 7 * 24 * 60 * 60 }
});

// === Cancel TTL（Set as false）===
db.runCommand({
  collMod: 'sessions',
  index: { keyPattern: { createdAt: 1 }, expireAfterSeconds: -1 }
});

Typical Scenarios:

• User session (expires after 30 minutes)
• Verification code (expires in 10 minutes)
• Log data (retained for 90 days)
• Temporary token (expires in 1 hour)

5. Partial Indexes

Concept Explanation: A partial index indexes only those documents that meet the filter criteria; it is an enhanced version of a sparse index. By using partialFilterExpression to specify which documents are included in the index, it both saves space and improves index accuracy, making it one of the most recommended index optimization methods for production environments.

How It Works: When creating a partial index, the engine inserts only those documents that satisfy partialFilterExpression into the B+Tree. During a query, the optimizer will select this index only if the query conditions "cover" the partialFilterExpression (i.e., the query conditions are a subset of or equivalent to the filter conditions); otherwise, it will not be used.

Use Cases:

• Index only "Items for Sale" (isActive: true, stock: {$gt: 0}), skipping discontinued and out-of-stock items
• Index only "verified users" (emailVerified: true), and skip unverified users
• Unique constraints apply only to certain documents (e.g., published article titles must be unique, while duplicate titles are allowed in drafts)

graph TB
    subgraph "Gathering（10000Document）"
        A[All Documents<br/>10000 items]
    end

    subgraph "Regular Index"
        B[Index Entries<br/>10000 items<br/>~10MB]
    end

    subgraph "PartialIndex<br/>isActive=true, stock>0"
        C[Index Entries<br/>3000 items<br/>~3MB]
    end

    A --> B
    A --> C

    style C fill:#d4edda

graph TB
    subgraph "Gathering（10000Document）"
        A[All Documents<br/>10000 items]
    end

    subgraph "Regular Index"
        B[Index Entries<br/>10000 items<br/>~10MB]
    end

    subgraph "PartialIndex<br/>isActive=true, stock>0"
        C[Index Entries<br/>3000 items<br/>~3MB]
    end

    A --> B
    A --> C

    style C fill:#d4edda

// === Selected Indexes：Index only documents that meet the criteria ===
db.products.createIndex(
  { category: 1, price: 1 },
  {
    partialFilterExpression: {
      isActive: true,
      stock: { $gt: 0 }
    }
  }
);
// Index only products currently for sale（isActive=true, stock>0）

// === Space-Saving Indexes ===
// Regular Index：1 Index all documents
// Partial Index: Only index 3000 products currently for sale (Saves 70% space)

▶ Example 1: Partial Indexing in Practice

// ShopHub：Index only products that are for sale and in stock，Savings 70% Index Space
db.products.insertMany([
  { sku: 'A001', category: 'Electronics', price: 599, isActive: true, stock: 50 },
  { sku: 'A002', category: 'Electronics', price: 299, isActive: false, stock: 0 },
  { sku: 'A003', category: 'Books', price: 29, isActive: true, stock: 100 },
  { sku: 'A004', category: 'Books', price: 49, isActive: true, stock: 0 }
]);

// Partial Index only isActive=true AND stock>0 the document（A001, A003）
db.products.createIndex(
  { category: 1, price: 1 },
  { partialFilterExpression: { isActive: true, stock: { $gt: 0 } } }
);

// The query must include filter Conditions for a hit
db.products.find({
  category: 'Electronics',
  isActive: true,
  stock: { $gt: 0 },
  price: { $gte: 100 }
}).explain();
// winningPlan.stage: IXSCAN ✅

// Missing filter Conditions → Missed
db.products.find({ category: 'Electronics', price: { $gte: 100 } }).explain();
// winningPlan.stage: COLLSCAN（Do not use partial Index）

6. ESR Rules

Equality → Sort → Range: The golden rule for the order of fields in a composite index.

Concept Explanation: The ESR rule is the most important rule in MongoDB index design. It specifies the optimal order of fields in a composite index: equality conditions come first, sort fields in the middle, and range conditions last. Violating the ESR rule can result in a significant decrease in index efficiency or even render the index ineffective.

How It Works:

• Equality fields are sorted first: equality conditions can quickly narrow the search space from N to K (K << N), providing the most precise starting point for subsequent fields
• Sort field centered: Since indexes are inherently ordered, if the sort field follows immediately after the equality field, the query results are already sorted in index order, eliminating the need for in-memory sorting (avoiding the SORT stage).
• Place the Range field last: Range queries scan a contiguous index range, and subsequent fields within that range cannot take advantage of the index’s sorted order; therefore, the range field must be placed last.

graph TB
    subgraph "ESR Index {status, createdAt, price}"
        A["Equality: status='paid'<br/>→ Locate all paid Document"] --> B["Sort: createdAt: -1<br/>→ The index is sorted，Memory-Free Sorting"]
        B --> C["Range: price >= 100<br/>→ Range Scan，Interrupt Subsequent Fields"]
    end

    subgraph "Counterexample：RSE Index {price, status, createdAt}"
        D["Range: price >= 100<br/>→ Scan a large number of index entries"] --> E["Sort: status<br/>→ Memory-based sorting required"]
        E --> F["Equality: createdAt<br/>→ The index can no longer be used"]
    end

    style A fill:#d4edda
    style B fill:#cce5ff
    style C fill:#fff3cd
    style D fill:#f8d7da
    style E fill:#f8d7da
    style F fill:#f8d7da

graph TB
    subgraph "ESR Index {status, createdAt, price}"
        A["Equality: status='paid'<br/>→ Locate all paid Document"] --> B["Sort: createdAt: -1<br/>→ The index is sorted，Memory-Free Sorting"]
        B --> C["Range: price >= 100<br/>→ Range Scan，Interrupt Subsequent Fields"]
    end

    subgraph "Counterexample：RSE Index {price, status, createdAt}"
        D["Range: price >= 100<br/>→ Scan a large number of index entries"] --> E["Sort: status<br/>→ Memory-based sorting required"]
        E --> F["Equality: createdAt<br/>→ The index can no longer be used"]
    end

    style A fill:#d4edda
    style B fill:#cce5ff
    style C fill:#fff3cd
    style D fill:#f8d7da
    style E fill:#f8d7da
    style F fill:#f8d7da

ESR Rule Comparison Table:

Consequences of Violating the ESR:

// === Search：Equivalence Filtering + Sort + Scope ===
db.products.find({
  category: 'Electronics',          // Equality
  isActive: true                    // Equality
}).sort({ createdAt: -1 })          // Sort
  .limit(20);

// Price Range
db.products.find({
  category: 'Electronics',
  createdAt: { $gte: new Date('2026-01-01') }  // Range
}).sort({ rating: -1 });

// === Best Index ===
db.products.createIndex({
  category: 1,         // E - Equivalent
  isActive: 1,         // E - Equivalent
  createdAt: -1,       // S - Sort（Index Direction Matching）
  rating: -1           // R - Scope（Save it for last）
});

▶ Example 2: A Practical Comparison of ESR Rules

// TechCorp Order System：Comparison ESR Correct vs Execution Plan with Incorrect Ordering
db.orders.insertMany([
  { userId: 'user_001', status: 'paid', createdAt: new Date('2026-07-01'), total: 100 },
  { userId: 'user_002', status: 'pending', createdAt: new Date('2026-07-02'), total: 200 },
  { userId: 'user_001', status: 'paid', createdAt: new Date('2026-07-03'), total: 50 }
]);

// ❌ Index Errors: Range before Sort
db.orders.createIndex({ userId: 1, total: 1, status: 1, createdAt: -1 }, { name: 'idx_wrong' });
db.orders.find({ userId: 'user_001', status: 'paid' })
  .sort({ createdAt: -1 }).explain('executionStats');
// appear SORT stage（Memory Sorting），totalKeysExamined much greater than nReturned

// ✅ Correct Indexing：ESR Order
db.orders.dropIndex('idx_wrong');
db.orders.createIndex({ userId: 1, status: 1, createdAt: -1, total: 1 }, { name: 'idx_esr' });
db.orders.find({ userId: 'user_001', status: 'paid' })
  .sort({ createdAt: -1 }).explain('executionStats');
// No SORT stage, totalKeysExamined ≈ nReturned

7. Best Practices for Index Design

Concept Explanation: Index design is not an isolated technical decision, but rather a comprehensive trade-off based on query patterns, data characteristics, and business requirements. Good index design can make queries fly, while poor design wastes space and slows down writes.

Design Principles:

1. Query-Based Design—First analyze explain() and the slow query log, then create indexes
2. ESR Priority—The order of fields in a composite index follows Equality → Sort → Range
3. Prioritize high selectivity—Fields with many unique values (such as userId) are more suitable for indexing than fields with low selectivity (such as isActive)
4. Index Coverage—Consider index coverage for high-frequency queries to avoid table lookups
5. Regular Cleanup—Use $indexStats to find and delete unused indexes

graph TB
    A[Analyzing Query Patterns<br/>Slow Query Log] --> B[Identifying High-Frequency Queries]
    B --> C{Query Type?}
    C -->|Equal Value Query| D[Single field/Unique Index]
    C -->|Multiple Condition Combinations| E[Composite Index ESR]
    C -->|Sort+Filter| F[ESR Composite Index]
    C -->|Show only a few fields| G[Index Coverage]
    B --> H[Evaluating Options]
    H -->|High selectivity| I[✅ Create an index]
    H -->|Low selectivity| J[❌ Do not build / use partial]
    I --> K[Create+Verification<br/>explain]
    K --> L[Monitoring Utilization<br/>$indexStats]
    L --> M{Low utilization rate?}
    M -->|Yes| N[Delete Index]
    M -->|No| O[Retain]

    style D fill:#d4edda
    style E fill:#d4edda
    style F fill:#d4edda
    style G fill:#d4edda

graph TB
    A[Analyzing Query Patterns<br/>Slow Query Log] --> B[Identifying High-Frequency Queries]
    B --> C{Query Type?}
    C -->|Equal Value Query| D[Single field/Unique Index]
    C -->|Multiple Condition Combinations| E[Composite Index ESR]
    C -->|Sort+Filter| F[ESR Composite Index]
    C -->|Show only a few fields| G[Index Coverage]
    B --> H[Evaluating Options]
    H -->|High selectivity| I[✅ Create an index]
    H -->|Low selectivity| J[❌ Do not build / use partial]
    I --> K[Create+Verification<br/>explain]
    K --> L[Monitoring Utilization<br/>$indexStats]
    L --> M{Low utilization rate?}
    M -->|Yes| N[Delete Index]
    M -->|No| O[Retain]

    style D fill:#d4edda
    style E fill:#d4edda
    style F fill:#d4edda
    style G fill:#d4edda

(1) Recommended Approach

// ✅ Recommendations 1：Single-Field Indexes Cover High-Frequency Queries
db.products.createIndex({ sku: 1 });

// ✅ Recommendations 2：Composite indexes follow ESR Principles
db.orders.createIndex({ userId: 1, status: 1, createdAt: -1 });

// ✅ Recommendations 3：TTL Automatic Cleanup
db.logs.createIndex({ createdAt: 1 }, { expireAfterSeconds: 30 * 86400 });

// ✅ Recommendations 4：Space-Saving Indexes
db.products.createIndex(
  { category: 1 },
  { partialFilterExpression: { isActive: true } }
);

// ✅ Recommendations 5：Unique indexes ensure data integrity
db.users.createIndex({ email: 1 }, { unique: true });

(2) Anti-patterns

Common Anti-Patterns and Their Consequences:

// ❌ Anti-pattern 1：Indexing Fields with Low Selectivity
db.users.createIndex({ isActive: 1 });
// isActive Only true/false Two values，Low index efficiency

// ❌ Anti-pattern 2：Excessive Indexing
// A set containing more than 20 Index，Write performance has dropped significantly

// ❌ Anti-pattern 3：Incorrect Order of Fields in a Composite Index
db.products.createIndex({ price: 1, category: 1 });
// Wrong: price should be placed after category

// ❌ Anti-pattern 4：Unnecessary Indexes
db.users.createIndex({ lastLoginAt: 1 });
// If you rarely press lastLoginAt Search，Index Waste

8. Index Performance Monitoring

Concept Explanation: Index monitoring is an essential part of operations in a production environment. By analyzing slow query logs and index usage statistics, you can identify unused indexes (which waste space), inefficient indexes (that need optimization), and missing indexes (that need to be created).

How It Works: MongoDB’s built-in Profiler logs slow queries, while the $indexStats aggregation pipeline tracks the number of times each index is used and the time spent on each query. Combined, these provide a comprehensive view of index health.

Monitoring Metrics:

// === Slow Query Analysis ===
db.setProfilingLevel(2, { slowms: 100 });
// Records exceeding 100ms Queries

// === View Slow Queries ===
db.system.profile.find({ millis: { $gt: 100 } })
  .sort({ ts: -1 })
  .limit(10);

// === View Index Usage Statistics ===
db.products.aggregate([
  { $indexStats: {} }
]);
// Find unused index

// === Delete Unused Indexes ===
db.products.dropIndex('unused_index_name');

Key Points Analysis:

1. Profiling Level 0 = Off, 1 = Slow queries only, 2 = All records (Level 2 affects performance and is for debugging purposes only)
2. If accesses.ops for $indexStats is 0, it means it has never been used since MongoDB was started.
3. Recommended production environment: Level 1 + slowms: 100, to balance monitoring granularity and performance

▶ Example: TTL Index + Partial Index + ESR in Practice

// Scene 1：TTL Automatically Clear Session Indexes（30 Expires in minutes）
db.sessions.insertMany([
  { userId: 'user_001', token: 'abc', createdAt: new Date() },
  { userId: 'user_002', token: 'xyz', createdAt: new Date(Date.now() - 31 * 60 * 1000) }  // Expired
]);

db.sessions.createIndex(
  { createdAt: 1 },
  { expireAfterSeconds: 30 * 60 }  // 30 minutes
);

// Waiting 60 seconds later，Expired documents are automatically deleted
// db.sessions.find() → Only user_001 The conversation

// Scene 2：Selected Indexes - Index only products currently for sale（Savings 70% Space）
db.products.createIndex(
  { category: 1, price: 1 },
  {
    partialFilterExpression: {
      isActive: true,
      stock: { $gt: 0 }
    }
  }
);

// Some indexes are only used when the query contains filter Use when conditions apply
db.products.find({
  category: 'Electronics',
  isActive: true,    // Must match partialFilterExpression
  stock: { $gt: 0 }, // Must match partialFilterExpression
  price: { $gte: 100, $lte: 500 }
}).explain();
// winningPlan.inputStage.stage: IXSCAN（Partial indexing was used）

// Scene 3：ESR Rules in Practice - Order Inquiry
db.orders.insertMany([
  { userId: 'user_001', status: 'paid', createdAt: new Date('2026-07-01'), total: 100 },
  { userId: 'user_001', status: 'paid', createdAt: new Date('2026-07-02'), total: 200 },
  { userId: 'user_002', status: 'pending', createdAt: new Date('2026-07-03'), total: 50 }
]);

// ESR Best Index：Equality → Sort → Range
db.orders.createIndex({
  userId: 1,        // E - Equivalence Filtering
  status: 1,        // E - Equivalence Filtering
  createdAt: -1,    // S - Sort（Directional Matching）
  total: 1          // R - Range Query（Save it for last）
});

// Efficient Queries：Orders Paid by the User，In reverse chronological order，Price Range
db.orders.find({
  userId: 'user_001',                              // E
  status: 'paid',                                  // E
  total: { $gte: 50, $lte: 300 }                   // R
}).sort({ createdAt: -1 }).limit(20)               // S
.explain('executionStats');

// Output：Use composite indexes exclusively，No additional sorting required
// totalKeysExamined: 2, totalDocsExamined: 2, nReturned: 2

Output: TTL automatically cleans up expired sessions; some indexes save space; ESR composite indexes optimize query performance.

❓ FAQ

📖 Summary

• Unique index: Ensures that field values are unique
• Sparse index: Skips null fields
• TTL Index: Automatic expiration and deletion
• Partial indexing: Only documents that meet the criteria are indexed
• ESR Rule: Equality → Sort → Range
• Index Design: Create indexes for high-frequency query fields; exercise caution when creating indexes for low-selectivity fields; and ensure composite indexes follow the ESR principle.

📝 Exercises

1. Basic Question (⭐): Create a unique index on the email column for the users collection.
2. Basic Question (⭐): Create a TTL index for the sessions collection (expires in 30 minutes).
3. Advanced Exercise (⭐⭐): Create a partial index for the products collection (only for products currently for sale).
4. Advanced Problem (⭐⭐): Design an optimal index for the orders collection using the ESR rule.
5. Challenge (⭐⭐⭐): Design a complete e-commerce indexing scheme (10+ indexes), analyze usage with $indexStats, and delete unused indexes.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us