Indexing Fundamentals and Principles

Indexes are key to database performance—mastering index principles and optimization can increase query speed by a factor of 1,000.

1. What You'll Learn

• Indexing Principles (B-Tree Data Structure)
• createIndex() Syntax
• Single-Column Indexes and Composite Indexes
• Interpreting the explain() Execution Plan
• Index coverage (covered query)
• Index Costs and Trade-offs

2. Indexing Principles (B-Tree)

Concept Explanation: An index is an auxiliary data structure, similar to a book’s table of contents, that allows a database to quickly locate a document without having to scan the entire collection. MongoDB uses a B-tree-based index structure (the WiredTiger engine uses a B+tree variant) to establish an ordered mapping between field values and document locations, reducing the time complexity of queries from O(N) to O(log N).

How It Works: MongoDB’s B+Tree indexes store field values in internal nodes for routing, and store actual key values and document pointers in leaf nodes. The leaf nodes are connected via a doubly linked list, which naturally supports range queries and sorting. When a query matches an index, the engine compares key values layer by layer starting from the root node, ultimately locating the physical position of the target document in a leaf node, thereby avoiding a full collection scan (COLLSCAN).

Use Cases:

• Fields frequently used as query criteria (such as sku and userId)
• Sort field (e.g., createdAt: -1)
• Fields that require a unique constraint (e.g., email)
• Not suitable for: Fields with low selectivity (e.g., isActive, which only accepts true or false values), and fields that are written to very frequently but queried very rarely

graph TB
    A[Root Node<br/>50-100] --> B[Internal Node 1<br/>20-50]
    A --> C[Internal Node 2<br/>20-50]
    B --> D[Leaf Node 1<br/>Link to the document]
    B --> E[Leaf Node 2]
    C --> F[Leaf Node 3]
    C --> G[Leaf Node 4]
    D <--> E <--> F <--> G

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

graph TB
    A[Root Node<br/>50-100] --> B[Internal Node 1<br/>20-50]
    A --> C[Internal Node 2<br/>20-50]
    B --> D[Leaf Node 1<br/>Link to the document]
    B --> E[Leaf Node 2]
    C --> F[Leaf Node 3]
    C --> G[Leaf Node 4]
    D <--> E <--> F <--> G

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

B+Tree Query Process: For an equality query, comparisons are made layer by layer starting from the root node → until a leaf node is reached → then the document pointer is returned; for a range query, after locating the starting leaf node, the linked list is scanned sequentially → and all documents that meet the criteria are collected.

sequenceDiagram
    participant App as Application Inquiry
    participant WT as WiredTigerEngine
    participant IX as B+TreeIndex
    participant DOC as Collection of Documents

    App->>WT: find({sku: 'SKU-005'})
    WT->>IX: Root Node Comparison 50<005<100 → Zuo Zishu
    IX-->>WT: Internal Node: 20<005<50 → Left lobe
    WT->>IX: Leaf Node Search SKU-005
    IX-->>WT: Found → doc_pointer=0x7F3A
    WT->>DOC: Read 0x7F3A Location Documentation
    DOC-->>App: Return matching documents

    Note over WT,IX: Time Complexity O(log N)<br/>No need to scan all the documents

sequenceDiagram
    participant App as Application Inquiry
    participant WT as WiredTigerEngine
    participant IX as B+TreeIndex
    participant DOC as Collection of Documents

    App->>WT: find({sku: 'SKU-005'})
    WT->>IX: Root Node Comparison 50<005<100 → Zuo Zishu
    IX-->>WT: Internal Node: 20<005<50 → Left lobe
    WT->>IX: Leaf Node Search SKU-005
    IX-->>WT: Found → doc_pointer=0x7F3A
    WT->>DOC: Read 0x7F3A Location Documentation
    DOC-->>App: Return matching documents

    Note over WT,IX: Time Complexity O(log N)<br/>No need to scan all the documents

(1) Details on WiredTiger Index Storage

3. createIndex Syntax

Concept Explanation: createIndex() is the core command for creating indexes in MongoDB; it instructs the engine to build a B+Tree index structure for the specified field. Once the index is created, the query optimizer automatically determines whether to use it—developers do not need to modify the query statement.

How It Works: When creating an index, MongoDB scans all documents in the collection, extracts the values of the indexed fields, sorts them, and builds a B+Tree structure that is written to disk. During this process, a write lock is held on the collection (indexes can be built in the background background: true), and creating an index on a large collection may take anywhere from a few minutes to several hours.

Grammar Rules:

// === Create a single-field index ===
db.products.createIndex({ sku: 1 });          // Ascending
db.products.createIndex({ createdAt: -1 });   // Descending

// === Create a composite index ===
db.products.createIndex({ category: 1, price: -1 });

// === Create a unique index ===
db.products.createIndex({ sku: 1 }, { unique: true });

// === Create in the Backend(Non-blocking)===
db.products.createIndex({ tags: 1 }, { background: true });

// === Custom Index Name ===
db.products.createIndex({ title: 1 }, { name: 'idx_title' });

Key Points Analysis:

1. 1 and -1 affect the sort order of indexes; they have no practical impact on single-field indexes but are crucial for sort optimization in composite indexes.
2. background: true In MongoDB 4.2 and later, this is built by default in the background; the parameters are retained but no longer need to be explicitly specified.
3. Each collection has a default unique index named _id (which cannot be deleted); there is no need to create an additional one.

▶ Example 1: createIndex and Index Creation Monitoring

// ShopHub E-commerce:Create a key index for the product collection，Monitoring Build Progress
db.products.createIndex({ category: 1, price: -1, rating: -1 }, { name: 'idx_category_price_rating', background: true });

// View Index Build Progress
db.currentOp({
  $or: [
    { op: 'command', 'command.createIndexes': { $exists: true } },
    { op: 'none', ns: /shopdb\.products/ }
  ]
});

// View All Indexes
db.products.getIndexes();
// [
//   { v: 2, key: { _id: 1 }, name: '_id_' },
//   { v: 2, key: { category: 1, price: -1, rating: -1 }, name: 'idx_category_price_rating' }
// ]

4. explain() Execution Plan

Concept Explanation: explain() is a query analysis tool for MongoDB that returns the execution plan selected by the query optimizer, including key metrics such as whether an index was used, how many documents were scanned, and how long the operation took. It serves as an “X-ray” for index optimization—run explain() first, then optimize.

How It Works: The MongoDB query optimizer generates multiple candidate plans for each query, executes them, compares their performance, and caches the optimal plan. explain() Outputs three levels:

• queryPlanner: Plan selected by the optimizer (not executed)
• executionStats: Actual execution statistics (requires the 'executionStats' parameter)
• allPlansExecution: Execution statistics for all candidate plans

graph LR
    A[Query Request] --> B[Query Optimizer]
    B --> C[Generate Candidate Plans]
    C --> D[Plan A: IXSCAN]
    C --> E[Plan B: COLLSCAN]
    D --> F[Execution Comparison]
    E --> F
    F --> G[Selecting the Optimal Plan]
    G --> H[Cache + Execute]

    style G fill:#d4edda
    style E fill:#f8d7da

graph LR
    A[Query Request] --> B[Query Optimizer]
    B --> C[Generate Candidate Plans]
    C --> D[Plan A: IXSCAN]
    C --> E[Plan B: COLLSCAN]
    D --> F[Execution Comparison]
    E --> F
    F --> G[Selecting the Optimal Plan]
    G --> H[Cache + Execute]

    style G fill:#d4edda
    style E fill:#f8d7da

Use Cases:

• If a query is slow, use explain() to check whether it used an index
• Before deploying a new index, verify whether queries return results
• Comparing Performance Differences Among Different Indexing Schemes
• Found that COLLSCAN (full-table scan) needs to be optimized

// === View the query execution plan ===
db.products.find({ category: 'Electronics' }).explain('executionStats');

// === Key Metrics ===
{
  queryPlanner: {
    winningPlan: {
      stage: 'IXSCAN',           // Index Scan(✅)
      // stage: 'COLLSCAN',      // Exhaustive Scan(❌)
      inputStage: {
        stage: 'IXSCAN',
        indexName: 'category_1'
      }
    }
  },
  executionStats: {
    totalDocsExamined: 250,      // Number of scanned documents
    totalKeysExamined: 250,      // Number of Scan Index Keys
    nReturned: 250,              // Number of documents returned
    executionTimeMillis: 5,      // Execution Time(milliseconds)
    totalQueryPlanExecutionTime: 7
  }
}

(1) Interpretation of Key Indicators

Comparison of the Three explain Modes:**

▶ Example 2: Using explain() to Diagnose Slow Queries

// TechCorp: The system has detected that order lookups are becoming slower. Use explain to diagnose
// Before the Index:COLLSCAN
db.orders.find({ status: 'paid', total: { $gte: 100 } }).explain('executionStats');
// stage: 'COLLSCAN', totalDocsExamined: 100000, executionTimeMillis: 450

// Create a composite index
db.orders.createIndex({ status: 1, total: -1 });

// After indexing:IXSCAN
db.orders.find({ status: 'paid', total: { $gte: 100 } }).explain('executionStats');
// stage: 'IXSCAN', indexName: 'status_1_total_-1'
// totalDocsExamined: 5000, nReturned: 5000, executionTimeMillis: 8

// Efficiency Assessment: examined/returned = 1.0 (Optimal Value), Performance improved 56x

5. Composite Indexes and the Leftmost Prefix

Concept Explanation: A composite index is a single index created across multiple fields (e.g., { category: 1, price: -1, rating: 1 }). It is more efficient than multiple single-field indexes because a single index lookup can satisfy multiple query conditions simultaneously. The "leftmost prefix" principle is the core rule of composite indexes—the index is only effective when the fields are used consecutively starting from the leftmost field.

How It Works: A composite index builds a B+ tree based on field order. It first sorts by the first field; if the first field values are the same, it sorts by the second field, and so on. During a query, matching must start from the leftmost field; skipping any prefix fields will render the subsequent fields in the index ineffective—just as you must first determine the first letter when looking up a word in a dictionary.

graph TB
    subgraph "Composite Index {category, price, rating}"
        A[Electronics<br/>$100<br/>★5] --> B[Electronics<br/>$200<br/>★4]
        B --> C[Electronics<br/>$300<br/>★3]
        C --> D[Books<br/>$10<br/>★5]
        D --> E[Books<br/>$20<br/>★4]
    end

    subgraph "Query Hit Analysis"
        F["✅ {category}"] --> G["✅ {category, price}"]
        G --> H["✅ {category, price, rating}"]
        I["❌ {price}"] --> J["Skip category"]
        K["❌ {rating}"] --> L["Skip category, price"]
        M["❌ {category, rating}"] --> N["Skip price"]
    end

    style F fill:#d4edda
    style G fill:#d4edda
    style H fill:#d4edda
    style I fill:#f8d7da
    style K fill:#f8d7da
    style M fill:#f8d7da

graph TB
    subgraph "Composite Index {category, price, rating}"
        A[Electronics<br/>$100<br/>★5] --> B[Electronics<br/>$200<br/>★4]
        B --> C[Electronics<br/>$300<br/>★3]
        C --> D[Books<br/>$10<br/>★5]
        D --> E[Books<br/>$20<br/>★4]
    end

    subgraph "Query Hit Analysis"
        F["✅ {category}"] --> G["✅ {category, price}"]
        G --> H["✅ {category, price, rating}"]
        I["❌ {price}"] --> J["Skip category"]
        K["❌ {rating}"] --> L["Skip category, price"]
        M["❌ {category, rating}"] --> N["Skip price"]
    end

    style F fill:#d4edda
    style G fill:#d4edda
    style H fill:#d4edda
    style I fill:#f8d7da
    style K fill:#f8d7da
    style M fill:#f8d7da

Use Cases:

• Multi-criteria queries (e.g., category + price)
• Filter + Sort combinations (e.g., status = 'paid' + sort by createdAt)
• Not suitable: Querying each condition independently (in which case, multiple single-field indexes are more flexible)

// === Composite Index:{ category: 1, price: -1, rating: 1 } ===
db.products.createIndex({ category: 1, price: -1, rating: 1 });

// ✅ Queries Using Indexes:
db.products.find({ category: 'Electronics' });                                    // Uses category
db.products.find({ category: 'Electronics', price: { $gte: 100 } });            // Uses category + price
db.products.find({ category: 'Electronics', price: { $gte: 100 }, rating: 5 }); // Use all 3 Field

// ⚠️ Queries That Do Not Use Indexes:
db.products.find({ price: { $gte: 100 } });                                      // Skip category
db.products.find({ rating: 5 });                                                 // Skip category, price
db.products.find({ category: 'Electronics', rating: 5 });                       // Skip price

Leftmost Prefix Principle: Composite indexes are only effective when used consecutively starting from the leftmost field. Skipping intermediate fields breaks the index chain—subsequent fields cannot use the index.

ESR Rule (Equality → Sort → Range): The golden rule for the order of fields in a composite index—equality filter fields come first, sort fields in the middle, and range query fields last. This will be covered in depth in a later lesson (Lesson 19).

6. Index Coverage

Concept Explanation: A covered query refers to a query in which all the fields required for the query are included in the index, allowing the engine to return results directly from the index without needing to fetch the original document. This is the ultimate form of index optimization—the query does not require any access to the document data at all.

How It Works: The standard query process is "index scan → retrieve document pointer → access the document in the table → extract fields → return"; the index-based query process is "index scan → extract fields directly from the index → return." By eliminating the need to access the table, I/O is reduced by half, and performance improves by more than 50%.

graph LR
    subgraph "General Inquiry"
        A1[Index Scan] --> A2[Get doc pointer]
        A2 --> A3[Return to the table and read the document]
        A3 --> A4[Extract Fields]
        A4 --> A5[Return Results]
    end

    subgraph "Index-Covered Queries"
        B1[Index Scan] --> B2[Directly Extract Index Fields]
        B2 --> B3[Return Results]
    end

    style A3 fill:#f8d7da
    style B2 fill:#d4edda

graph LR
    subgraph "General Inquiry"
        A1[Index Scan] --> A2[Get doc pointer]
        A2 --> A3[Return to the table and read the document]
        A3 --> A4[Extract Fields]
        A4 --> A5[Return Results]
    end

    subgraph "Index-Covered Queries"
        B1[Index Scan] --> B2[Directly Extract Index Fields]
        B2 --> B3[Return Results]
    end

    style A3 fill:#f8d7da
    style B2 fill:#d4edda

Use Cases:

• High-frequency queries require only a few fields (e.g., the list page displays only sku, title, price)
• Large document collection; high cost of lookups
• Key Condition: The projection must exclude _id (_id: 0), and all projection fields must be in the index

// === General Inquiry:I need to go back to the table to check the documentation. ===
db.products.find(
  { category: 'Electronics' },
  { sku: 1, title: 1, price: 1 }
);

// === Index-Covered Queries:Returned directly from the index ===
db.products.createIndex({ category: 1, sku: 1, title: 1, price: 1 });

db.products.find(
  { category: 'Electronics' },
  { sku: 1, title: 1, price: 1, _id: 0 }  // _id: 0 Must be excluded
);
// explain Displayed in the middle stage: 'PROJECTION_COVERED' ✅

Key Points Analysis:

1. _id is always returned by default and is not included in the regular index; you must use _id: 0 to exclude it in order to override it.
2. The more index fields there are, the more queries they cover, but the larger the index size becomes—a trade-off must be made.
3. Index coverage is most effective for large documents (the amount of I/O saved is proportional to the document size).

7. Index Management

Concept Explanation: Index management includes creating, viewing, deleting, and rebuilding indexes. In a production environment, indexes are not something you can simply “set and forget”; you need to continuously monitor their usage, delete unused indexes, and rebuild fragmented indexes.

Index Lifecycle:

graph LR
    A[Analyzing Query Patterns] --> B[Design Index]
    B --> C[Create an Index<br/>background:true]
    C --> D[Validate Hit<br/>explain()]
    D --> E[Monitoring Utilization<br/>$indexStats]
    E --> F{Low utilization rate?}
    F -->|Yes| G[Delete Index]
    F -->|No| H[Continue monitoring]
    G --> A
    H --> E

    style G fill:#f8d7da
    style D fill:#d4edda

graph LR
    A[Analyzing Query Patterns] --> B[Design Index]
    B --> C[Create an Index<br/>background:true]
    C --> D[Validate Hit<br/>explain()]
    D --> E[Monitoring Utilization<br/>$indexStats]
    E --> F{Low utilization rate?}
    F -->|Yes| G[Delete Index]
    F -->|No| H[Continue monitoring]
    G --> A
    H --> E

    style G fill:#f8d7da
    style D fill:#d4edda

// === View all indexes in the collection ===
db.products.getIndexes();

// === Delete Index ===
db.products.dropIndex('sku_1');
db.products.dropIndex({ sku: 1 });

// === Delete all indexes(Retain _id)===
db.products.dropIndexes();

// === Rebuild Index ===
db.products.reIndex();

// === View Index Size ===
db.products.totalIndexSize();

Key Points Analysis:

1. reIndex() Locks the collection; in a production environment, it is recommended to run this during a maintenance window.
2. dropIndex() We recommend first using $indexStats to confirm that the index is indeed not in use.
3. It is recommended that each set index contain no more than 10 entries; too many entries will affect write performance.

8. Index Cost

Concept Explanation: Indexes are not free—each index takes up disk space, increases write overhead, and consumes memory. Understanding the cost of indexes is the foundation for making the right trade-offs. “The more indexes, the better” is the most common misconception.

How It Works: Every time a document is inserted, updated, or deleted, MongoDB must synchronously update the B+Tree structures of all relevant indexes. If a collection has N indexes, write operations require maintaining N B+Trees. The more indexes there are, the slower the writes become, and the greater the memory usage (since the WiredTiger cache must load the index pages).

graph LR
    A[Create an index] --> B[Faster reads]
    A --> C[Slower writes]
    A --> D[Takes up space]

    B --> B1[Search +1000x]
    C --> C1[Insert/Update +50% Expenses]
    D --> D1[Per Index 1-10 MB]

    subgraph "Weighing Decisions"
        E[High query frequency?] -->|Yes| F[✅ Create an index]
        E -->|No| G[❌ Do not build]
        H[High write frequency?] -->|Yes| I[⚠️ Caution]
        H -->|No| F
    end

graph LR
    A[Create an index] --> B[Faster reads]
    A --> C[Slower writes]
    A --> D[Takes up space]

    B --> B1[Search +1000x]
    C --> C1[Insert/Update +50% Expenses]
    D --> D1[Per Index 1-10 MB]

    subgraph "Weighing Decisions"
        E[High query frequency?] -->|Yes| F[✅ Create an index]
        E -->|No| G[❌ Do not build]
        H[High write frequency?] -->|Yes| I[⚠️ Caution]
        H -->|No| F
    end

Quantifying the Cost of Indexing:

Index Trade-offs:

• ✅ Frequently queried fields → Create an index
• ⚠️ Fields that are updated frequently → Be cautious when creating indexes
• ⚠️ Large array fields → multikey indexes may become too large

Index Selection Strategy:

9. Comprehensive Practical Training

(1) Index Design Principles

Detailed Explanation of the ESR Rule: The order of fields in a composite index should follow Equality → Sort → Range. Place the equality filter field first (to quickly narrow down the range), the sort field in the middle (to leverage the index’s sorted order and avoid in-memory sorting), and the range query field last (since a range scan will interrupt the use of subsequent index fields).

graph LR
    E["Equality<br/>Equivalence Filtering<br/>category='A'"] --> S["Sort<br/>Sort<br/>createdAt: -1"]
    S --> R["Range<br/>Scope<br/>price >= 100"]

    style E fill:#d4edda
    style S fill:#cce5ff
    style R fill:#fff3cd

graph LR
    E["Equality<br/>Equivalence Filtering<br/>category='A'"] --> S["Sort<br/>Sort<br/>createdAt: -1"]
    S --> R["Range<br/>Scope<br/>price >= 100"]

    style E fill:#d4edda
    style S fill:#cce5ff
    style R fill:#fff3cd

// === Design of an E-commerce Product Index ===
db.products.createIndex({ sku: 1 }, { unique: true });                    // Unique Index
db.products.createIndex({ category: 1, price: -1 });                       // Category Page
db.products.createIndex({ category: 1, rating: -1 });                      // Categories+Rating
db.products.createIndex({ isActive: 1, createdAt: -1 });                   // Listing Date
db.products.createIndex({ title: 'text', description: 'text' });          // Full-Text Search
db.products.createIndex({ tags: 1 });                                      // Tag Filtering

(2) Analysis of Index Usage

// === Analyzing Slow Queries ===
db.products.find({
  category: 'Electronics',
  price: { $gte: 100, $lte: 1000 },
  isActive: true
}).sort({ createdAt: -1 }).limit(20);

// Check whether an index is being used
const explain = db.products.find({...}).explain('executionStats');
print('Stage:', explain.queryPlanner.winningPlan.stage);
print('Docs Examined:', explain.executionStats.totalDocsExamined);
print('Time:', explain.executionStats.executionTimeMillis, 'ms');

▶ Example: Practical Index Design and Performance Analysis

// 1. Create a Test Set(10 10,000 product records)
for (let i = 0; i < 100000; i++) {
  db.products.insertOne({
    sku: 'SKU-' + i.toString().padStart(6, '0'),
    title: 'Product ' + i,
    category: ['Electronics', 'Books', 'Clothing', 'Home'][i % 4],
    price: Math.random() * 1000,
    stock: Math.floor(Math.random() * 100),
    createdAt: new Date(Date.now() - Math.random() * 30 * 24 * 60 * 60 * 1000),
    isActive: true
  });
}

// 2. Create an Index
db.products.createIndex({ sku: 1 }, { unique: true });
db.products.createIndex({ category: 1, price: -1 });  // Composite Index
db.products.createIndex({ createdAt: -1 });

// 3. Comparison:Indexed vs No index
console.time('Unindexed Query');
db.products.find({ category: 'Electronics', price: { $gte: 100, $lte: 500 } }).toArray();
console.timeEnd('Unindexed Query');  // ~500ms

// After creating the index
db.products.createIndex({ category: 1, price: 1 });

console.time('Indexed queries');
db.products.find({ category: 'Electronics', price: { $gte: 100, $lte: 500 } }).toArray();
console.timeEnd('Indexed queries');  // ~5ms(Performance ↑100x)

// 4. explain() Analyze the Execution Plan
const explain = db.products.find({
  category: 'Electronics',
  price: { $gte: 100, $lte: 500 }
}).sort({ createdAt: -1 }).limit(20).explain('executionStats');

print('Stage:', explain.queryPlanner.winningPlan.stage);  // IXSCAN
print('Index:', explain.queryPlanner.winningPlan.inputStage?.indexName);  // category_1_price_1
print('Docs Examined:', explain.executionStats.totalDocsExamined);
print('Keys Examined:', explain.executionStats.totalKeysExamined);
print('Returned:', explain.executionStats.nReturned);
print('Time:', explain.executionStats.executionTimeMillis, 'ms');

// 5. Index-Covered Queries(No need to return to the table)
db.products.createIndex({ category: 1, sku: 1, price: 1 });

db.products.find(
  { category: 'Electronics' },
  { sku: 1, price: 1, _id: 0 }  // Return only fields that are already in the index
).explain();

// Stage: PROJECTION_COVERED(No need to consult the documentation)

Output: The index improves query performance by a factor of 100; EXPLAIN() shows IXSCAN (index scan) + PROJECTION_COVERED (index coverage).

❓ FAQ

📖 Summary

• Indexing principle: B-tree data structure, O(log N) queries
• createIndex syntax: single-field, composite, unique, text
• explain() interpretation: winningPlan + executionStats
• The Leftmost Prefix Principle for Composite Indexes
• Index coverage: Returns results directly from the index, avoiding a table lookup
• Index overhead: Slow writes + takes up space

📝 Exercises

1. Basic Question (⭐): Create three indexes—sku, category, and createdAt—for the products collection.
2. Basic Question (⭐): Use explain() to analyze a query and confirm that an index is being used (IXSCAN).
3. Advanced Exercise (⭐⭐): Create a composite index { category: 1, price: -1 } to test the leftmost prefix principle (which queries use the index).
4. Advanced Problem (⭐⭐): Implement an index-covered query (where all fields are included in the index).
5. Challenge Question (⭐⭐⭐): Design a complete indexing scheme for e-commerce products (8 indexes), and analyze the query scenarios for each index.

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