Should I set a compound index or two single field indexes for my operation in MongoDB?

0
votes

So, I have data in the following model in MongoDB:

  1. The default _id
  2. A distint uniq_id (there will be multiple documents for each uniq_id)
  3. A suitable timestamp value, ts for the document

Now, somewhere in my code, I wish to extract the oldest 100 documents for a given uniq_id. So I run this query:

db.students.find({uniq_id:"xyz"}).limit(100).sort({ts:1})

My questions now are:

  1. How does limit() work? Will it use the default _id to get the first 100 documents? In other words, if I have 1100 documents resulting from find(), I know limit(100) is able to extract documents 1-100. Does it do this by making use of the default _id index?

  2. To sort it based on timestamp (again to emphasize that I want the oldest 100 documents), do I need to set a compound index on both uniq_id and timestamp or is it better to create two single indexes on uniq_id and timestamp separately?

I am of the assumption that two single field indexes will do the job. Can someone help me out with this?

Thanks in advance.

1
mongodbindexingnosql

1 Answers

0
votes
  1. limit(n) would return the first n results returned by the database. In your example query, the result is sorted by ts. When this is put through the query optimizer, MongoDB will first sort according to the given sort key and then limit the results.

  2. You should use a compound index. But remember, order matters. So, as for your example, the compound index should be created

    db.students.ensureIndex({uniq_id:1,ts:1})

  3. Use .explain() to find out what suits you best.

HTH.