Unnecessary Indexes

#Unnecessary Indexes

Before we go any further, we want to emphasize that indexes are good. Indexes allow MongoDB to efficiently query data. If a query does not have an index to support it, MongoDB performs a collection scan, meaning that it scans every document in a collection. Collection scans can be very slow. If you frequently execute a query, make sure you have an index to support it.

Now that we have an understanding that indexes are good, you might be wondering, "Why are unnecessary indexes an anti-pattern? Why not create an index on every field just in case I'll need it in the future?"

We've discovered three big reasons why you should remove unnecessary indexes:

1. Indexes take up space. Each index is at least 8 kB and grows with the number of documents associated with it. Thousands of indexes can begin to drain resources.
2. Indexes can impact the storage engine's performance. As we discussed in the previous post in this series about the Massive Number of Collections Anti-Pattern, the WiredTiger storage engine (MongoDB's default storage engine) stores a file for each collection and for each index. WiredTiger will open all files upon startup, so performance will decrease when an excessive number of collections and indexes exist.
3. Indexes can impact write performance. Whenever a document is created, updated, or deleted, any index associated with that document must also be updated. These index updates negatively impact write performance.

In general, we recommend limiting your collection to a maximum of 50 indexes.

To avoid the anti-pattern of unnecessary indexes, examine your database and identify which indexes are truly necessary. Unnecessary indexes typically fall into one of two categories:

1. The index is rarely used or not at all.
2. The index is redundant because another compound index covers it.

#Example

Consider Leslie from the incredible TV show Parks and Recreation. Leslie often looks to other powerful women for inspiration.

Let's say Leslie wants to inspire others, so she creates a website about her favorite inspirational women. The website allows users to search by full name, last name, or hobby.

Leslie chooses to use MongoDB Atlas to create her database. She creates a collection named InspirationalWomen. Inside of that collection, she creates a document for each inspirational woman. Below is a document she created for Sally Ride.

1
// InspirationalWomen collection
2
3
{
4
   "_id": {
5
      "$oid": "5ec81cc5b3443e0e72314946"
6
   },
7
   "first_name": "Sally",
8
   "last_name": "Ride",
9
   "birthday": 1951-05-26T00:00:00.000Z,
10
   "occupation": "Astronaut",
11
   "quote": "I would like to be remembered as someone who was not afraid to do what
             she wanted to do, and as someone who took risks along the way in order
             to achieve her goals.",
12
   "hobbies": [
13
      "Tennis",
14
      "Writing children's books"
15
   ]
16
}

Leslie eats several sugar-filled Nutriyum bars, and, riding her sugar high, creates an index for every field in her collection.

She also creates a compound index on the last_name and first_name fields, so that users can search by full name. Leslie now has one collection with eight indexes:

1. _id is indexed by default (see the MongoDB Docs for more details)
2. { first_name: 1 }
3. { last_name: 1 }
4. { birthday: 1 }
5. { occupation: 1 }
6. { quote: 1 }
7. { hobbies: 1 }
8. { last_name: 1, first_name: 1}

Leslie launches her website and is excited to be helping others find inspiration. Users are discovering new role models as they search by full name, last name, and hobby.

#Removing Unnecessary Indexes

Leslie decides to fine-tune her database and wonders if all of those indexes she created are really necessary.

She opens the Atlas Data Explorer and navigates to the Indexes pane. She can see that the only two indexes that are being used are the compound index named last_name_1_first_name_1 and the hobbies_1 index. She realizes that this makes sense.

Her queries for inspirational women by full name are covered by the last_name_1_first_name_1 index. Additionally, her query for inspirational women by last name is covered by the same last_name_1_first_name_1 compound index since the index has a last_name prefix. Her queries for inspirational women by hobby are covered by the hobbies_1 index. Since those are the only ways that users can query her data, the other indexes are unnecessary.

In the Data Explorer, Leslie has the option of dropping all of the other unnecessary indexes. Since MongoDB requires an index on the _id field, she cannot drop this index.

In addition to using the Data Explorer, Leslie also has the option of using MongoDB Compass to check for unnecessary indexes. When she navigates to the Indexes pane for her collection, she can once again see that the last_name_1_first_name_1 and the hobbies_1 indexes are the only indexes being used regularly. Just as she could in the Atlas Data Explorer, Leslie has the option of dropping each of the indexes except for _id.

Leslie decides to drop all of the unnecessary indexes. After doing so, her collection now has the following indexes:

1. _id is indexed by default
2. { hobbies: 1 }
3. { last_name: 1, first_name: 1}

#Summary

Creating indexes that support your queries is good. Creating unnecessary indexes is generally bad.

Unnecessary indexes reduce performance and take up space. An index is considered to be unnecessary if (1) it is not frequently used by a query or (2) it is redundant because another compound index covers it.

You can use the Atlas Data Explorer or MongoDB Compass to help you discover how frequently your indexes are being used. When you discover an index is unnecessary, remove it.

Be on the lookout for the next post in this anti-patterns series!

#Related Links

Check out the following resources for more information:

• MongoDB Docs: Remove Unnecessary Indexes
• MongoDB Docs: Indexes
• MongoDB Docs: Compound Indexes — Prefixes
• MongoDB Docs: Indexing Strategies
• MongoDB Docs: Data Modeling Introduction
• MongoDB University M320: Data Modeling
• MongoDB University M201: MongoDB Performance
• Blog Series: Building with Patterns