CODEWITHSUNDEEP
djangomongodbmongoengine
vadimb
vadimb

Reputation: 472

Mongodb whats faster $in or $or?

Which query will be faster? Len of ObjectId list can be more than 2.

db.house.find(
    { persons:
        { $elemMatch:
            { person_id:
                { $in:
                    [ ObjectId("570028671d41c8eaeb6c9ce8"), 
                      ObjectId("570028681d41c8eaeb6c9e38")
                    ]
                 }
             }
         }
     })

db.house.find(
    { $or:
        [{ persons:
            {$elemMatch:  
                {person_id:ObjectId("570028671d41c8eaeb6c9ce8")}}
         }, 
         {persons:{$elemMatch:              
                {person_id:ObjectId("570028681d41c8eaeb6c9e38")}}
         }]
    })

I have indexes for: persons, persons.person_id. I making queries from Django (mongoengine) if it does matter. There is about 100k houses and 2k persons in my db for now. House documents are like that:

{
    "_id" : ObjectId("570031aa1d41c8ed54393b19"),
    "persons" : [
        {
            "person_id" : ObjectId("570028671d41c8eaeb6c9dff"),
            "t" : "150 t"
        },
        {
            "person_id" : ObjectId("5700312d1d41c8ed54393b05"),
            "t" : "1 g"
        },
        {
            "person_id" : ObjectId("5700312d1d41c8ed54393b06"),
            "t" : "70 y"
        }
    ]
}

Upvotes: 1

Views: 309

Answers (1)

JohnnyHK
JohnnyHK

Reputation: 312035

As mentioned in the documentation, $in should be used instead of $or whenever possible.

But you also don't need to use $elemMatch as you're directly matching against a single field, so you can simplify your query to:

db.house.find(
    { 'persons.person_id':
        { $in:
            [ ObjectId("570028671d41c8eaeb6c9ce8"), 
              ObjectId("570028681d41c8eaeb6c9e38")
            ]
        }
    })

Upvotes: 4

Related Questions