Optimize and Tune MongoDB Performance with Hidden Indexes
Rate this tutorial
MongoDB 4.4 is the biggest release of MongoDB to date and is available in
. You can try out it out in
the development release. There is so much
to talk about ranging from new features like custom aggregation expressions, improvements to existing functionality like refinable shard keys, and much more.
In this post, we are going to look at a new feature coming to MongoDB 4.4 that will help you better optimize and fine-tune the performance of your queries as your application evolves called hidden indexes.
For this tutorial you'll need:
Most database technologies, and MongoDB is no different, rely on indexes to speed up performance and efficiently execute queries. Without an index, MongoDB would have to perform a collection scan, meaning scanning every document in a collection to filter out the ones the query asked for.
With an index, and often times with a correct index, this process is greatly sped up. But choosing the right data to index is an art and a science of its own. If you'd like to learn a bit more about indexing best practices, check out this
. Building, maintaining, and dropping indexes can be resource-intensive and time-consuming, especially if you're working with a large dataset.
The awesome thing about hidden indexes is that besides being hidden from the query planner, meaning they won't be used in the execution of the query, they behave exactly like a normal index would. This means that hidden indexes are still updated and maintained even while hidden (but this also means that a hidden index continues to consume disk space and memory so if you find that hiding an index does not have an impact on performance, consider dropping it), hidden unique indexes still apply the unique constraint to documents, and hidden TTL indexes still continue to expire documents.
To create a hidden index in MongoDB 4.4 you simply pass a
hiddenparameter and set the value to
db.collection.createIndex()options argument. For a more concrete example, let's assume we have a
moviescollection that stores documents on individual films. The documents in this collection may look something like this:
Now let's assume we wanted to create a brand new index on the title of the movie and we wanted it to be hidden by default. To do this, we'd execute the following command:
From the screenshot, we can see that
collscanwas used and that the actual query execution time took 8ms. If we navigate to the Indexes tab in MongoDB Compass, we can also confirm that we do have a
title_1index created, that's consuming 315.4kb, and has been used 0 times.
This is the expected behavior as we created our index as hidden from the get-go. Next, we'll learn how to unhide the index we created and see if we get improved performance.
To measure the impact an index has on our query performance, we'll unhide it. We have a couple of different options on how to accomplish this. We can, of course, use
db.runCommand()in conjunction with
collMod, but we also have a number of mongo shell helpers that I think are much easier and less verbose to work with. In this section, we'll use the latter.
To unhide an index, we can use the
db.collection.unhideIndex()method passing in either the name of the index, or the index keys. Let's unhide our title index using the index keys. To do this we'll execute the following command:
Our response will look like this:
If we were to execute our query to find Toy Story 3 in MongoDB Compass now and view the Explain Plan, we'd see that instead of a
collscanor collection scan our query will now use the
ixscanor index scan, meaning it's going to use the index. We get the same results back, but now our actual query execution time is 0ms.
Additionally, if we look at our Indexes tab, we'll see that our
title_1index was used one time.
When you create an index in MongoDB 4.4, by default it will be created with the
hiddenproperty set to false, which can be overwritten to create a hidden index from the get-go as we did in this tutorial. But what about existing indexes? Can you hide and unhide those? You betcha!
Just like the
db.collection.unhideIndex()helper method, there is a
db.collection.hideIndex()helper method, and it allows you to hide an existing index via its name or index keys. Or you can use the
db.runCommand()in conjunction with
collMod. Let's hide our title index, this time using the
Executing this command will once again hide our
title_1index from the query planner so when we execute queries and search for movies by their title, MongoDB will perform the much slower
collscanor collection scan.
Hidden indexes in MongoDB 4.4 make it faster and more efficient for you to tune performance as your application evolves. Getting indexes right is one-half art, one-half science, and with hidden indexes you can make better and more informed decisions much faster.
Safe Harbor Statement
The development, release, and timing of any features or functionality described for MongoDB products remains at MongoDB's sole discretion. This information is merely intended to outline our general product direction and it should not be relied on in making a purchasing decision nor is this a commitment, promise or legal obligation to deliver any material, code, or functionality. Except as required by law, we undertake no obligation to update any forward-looking statements to reflect events or circumstances after the date of such statements.