Forcing index usage

We can force MongoDB to use an index by applying the hint() parameter:

> db.books.createIndex( { isbn: 1 }, { background: true } )
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 8,
"numIndexesAfter" : 9,
"ok" : 1
}

The output from createIndex notifies us that the index was created ("ok" : 1), no collection was automatically created as a part of index creation ("createdCollectionAutomatically" : false), the number of indexes before this index creation was 8, and now there are nine indexes for this collection, in total.

Now, if we try to search for a book by isbn, we can use the explain() command to see the winningPlan sub-document, where we can find which query plan was used:

> db.books.find({isbn: "1001"}).explain()

"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"isbn" : 1,
"name" : 1
},
"indexName" : "isbn_1_name_1",
...

This means that an index with isbn is 1 and name is 1 was used instead of our newly created index. We can also view our index in the rejectedPlans sub-document of the output, as follows:


"rejectedPlans" : [
{
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"isbn" : 1
},
"indexName" : "isbn_1",
...

This is, in fact, correct, as MongoDB is trying to reuse an index that is more specific than a generic one.

We may not be sure though in cases where our isbn_1 index is performing better than the isbn_1_name_1 one.

We can force MongoDB to use our newly created index, as follows:

> db.books.find({isbn: "1001"}).hint("international_standard_book_number_index")
.explain()
{
...
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"isbn" : 1
},
...

Now, the winningPlan sub-document contains our index, isbn_1, and there are no rejectedPlans elements. It's an empty array in the result set.

We cannot use hint() with the special type of text indexes.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset