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

Compound find with sort picks the wrong index

    XMLWordPrintableJSON

Details

    • Icon: Bug Bug
    • Resolution: Incomplete
    • Icon: Major - P3 Major - P3
    • None
    • 2.6.5
    • Querying
    • 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

    Description

      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.

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved: