Search code examples
mongodbquery-optimization

MongoDB Query Not Using Expected Index

I have a query {discontinued:0, status:1, visibility:4, "out_of_stock_backend_item.id":{$ne:12524}}

I'm expecting it to use the index

discontinued:-1
status:1
visibility:1
out_of_stock_backend_item.id:1

But somehow the above index is coming in the rejected plan and the one in the winning plan is

discontinued:-1
status:1
visibility:1
entity_id:1

How I can make it use the correct index?

Solution

  • You can use the hint method, as the documentation said:

    "hint" force MongoDB to use when performing the query. Specify the index either by the index name or by the index specification document.

    db.collection.find().hint( { field: 1 } );

    OR using the index name

    db.collection.find().hint( "index_name" )