Info
When finding a document using multiple attributes while specifying a sort, instead of using the compound index for the find, the planner uses the sort's index instead. This is despite the fact that the query actually only has one result.
Strangely enough, when explain() is run on the query, it chooses the correct index.
Also, by using a hint the optimal index can be enforced.
Top User Comments
jon.linn@caringbridge.org commented on Fri, 16 Feb 2018 18:54:22 +0000:
Is there any more info on how this was (partially?) resolved and in what versions it was resolved?
rassi@10gen.com commented on Fri, 14 Nov 2014 16:30:29 +0000:
Could you run the following the next time you're able to reproduce the issue of the _id index being picked, and paste the output into a comment on this ticket?
db.getSiblingDB("socialdb_prod").accounts.getPlanCache().getPlansByQuery({service: 1, uid: 1}, {}, {_id: 1});
db.getSiblingDB("socialdb_prod").accounts.getPlanCache().clear(); // Clears the query plan cache for this collection.
db.getSiblingDB("socialdb_prod").accounts.getPlanCache().getPlansByQuery({service: 1, uid: 1}, {}, {_id: 1});
Steps to Reproduce
With the following indexes:
> db.accounts.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "socialdb_prod.accounts"
},
{
"v" : 1,
"unique" : true,
"key" : {
"service" : 1,
"uid" : 1
},
"name" : "service_1_uid_1",
"background" : true,
"dropDups" : true,
"ns" : "socialdb_prod.accounts"
}
]
Run a search:
db.accounts.find({"service": 0, "uid": "26119042"}).sort({_id: 1})
Uses _id index instead of the obvious
{ service: 1, uid: 1 }
2014-11-13T19:29:57.910+0000 [conn1530188] query socialdb_prod.accounts query: { query: { service: 0.0, uid: "26119042" }, orderby: { _id: 1.0 } } planSummary: IXSCAN { _id: 1 } ntoreturn:0 ntoskip:0 nscanned:3575682 nscannedObjects:3575682 keyUpdates:0 numYields:4 locks(micros) r:8240040 nreturned:1 reslen:334 4285ms
