Use MongoDB Indexes

Introduction

In this lab, you will learn the fundamentals of using MongoDB indexes to optimize query performance and sorting operations. The lab covers several key steps, including querying with indexes, sorting using indexes, checking index coverage, handling missing indexes, and removing unused indexes. By the end of the lab, you will have a solid understanding of how to leverage MongoDB's indexing capabilities to improve the efficiency of your database applications.

The lab starts by creating a sample collection and demonstrating how to create an index on a specific field to speed up queries. It then explores using indexes for sorting operations and checking the index coverage for a given query. The lab also covers scenarios where an index is missing and how to handle such cases, as well as the process of removing unused indexes to maintain a healthy database.

Skills Graph

Query With Index

In this step, you'll learn how to use indexes to optimize query performance in MongoDB. Indexes are special data structures that store a small portion of a collection's data set in an easy-to-traverse form, making database queries much faster.

First, let's start by opening the MongoDB shell:

mongosh

Now, let's create a sample collection with some data to demonstrate indexing:

use indexlab

db.users.insertMany([
    { name: "Alice", age: 28, city: "New York" },
    { name: "Bob", age: 35, city: "San Francisco" },
    { name: "Charlie", age: 42, city: "Chicago" },
    { name: "David", age: 25, city: "New York" },
    { name: "Eve", age: 31, city: "San Francisco" }
])

Let's create an index on the 'age' field to speed up queries:

db.users.createIndex({ age: 1 });

The 1 indicates an ascending index. Now, let's run a query and check its performance:

db.users.find({ age: { $gt: 30 } }).explain("executionStats");

This command will show you the query execution details, including whether the index was used.

Example output:

{
  "queryPlanner": {
    "winningPlan": {
      "stage": "FETCH",
      "inputStage": {
        "stage": "IXSCAN",
        "indexName": "age_1"
      }
    }
  },
  "executionStats": {
    "executionTimeMillis": 0,
    "totalDocsExamined": 2,
    "totalKeysExamined": 2
  }
}

The key things to notice are:

• IXSCAN indicates the index was used
• totalDocsExamined is low, showing the index helped optimize the query
• executionTimeMillis is very short

Sort Using Index

In this step, you'll learn how to use indexes to optimize sorting operations in MongoDB. Sorting large datasets can be computationally expensive, but with the right index, we can significantly improve performance.

Let's continue working with our existing database from the previous step:

use indexlab

We'll create a compound index that allows efficient sorting:

db.users.createIndex({ city: 1, age: -1 });

This compound index will help us sort by city in ascending order and age in descending order. Let's run a query that demonstrates this:

db.users.find().sort({ city: 1, age: -1 }).explain("executionStats");

Example output:

{
  "queryPlanner": {
    "winningPlan": {
      "stage": "SORT",
      "sortPattern": { "city": 1, "age": -1 },
      "inputStage": {
        "stage": "IXSCAN",
        "indexName": "city_1_age_-1"
      }
    }
  },
  "executionStats": {
    "executionTimeMillis": 0,
    "totalDocsExamined": 5,
    "totalKeysExamined": 5
  }
}

Now, let's see the actual sorted results:

db.users.find().sort({ city: 1, age: -1 });

Key observations:

• The index supports sorting by multiple fields
• 1 means ascending order
• -1 means descending order
• The query uses the index for efficient sorting

Check Index Coverage

In this step, you'll learn how to check index coverage and understand how MongoDB uses indexes to optimize query performance. Index coverage helps you determine whether your queries are fully utilizing the available indexes.

Let's continue working with our existing database:

use indexlab

We'll create a more comprehensive index to demonstrate coverage:

db.users.createIndex({ name: 1, city: 1, age: 1 });

Now, let's run a query that can fully use our index:

db.users.find({ name: "Alice", city: "New York" }).explain("executionStats");

Example output:

{
  "queryPlanner": {
    "winningPlan": {
      "stage": "FETCH",
      "inputStage": {
        "stage": "IXSCAN",
        "indexName": "name_1_city_1_age_1"
      }
    }
  },
  "executionStats": {
    "totalDocsExamined": 1,
    "totalKeysExamined": 1,
    "indexesUsed": ["name_1_city_1_age_1"]
  }
}

Let's check a query with partial index coverage:

db.users.find({ city: "New York" }).explain("executionStats");

Key observations:

• totalDocsExamined shows how many documents were checked
• totalKeysExamined indicates index efficiency
• indexesUsed shows which indexes were utilized

To list all indexes in the collection:

db.users.getIndexes();

Handle Missing Index

In this step, you'll learn how to handle queries without indexes and understand the performance implications of missing indexes in MongoDB.

Let's continue working with our existing database:

use indexlab

First, let's run a query without an index to see its performance:

db.users.find({ email: "[email protected]" }).explain("executionStats");

Example output:

{
  "queryPlanner": {
    "winningPlan": {
      "stage": "COLLSCAN",
      "direction": "forward"
    }
  },
  "executionStats": {
    "totalDocsExamined": 5,
    "executionTimeMillis": 0
  }
}

Key observations:

• COLLSCAN indicates a collection scan (checking every document)
• totalDocsExamined shows all documents were checked

Let's create an index to improve this query's performance:

db.users.createIndex({ email: 1 });

Now, run the same query again:

db.users.find({ email: "[email protected]" }).explain("executionStats");

Example output:

{
  "queryPlanner": {
    "winningPlan": {
      "stage": "FETCH",
      "inputStage": {
        "stage": "IXSCAN",
        "indexName": "email_1"
      }
    }
  },
  "executionStats": {
    "totalDocsExamined": 0,
    "totalKeysExamined": 0,
    "executionTimeMillis": 0
  }
}

Key improvements:

• IXSCAN replaces COLLSCAN
• totalDocsExamined is now 0
• Query performance is significantly improved

To add email to our existing users:

db.users.updateMany({}, [
  {
    $set: {
      email: {
        $concat: [{ $toLower: "$name" }, "@example.com"]
      }
    }
  }
]);

Remove Unused Index

In this step, you'll learn how to identify and remove unused indexes in MongoDB to optimize database performance and storage.

Let's first view all existing indexes in our collection:

use indexlab
db.users.getIndexes()

Example output:

[
  { v: 2, key: { _id: 1 }, name: '_id_' },
  { v: 2, key: { age: 1 }, name: 'age_1' },
  { v: 2, key: { city: 1, age: -1 }, name: 'city_1_age_-1' },
  { v: 2, key: { name: 1, city: 1, age: 1 }, name: 'name_1_city_1_age_1' },
  { v: 2, key: { email: 1 }, name: 'email_1' }
]

To check index usage, we'll use the $indexStats aggregation:

db.users.aggregate([{ $indexStats: {} }]);

Example output:

[
  {
    name: 'age_1',
    key: { age: 1 },
    host: '...',
    accesses: {
      ops: 0,
      since: ISODate("...")
    }
  },
  // ... other index statistics
]

Let's remove an unused index. We'll remove the 'city1_age-1' index:

db.users.dropIndex("city_1_age_-1");

Verify the index has been removed:

db.users.getIndexes();

Best practices for index management:

• Regularly check index usage
• Remove indexes that are not frequently used
• Consider the balance between query performance and storage overhead

Summary

In this lab, you learned how to use MongoDB indexes to optimize query performance and sorting operations. First, you created an index on the 'age' field and observed how the index was used to speed up queries that filter on the 'age' field. Then, you created a compound index to enable efficient sorting on multiple fields. You also learned how to check index coverage and handle missing indexes, as well as how to remove unused indexes to maintain a healthy index structure.