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

Mongo index on implicit/explicit $and not consistent

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Minor - P4 Minor - P4
    • 2.5.3
    • Affects Version/s: 2.4.6
    • Component/s: Querying
    • Labels:
    • Environment:
      Windows 64bit, 2.4.6.
    • ALL
    • Hide 
      // Populate
db.test.insert({_id:1,Fields:{K1:123,K2:456}})
db.test.insert({_id:2,Fields:{K1:456,K2:123}})

// Index on fields of subdocument
db.test.ensureIndex({"Fields.K1": 1})
db.test.ensureIndex({"Fields.K2": 1})...

// Execute some queries
db.test.find({_id: {$lt: 20}, "$or": [{"Fields.K1": 123}, {"Fields.K2": 123}]}).explain()
db.test.find({$and: [{_id: {$lt: 20}}, {"$or": [{"Fields.K1": 123}, {"Fields.K2": 123}]}]}).explain()
      Show
      // Populate db.test.insert({_id:1,Fields:{K1:123,K2:456}}) db.test.insert({_id:2,Fields:{K1:456,K2:123}}) // Index on fields of subdocument db.test.ensureIndex({ "Fields.K1" : 1}) db.test.ensureIndex({ "Fields.K2" : 1})... // Execute some queries db.test.find({_id: {$lt: 20}, "$or" : [{ "Fields.K1" : 123}, { "Fields.K2" : 123}]}).explain() db.test.find({$and: [{_id: {$lt: 20}}, { "$or" : [{ "Fields.K1" : 123}, { "Fields.K2" : 123}]}]}).explain()

      Mongo allegedly uses an implicit AND when quering comma-separated fields of the form:

      coll.find({k1:v1, k2:v2})

      Thus the above should be equivalent in all senses to:

      coll.find({$and: [{k1:v1}, {k2:v2}]})

      However, including the explicit $and alters the indexing behaviour of the search. Using the below steps, the explicit $and uses only the _id index, whereas the implicit $and utilises other indexes. Trying to force the explicit $and to use the index that the implicit version is using (with .hint()) does not work.

        1. server11517.js
          1 kB

            Assignee:
            hari.khalsa@10gen.com hari.khalsa@10gen.com
            Reporter:
            mmmdreg George Narroway
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved: