MongoDB 2.6.7 not using expected index when $in used

[Astro]

I have a query that uses index when user_id is single value. The same query does not use index when user_ids passed as array with $in.

Index: {user_id:1,updated_at:-1 }

Following is the sample query that uses index:

{  

     name: "a",

     version:"2.0"

     user_id: 'user1',

}.sort({updated_at:-1})

But this query does not use index:

{  

     name: "a",

     version:"2.0"

     user_id: {$in:['user1','user2', 'user3']}

}.sort({updated_at:-1})

It uses another index {name:1, version:1, updated_at:-1} which is not effective in this case.

Any clues?

Thanks!

[Kevin Adistambha]

Hi,

Since in both queries either index can be used (e.g. both queries contain the fields which are present in both indexes), MongoDB will choose one of the index which can provide a higher selectivity for the query. If you think the selected index is sub-optimal, you can force a particular index to be used by using the hint() method. For example:

db.collection.find(...).sort(...).hint({user_id:1, updated_at:-1})

will force the query planner to use the {user_id:1, updated_at:-1} index.

To experiment, you can append the explain() method to the end of the query (e.g. db.collection.find(...).sort(...).hint(...).explain()). This way, you can check the selectivity of different indexes with regard to the query by noting the nscannedObjects field (which shows how many documents MongoDB needs to examine to respond to the query). The lower this number is, the more selective the index is for the query.

You may find these links useful:

• explain() output
• How the query optimizer chooses an index

Also, I would recommend you to upgrade to the latest version in the 2.6 series, which is currently 2.6.12 for improvements and bugfixes.

Best regards,
Kevin

​

[Astro]

Thanks, Kevin.

When used explain(), I see the index being used in less selective than that of other.

When queried using hint with index {user_id:1,updated_at:-1 } ====> "nscannedObjects" : 24837 

When queried using hint with index {name:1, version:1, updated_at:-1} ===> "nscannedObjects" : 36780

The second index {name:1, version:1, updated_at:-1}  is used despite being less selective. 

Any thoughts?

[Kevin Adistambha]

Hi,

When used explain(), I see the index being used in less selective than that of other.

When queried using hint with index {user_id:1,updated_at:-1 } ==⇒ “nscannedObjects” : 24837
When queried using hint with index {name:1, version:1, updated_at:-1} =⇒ “nscannedObjects” : 36780

The second index {name:1, version:1, updated_at:-1} is used despite being less selective.

The main issue is that there are two candidate indexes that can be used to satisfy your query. In MongoDB 2.6 the index was chosen by running the query against several candidate indexes (in parallel), and in your case, the first index that can return 101 results earlier was chosen. Therefore, the query planner does not know the selectivity of the index in advance. The nScannedObjects numbers are computed after the fact, hence it is possible that the selected index happened to be less selective compared to another, similar index. This index selection steps are described in detail in Query Optimization

If you find that the index selection is sub-optimal, the hint() operator could be used to force MongoDB to use a specific index that you know is more selective.

Best regards,
Kevin

​