Should I use $in for a performant query in MongoDB?

Question

Consider the following data structure in a collection:

{
_id : ObjectId("4ec6c015482c4c8302000001"),
uid : ObjectId("4ec6c015482c4c8302003233") //reference to user's Object ID
someValue : some json object,
}

Said collection will be sharded on uid.

Between these two situations, which would be more performant for reads?

Option A)

Store References to each data structure in the users object and perform this query:

db.collection.find({_id: {$in: ids}}

Option B)

Create an index on uid and query this way:

db.collection.find({uid : ObjectId("4ec6c015482c4c8302003233")})

each result set will include 0-20 of the data structures from the collection.

Summed up: Will it be faster to find() 20 specific ID's or all objects that match an indexed ID value (the result set is also 20)

DhruvPathak · Accepted Answer · 2011-12-02 05:52:52Z

2

Option (B) is better and straightforward :

i) You do not need to store references of _ids seperately.

ii) If an index exists onuid, your query would be fast,no optimization needed.

iii) Other operations like sort, limit,findOne will work in basic syntax . With option (A),you will have to always fetch whole data and do operations on it .

answered Dec 2, 2011 at 5:52

DhruvPathak

43.4k17 gold badges125 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

misaxi · Accepted Answer · 2011-12-02 06:07:21Z

2

create an index on uid and the answer is simply like this.

      uid    V.S.   _id  _id  _id 
      /|\            |    |    |
  doc doc doc       doc  doc  doc

the more _ids you push into query, the more checks against _id which means extra i/o. and time for fetching document is the same.

answered Dec 2, 2011 at 6:07

misaxi

5662 silver badges11 bronze badges

Comments

rit · Accepted Answer · 2011-12-02 05:21:51Z

0

If you choose Option A) and don't have an index, option B) will be more performant. Even tough if you would have all data completly in memory it is more efficient to use B) (as the application only has to compare one values, and not several).

answered Dec 2, 2011 at 5:21

rit

2,3183 gold badges19 silver badges27 bronze badges

1 Comment

Chris Biscardi Over a year ago

_id has an automatically created index that you can't delete, unless the collection is capped.

Collectives™ on Stack Overflow

Should I use $in for a performant query in MongoDB?

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related