Using Indexing To Optimize MongoDB Performance
Tags: mongodb, database, development, programming
Published: 2024-05-30 17:16:03

### Introduction
Every system must have its database performance **optimized** in order to function properly. It improves the app's overall performance. When data grows, an efficient database system can handle the extra load without experiencing noticeable slowdowns and process data much more quickly and improve user experience.

### What is Indexing?
Database indexing is a technique for improving it's performance by speeding up **read** operations. It is similar to the indexes used in books. It efficiently guides database queries, resulting in overall improved performance.

### Why Indexing?
Indexing can be used to improve overall performance of MongoDB. Indexes **decrease** the amount of data that query operations must process, which **increases** the performance of read operations. This lessens the effort involved in responding to requests in MongoDB. For this tutorial, we'll look at a simple MongoDB collection called `students`, which has the following field in each document.

```javascript
{
    _id: ObjectId, //for this example we will consider a simple number
    name: string, //name of students
    age: number, //age of students
}
```

Assume that the `students` collection contains the following documents.

| \_id | name    | age | gender |
| ---- | ------- | --- | ------ |
| 1    | Alice   | 25  | female |
| 2    | Bob     | 30  | male   |
| 3    | Charlie | 22  | male   |
| 4    | David   | 30  | male   |
| 5    | Eve     | 22  | female |

### Query Performance Without Indexing

Now, without indexing, let us find 'Eve' using the `db.students.find({name: 'Eve', age: 22}).explain('executionStats')` method. Here `explain('executionStats')` method provides information about the performance of the query and it returns following object:

```javascript
{
   queryPlanner: {
         ...
         winningPlan: {
            stage: 'COLLSCAN',
            ...
         }
   },
   executionStats: {
      executionSuccess: true,
      nReturned: 1,
      executionTimeMillis: 0,
      totalKeysExamined: 0,
      totalDocsExamined: 5,
      executionStages: {
         stage: 'COLLSCAN',
         ...
      },
      ...
   },
   ...
}
```

- `queryPlanner.winningPlan.stage: 'COLLSCAN'` means this query did not use any indexing rather it performed a **collection scan** which is basically searching one document after another which is generally an expensive process.

- `executionStats.nReturned: 1` indicates that this query returned only 1 document.

- `executionStats.totalKeysExamined: 0` shows that this query is not using any indexing for search.

- `executionStats.totalDocsExamined: 5` indicates this query scanned total of **5 documents** i.e. entire collection is scanned.

### Query Performance With Indexing

Before creating an index let's find the current available indexes. To get the current indexes we can use `db.students.getIndexes()` method, it will return the following array:

```javascript
[ { v: 2, key: { _id: 1 }, name: '_id_' } ]
```

`key` means the `field name` based on which the indexing is created that means the *`_id` field is already indexed by default*. So, searching with `_id` already performed using an index.

Let's create index for `age` and `name` field of our collection where `age` will be in **ascending order** and `name` will be in **descending order**.

To create the index we can use `db.students.createIndex({age:1, name: -1})` `1` means ascending and `-1` means descending order. Calling the `getIndexes()` method again it will return the following array:

```javascript
[
  { v: 2, key: { _id: 1 }, name: '_id_' },
  { v: 2, key: { age: 1, name: -1 }, name: 'age_1_name_-1' }
]
```

New index with name `age_1_name_-1` is created.

> **Note:** Creating an index on the same field will throw an error. You need to *delete* the previous index using the [**db.collection.dropIndex()**](https://www.mongodb.com/docs/manual/reference/method/db.collection.dropIndex/) method first.


Now, let's run the query `db.students.find({name: 'Eve', age: 22}).explain('executionStats')` again and it returns the following result:

```javascript
{
   queryPlanner: {
    ...
    winningPlan: {
      stage: 'FETCH',
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: { age: 1, name: -1 },
        indexName: 'age_1_name_-1',
        ...
      }
    },
    ...
  },
   executionStats: {
         executionSuccess: true,
         nReturned: 1,
         executionTimeMillis: 1,
         totalKeysExamined: 1,
         totalDocsExamined: 1,
         executionStages: {
            ...
         },
         ...
   },
   ...
}
```
- `queryPlanner.winningPlan.inputStage.stage: 'IXSCAN'` indicates that this time it used **index scanning**.

- `executionStats.nReturned: 1` means this query returned 1 document.

- `executionStats.totalKeysExamined: 1` shows that this query is used **one key** for the execution of the query.

- `executionStats.totalDocsExamined: 1` means this query scanned **only one document** which previously was 5.

So, using index scanning made a huge improvement.

### How MongoDB IXScan Works

Similar to other databases, MongoDB employs [**B-trees**](https://www.programiz.com/dsa/b-tree) to store indexes. If a document has n number of collections then using `COLLSCAN` has complexity of `O(n)` since it needs to check all the documents for worst case on the other hand B-tree has **better** searching complexity which is `O(log n)`. Indexing scans the B-tree for the key and then returns the documents to which the key points. MongoDB indexing can be described in following steps:

1. **Initiating an Empty B-tree:** When you use the `createIndex()` method in MongoDB, an **empty B-tree** is created for the index. The B-tree is initially empty and will be filled out as documents are added to the collection.

2. **Updating the Tree Upon Insertion:** As documents are inserted into the collection, the B-tree index is updated to reflect these insertions. The B-tree is maintained in a balanced state to ensure efficient querying.

3. **Traversing the Tree for Queries:** When you execute a query that can utilize an index, MongoDB traverses the B-tree to find the **matching** documents efficiently. This traversal involves navigating the B-tree based on the values being queried. If the value is less than or equal to a node it will traverse to **left** subtree else **right** subtree.

4. **Leaf Nodes Pointing to Documents:** In a B-tree index, the leaf nodes typically contain **references** (pointers) to the actual documents in the collection that match the indexed values. This allows MongoDB to quickly locate the documents that satisfy the query conditions.

5. **Selecting Documents from Pointers:** When MongoDB finds leaf nodes containing pointers to documents, it retrieves those documents from the collection. These documents are then returned as query results.

The image below can help to summarize the entire process.

<p align="center">
  <img src="https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dhwgtvbgmts3v429ivsa.png" alt="Image description">
  <center><em>Fig: IXSCAN scan visualization</em></center>
  <br>
</p>


> **Note:** In MongoDB, the internal structure of non-leaf nodes within B-tree indexes is determined by the MongoDB server **itself**. These non-leaf nodes serve as guides for queries, directing them towards the appropriate leaf nodes where the indexed data is stored.

#### Execution Of The Query Using COLLSCAN


<p align="center">
  <img src="https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7jst91sa9lqpjwjqp448.png" alt="Image description">
  <center><em>Fig: COLLSCAN execution</em></center>
  <br>
</p>

#### Execution Of The Query Using IXSCAN

<p align="center">
  <img src="https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f2a1bns59d3933r4s299.png" alt="Image description">
  <center><em>Fig: IXSCAN execution</em></center>
  <br>
</p>

### Avoid Indexing When
- A collection has a **high** write-to-read ratio, and indexing are costly because each insert requires updating any indexes.
- For **smaller** databases, implementing indexing may not provide a substantial improvement in performance. The benefits of indexing become more apparent as the size of the database grows, but for smaller datasets, the performance gains might be minimal.

### Conclusion
Indexing is an excellent approach to improve the performance of your database. It enhances the user experience by making a system quicker. This can be accomplished by indexing the most frequently used fields of a collection. It is also crucial to note that indexing has its drawbacks. It demands more storage and processing. So, it's important to understand when to utilize it and when not to.