Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-16145

Compound find with sort picks the wrong index

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Incomplete
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 2.6.5
    • Component/s: Querying
    • Labels:
      None
    • ALL
    • Hide

      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
      Show
      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

      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.

            Assignee:
            Unassigned Unassigned
            Reporter:
            petrbela Petr Bela
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: