Mastering MongoDB Hidden Indexes for Optimal Query Performance

Mydbops
Feb 8, 2024
15
Mins to Read
All

In our exploration of MongoDB's advanced features, we previously delved into the diverse index types available within the database. However, MongoDB 4.4 introduces a compelling addition to this repertoire: the hidden index. This article serves as an in-depth guide to understanding the nuances of hidden indexes, exploring their utility, and mastering the techniques to seamlessly incorporate them into your MongoDB workflow.

Enhancing Query Performance with Indexes

Indexes are pivotal for efficient query performance in MongoDB. Before diving into hidden indexes, let's revisit the fundamental role indexes play in optimizing data retrieval. Check our detailed blog post for an in-depth understanding of MongoDB indexes.

Hidden Index

Since MongoDB 4.4, it is now possible to conceal indexes from the query plan. This functionality allows for the assessment of the potential impact of dropping an index without executing the actual drop.

Hidden indexes remain invisible to the query planner and are unusable for supporting queries.

Additionally, hidden indexes are included in the results of both listIndexes and db.collection.getIndexes().

Hiding an Index Before Dropping It

Dropping an index without proper evaluation can pose significant risks to your MongoDB server. To mitigate these risks, MongoDB 4.4 introduces a valuable solution: the Hidden Index. This feature enables you to assess the impact of dropping an index without actually executing the drop operation.

Accidentally dropping an index without assessing query performance and identifying a suitable alternative can result in significant issues on the server. To mitigate this risk, the Hidden Index provides a valuable solution, enabling a seamless evaluation of an index's impact on queries without the necessity of deletion and subsequent rebuilding. This ensures performance enhancement without disruption.

Hidden indexes are not utilized to support queries. If you hide an index and observe a substantial negative performance impact, consider retaining and unhiding the index to allow queries to resume using it.

Creating the Hidden Index

To create the hidden index, you must have featureCompatibilityVersion set to 4.4 or greater. However, once hidden, the index remains hidden even with featureCompatibilityVersion set to 4.2 on MongoDB 4.4 binaries.

You can use the createIndex command with the hidden as true to create the hidden index on the collection.

 
db..createIndex( {: 1 }, {hidden: true})
	

Hiding the Existing Index As Hidden

To convert existing indexes into hidden indexes, use the hideIndex() method.

 
db.business.hideIndex({:1},{background:true})
	

We have a couple of different options on how to accomplish this. We can, of course, use db.runCommand() in conjunction with collMod, you have the option of specifying the index name or its key pattern for unhiding the index.

To hide the index by employing the following command, specifying the index key pattern:

 
db.runCommand( {
   collMod: ,
   index: {
      keyPattern : {:1},
      hidden: true
   }} )
	

To hide the index by employing the following command, specifying the index key name:

 
db..unhideIndex( {:1} )
	

Unhiding the Hidden Index

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, you have the option of specifying the index name or its key pattern for unhiding the index.

To unhide an index, we can use the 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

 
db..unhideIndex( {:1} )
	

To unhide the index by employing the following command, specifying the index key pattern:

 
db.runCommand( {
   collMod: ,
   index: {
      keyPattern : {:1},
      hidden: false
   }} )
	

To unhide the index by employing the following command, specifying the index key name:

 
db.runCommand( {
   collMod: ,
   index: {
      name : ,
      hidden: false
   }
} )
	

How it Works

Hiding Indexes

To create a hidden index you simply pass a hidden parameter and set the value to true within the db.collection.createIndex() options argument.

For example, let's assume we have a business collection that stores documents on individual businesses. The documents in this collection may look something like this:

 
{
  _id: ObjectId("6562153589e16b5bbf7ad35a"),
  id: '6984',
  certificate_number: 1017764,
  business_name: 'DELI CORP.',
  date: 'Nov 01 2023',
  result: 'Closed',
  sector: 'Furniture Sales - 242',
  address: {
    city: 'HACKENSACK',
    zip: 10696,
    street: '139TH AVE',
    number: 19142
  }
}
	

Now let's assume we wanted to create a new index on the business_name of the business collection and we wanted it to be hidden by default. To do this, we'd execute the following command:

 
mydb:PRIMARY> db.business.createIndex({business_name:1},{hidden: true})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 2,
	"numIndexesAfter" : 3,
	"commitQuorum" : "votingMembers",
	"ok" : 1,
	"$clusterTime" : {
		"clusterTime" : Timestamp(1703121359, 6),
		"signature" : {
			"hash" : BinData(0,"dLzSuBijQ2gnnBMwRFQRubRNgrc="),
			"keyId" : NumberLong("7253318057697738758")
		}
	},
	"operationTime" : Timestamp(1703121359, 6)
}
	

Let’s see the indexes from the collection, We can notice the index has been hidden: true.

 
mydb:PRIMARY> db.business.getIndexes()
[
	{
		"v" : 2,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_"
	},
	{
		"v" : 2,
		"key" : {
			"business_name" : 1
		},
		"name" : "business_name_1",
		"hidden" : true
	}
]
	

We can also hide the existing index. To do this, we'd execute the following hideIndex() command:

 
mydb:PRIMARY> db.business.hideIndex({certificate_number:1},{background:true})
{
	"hidden_old" : false,
	"hidden_new" : true,
	"ok" : 1,
	"$clusterTime" : {
		"clusterTime" : Timestamp(1703124455, 1),
		"signature" : {
			"hash" : BinData(0,"rfy5oWxlw8N/+bS4/dVxzKbDze8="),
			"keyId" : NumberLong("7253318057697738758")
		}
	},
	"operationTime" : Timestamp(1703124455, 1)
}
	

To hide the existing index. To do this, we can also use the following runCommand():

 
mydb:PRIMARY> db.runCommand( {
...    collMod: "business",
...    index: {
...       name : "result_1",
...       hidden: true
...    }
... } )
{
	"hidden_old" : false,
	"hidden_new" : true,
	"ok" : 1,
	"$clusterTime" : {
		"clusterTime" : Timestamp(1703124391, 1),
		"signature" : {
			"hash" : BinData(0,"hpU1SlCjVYkBy4pas6WFVy12eWc="),
			"keyId" : NumberLong("7253318057697738758")
		}
	},
	"operationTime" : Timestamp(1703124391, 1)
}

	

Let’s see the indexes from the collection, We can notice the index has hidden: true.

 
mydb:PRIMARY> db.business.getIndexes()
[
	{
		"v" : 2,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_"
	},
	{
		"v" : 2,
		"key" : {
			"business_name" : 1
		},
		"name" : "business_name_1",
		"hidden" : true
	},
	{
		"v" : 2,
		"key" : {
			"certificate_number" : 1
		},
		"name" : "certificate_number_1",
		"background" : true,
		"hidden" : true
	},
{
		"v" : 2,
		"key" : {
			"result" : 1
		},
		"name" : "result_1",
		"background" : true,
		"hidden" : true
	}
]
	

Unhiding the Index

The hidden one is actually invisible. It is not considered in the rejected plans. To make it visible again we need to use the unhideIndex() method.

 
mydb:PRIMARY> db.business.unhideIndex({business_name:1},{background:true})
{
	"hidden_old" : true,
	"hidden_new" : false,
	"ok" : 1,
	"$clusterTime" : {
		"clusterTime" : Timestamp(1703125154, 1),
		"signature" : {
			"hash" : BinData(0,"rWau9TSkYhkBgc6WNo1LdzE34jM="),
			"keyId" : NumberLong("7253318057697738758")
		}
	},
	"operationTime" : Timestamp(1703125154, 1)
}
	

To make it visible again we can also use the runCommand() with collMod.

 
mydb:PRIMARY> db.runCommand( {
...    collMod: "business",
...    index: {
...       keyPattern : {"result" : 1},
...       hidden: false
...    }
... } )
{
	"hidden_old" : true,
	"hidden_new" : false,
	"ok" : 1,
	"$clusterTime" : {
		"clusterTime" : Timestamp(1703125384, 1),
		"signature" : {
			"hash" : BinData(0,"/gSzsZEjTe4nsW4tGilZSOk2K44="),
			"keyId" : NumberLong("7253318057697738758")
		}
	},
	"operationTime" : Timestamp(1703125384, 1)
}
	

Behavior Of Hidden Index

Apart from being hidden from the planner, hidden indexes behave like unhidden indexes. In the following scenarios.

  • We can create a hidden index on the single filed and compound index.
  • If a hidden index is a unique index, the index still applies its unique constraint to the documents.
  • If a hidden index is a TTL index, the index still expires documents.
  • Hidden indexes are included in listIndexes and db.collection.getIndexes() results.
  • Hiding an unhidden index or unhiding a hidden index resets its $indexStats.
  • During the writes, MongoDB maintains the hidden indexes the same as any regular index.
  • The hidden indexes are immediately available after unhidden.

Advantages Of Hidden Indexes

  • Using the hidden index before dropping it can be beneficial, especially for large collections. When you need to recreate the index after dropping it, rebuilding it can be time-consuming and lead to application issues.

Disadvantages Of Hidden Indexes

  • Hidden indexes are updated upon write operations to the collection and continue to consume disk space and memory. As such, they are included in various statistics operations, such as db.collection.stats() and $indexStats.
  • You cannot hide the _id index.

Snippet For Finding Hidden Index

The below-mentioned snippet helps you to find the hidden indexes in your cluster. We hope this snippet helps you find the hidden indexes.

 
print("S.No|Database Name|Collection Name|Index Name");
var dbNames = db.getMongo().getDBs();
var serial = 1;
dbNames.databases.forEach(function (database) {
    var currentDb = db.getSiblingDB(database.name);
    var collections = currentDb.getCollectionNames();
    collections.forEach(function (collection) {
        var indexes = currentDb[collection].getIndexes();
        indexes.forEach(function (index) {
            if (index.hasOwnProperty('hidden') && index.hidden === true) {
                print(serial + "|" + currentDb + "|" + collection + "|" + index.name);
                serial++;
            }
        });
    });
});
	

In conclusion, MongoDB's hidden index feature in version 4.4 is a game-changer in database management. This tool allows for a risk-free assessment of index impact before deletion, streamlining performance enhancements. The ability to hide and unhide indexes provides a dynamic approach to optimize query performance without the need for index recreation.

MongoDB's hidden index feature is a user-friendly and powerful tool, empowering administrators and developers to make informed decisions, enhancing efficiency in MongoDB 4.4 and beyond.

Ready to optimize your MongoDB performance? Partner with Mydbops, your trusted open-source database management experts. Explore our comprehensive services, including MongoDB optimization, performance tuning, and advanced consulting. Let's enhance your database efficiency together. Contact Us for a personalized consultation.

{{cta}}

No items found.

About the Author

Mydbops

Subscribe Now!

Subscribe here to get exclusive updates on upcoming webinars, meetups, and to receive instant updates on new database technologies.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.