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

Mongo index on implicit/explicit $and not consistent

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Minor - P4
    • Resolution: Fixed
    • 2.4.6
    • 2.5.3
    • Querying
    • 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()

    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

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

            Dates

              Created:
              Updated:
              Resolved: