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

Mongo index on implicit/explicit $and not consistent

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor - P4
    • Resolution: Fixed
    • Affects Version/s: 2.4.6
    • Fix Version/s: 2.5.3
    • Component/s: Querying
    • Labels:
    • Environment:
      Windows 64bit, 2.4.6.
    • Operating System:
      ALL
    • Steps To Reproduce:
      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()

      Description

      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.

        Attachments

          Activity

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: