MongoDB Indexing Best Practices: Performance Tips & Tricks

With any database, MongoDB included, indexes are a critical part of the development process. Without them, as your database grows in size, your database performance will continue to decrease with it. With valid indexes, as your data grows, the accessing performance will continue to be optimal.

In this article, we're going to explore the various types of indexes available within MongoDB, some tips and tricks to get the best experience, and instructions on how to make sure you're truly making use of the indexes that you're creating. If you're still learning MongoDB, I recommend checking out the Introduction to MongoDB course.

Where Are MongoDB Indexes Important and Why?

When talking about indexes, we're talking about performance. Indexes are the difference between looking at every document in a collection to see if it matches our query, or a more specific batch of documents that match our query. If our collection is small and there aren't a lot of documents to scan through, the performance will be good either way. But if we have 100,000 documents in a collection and 100 are going to match our query, it's probably going to take a bit of time to look at all 100,000 documents to find that 100, if there isn't an index based on those 100 documents.

There are numerous types of indexes in MongoDB:

• Single field: An index on one particular field within the document. The default _id field index can be considered a single field index.
• Compound: An index based on multiple fields of a document, where the order of fields used in the query and in the index is important.
• Multikey: An index used on array fields.
• Wildcard: An index that supports fields that are subject to be named slightly different between documents. This index won't perform as well as other indexes.
• Geospatial: An index to improve performance on geospatial queries that use latitude and longitude coordinate data.
• Hashed: An index that is best used with shard keys.

For more information on the various index types, check out the MongoDB documentation.

More often than not, you'll find yourself using single field, compound, or multikey indexes, but at the end of the day, it really depends on the type of application, queries, and data.

Indexes are important with any database interaction that fetches documents. This is not limited to the retrieval of documents using a find or findOne operation. When updating and deleting documents, you are also using a match criteria that the database will use to find documents before performing the update or delete.

The find and findOne operators are not the only retrieval operators that can benefit from an index. When using the $match operator as the first stage of your aggregation pipeline, an index would speed things up dramatically.

MongoDB Indexing Tips and Tricks for Success

Creating random indexes can be just as inefficient as using no indexes in MongoDB. Instead, there are a few things to consider that will help you build a much more efficient application.

Equality, sort, range (ESR) for compound indexes

The rule of thumb when creating a compound index is to determine how you'll use each of the fields in your query and make sure it follows the ESR) best practice.

What does ESR mean? Let's break it down:

• Equality: Fields that will be exact matched, not loosely matched. For example, matching on someone's exact name in your query would be an equality stage.
• Sort: Fields that you'll use for sorting in ascending or descending order.
• Range: Fields that you'll be matching on that are not going to be an exact match. For example, using the $lt or $gt operator will match on documents within a range, but not a specific match.

To get a better idea of what all this means, take a look at the following query:

db.employee.find(
	{
		"department": "Developer Relations",
		"salary": {
			"$lt": 80000
		}
	}
).sort(
	{ 
		"name": 1 
	}
);

The above query does an equality match on the department, a range match on the salary, and sorts the results by the name.

Following the ESR rules, the index would look something like this:

db.employee.createIndex({
	"department": 1,
	"name": 1,
	"salary": 1
});

We want to follow ESR rules to prevent in-memory sorts, and to narrow the search space for a performant query execution.

Covered queries

Indexing in general will create a smaller subset of documents to scan through when running a query and as a result, improve performance. But if you want to take your application performance to the next level, you'd want to explore covered queries. With a covered query, you are getting all the results from your query directly from the index, not having to scan through any documents in your collection.

There are a few catches here that may or may not make covered queries possible in your application:

• The match criteria and sort have to match exactly what your index was created around.
• The fields you're requesting in your result set also have to match your index.
• When sharding, the shard key needs to be a part of your index.

So what does all this mean?

Take the following index, for example:

db.department.createIndex({
	"country": 1,
	"salary": 1
});

In the above example, we want our documents indexed by the country field and the salary field. Then, we can run the following query:

db.department.find(
	{
		"country": "US"
	},
	{
		"country": 1,
		"salary": 1
	}
);

The above query, which includes a projection, will use our index, but it won't be a covered query. The _id field is returned by default in MongoDB and in this example, the _id field was never added to our index. So we can either change our index, or change our query. In the example of changing the query, we can do this:

db.department.find(
	{
		"country": "US"
	},
	{
		"country": 1,
		"salary": 1,
		"_id": 0
	}
);

Remember, if you want to use a covered query, the index must be valid and the fields requested must be included in the index. Otherwise, you're just finding documents managed by the index, not in the index itself.

You can validate whether or not a covered query is being used by making use of the executionStats argument in the explain operator mentioned further down in this article. The totalDocsExamined result will determine what is being used. If the value is zero, you're using a covered query.

Reducing or eliminating unnecessary indexes

Indexes are not free in the context of server resources. Every index needs to be maintained by the server and this maintenance uses CPU resources, RAM, and disk. As you create more indexes, you'll be creating more overhead which could result in higher costs or reduced performance in the scenario of an under-provisioned server. This reduced performance could spread to writes and deletes, not just reads, because MongoDB is consuming more resources.

It's a good idea to only create indexes for high-frequency queries. Does that query that gets run once a month really need an index? Probably not. Does that query that gets run every 10 seconds need an index? Definitely!

Learn about a few more indexing best practices on the MongoDB Blog.

Is Your MongoDB Index Working?

Just because you've created an index within MongoDB doesn't mean the query you're running is actually using it. In MongoDB, we can actually troubleshoot what index is being used for any particular query.

Let's take the following index, for example:

db.people.createIndex({
	"name": 1,
	"is_active": 1
});

In the above example, we're indexing on a name field and an is_active field. Now, we want to run the following query:

db.people.find({
	"is_active": true
});

The above query will run with or without a valid index, and if the dataset is small, it will run fast regardless. However, if we wanted to see if it is using our index, we can make an adjustment to the query:

db.people.find({
	"is_active": true
}).explain();

This time around, we're using the explain method to get the execution plan of the query. In the execution plan, we can spot the following:

winningPlan: {
	isCached: false,
	stage: 'COLLSCAN',
	filter: { is_active: { '$eq': true } },
	direction: 'forward'
}

For this particular query, MongoDB used a "COLLSCAN" which means it did not make use of our index even though it included one of our fields. If we change our query to something like this:

db.people.find({
	"name": "Nic Raboy",
	"is_active": true
}).explain();

The result of our explain has changed:

winningPlan: {
	isCached: false,
	stage: 'FETCH',
	inputStage: {
		stage: 'IXSCAN',
		keyPattern: { name: 1, is_active: 1 },
		indexName: 'name_1_is_active_1',
		isMultiKey: false,
		multiKeyPaths: { name: [], is_active: [] },
		isUnique: false,
		isSparse: false,
		isPartial: false,
		indexVersion: 2,
		direction: 'forward',
		indexBounds: {
			name: [ '["Nic Raboy", "Nic Raboy"]' ],
			is_active: [ '[true, true]' ]
		}
	}
},

This time around, the execution plan said we used an "IXSCAN" and specified the name of the index used, which in this case was the name_1_is_active_1 index. The name was automatically defined when we created the index.

For an update operation, the process is similar with an exception being where the explain function is placed.

An update operation might look like the following:

db.people.explain().update(
    {
        “name”: “Nic Raboy”,
        “Is_active”: true
    },
    {
        “$set”: { “is_active”: false }
    }
);

Notice that in the above example, the explain happens before the update function.

If you're using the expected index in your update operation, you'll see the "IXSCAN" value along with the index name. Find more information on update operations and using indexes with them.

An alternative method to finding the details of your query’s execution plan is to make use of the runCommand on the database. While not as elegant to look at, it is quite powerful and would look something like the following:

db.runCommand({
    "explain": {
        "update": "people",
        "updates": [
            {
                "q": { 
                    "name": "Nic Raboy",
                    "is_active": true
                },
                "u": {
                    "$set": { "is_active": false }
                }
            }
        ]
    },
    "verbosity": "allPlansExecution"
});

The above would work the same as the update operation with the explain function.

If you're not confident at creating indexes, or you're noticing that your queries are executing slower than expected, consider checking out the execution details.

Conclusion

You just saw some best practices around indexing in MongoDB. While we didn't explore every possible indexing scenario, you can use this as a starting point to making sure your users get the best possible experience when it comes to working with your data. 

To reiterate on a few things:

• Follow the equality, sort, range (ESR) rule whenever possible.
• If you can, try to do a covered query.
• Don't go crazy creating indexes, but instead figure out what queries will be used the most and how much data is being fetched in them.
• Lean on the explain operator for testing your indexes and their performance.

The above points were not an exhaustive list of indexing best practices that will improve your application performance. There are other factors, such as cardinality amounts.

For more help with indexing, check out the MongoDB documentation. If you're using a paid tier within MongoDB Atlas, consider using some of the performance adviser tools built in for index and query recommendations.