OPERATIONAL DEFECT DATABASE
...
...
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.
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});
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
Click on a version to see all relevant bugs
MongoDB Integration
Learn more about where this data comes from
Bug Scrub Advisor
Streamline upgrades with automated vendor bug scrubs
BugZero Enterprise
Wish you caught this bug sooner? Get proactive today.
