0

My collection, consisting of 20 mil docs, looks like this:

{
  "_id" : ObjectId("5f76a5588a93be772c14cc0f"),
  "list_id" : ObjectId("5f76a53e8a93be772c14cc06"),
  "email" : "email1@yahoo.com",
  "date_created" : ISODate("2020-10-02T03:58:16.739Z")
}
{
  "_id" : ObjectId("5f76adb88a93be772c14cc3e"),
  "list_id" : ObjectId("5f76a53e8a93be772c14cc06"),
  "email" : "email2@yahoo.com",
  "date_created" : ISODate("2020-10-02T03:59:11.229Z")
}
{
  "_id" : ObjectId("5f76adb88a93be772c14cc38"),
  "list_id" : ObjectId("5ed93f71c130ea5378977d4f"),
  "email" : "email3@yahoo.com",
  "date_created" : ISODate("2020-10-02T06:13:08.120Z")
}
...

I have 2 separate indexes created for list_id and date_created. Both are ascending.

The majority of the queries I run against this collection include querying list_id and date_created at the same time. E.g.

db.collection
  .find({
     list_id: ObjectId('608af31fb1d9113db9bbd069'),
     date_created: {$gte: new Date('2021-04-30T07:00:00Z')}
  })

My understanding is MongoDB will use only one of the above indexes depending on which one it determines to be more optimal.

My question is if I had a compound index consisting of list_id and date_created, would the above query run faster than just using one of the mentioned indexes?

codemonkey
  • 7,325
  • 5
  • 22
  • 36
  • yes the compound index is faster and occupies less memory than 2 indexes, see similar question [MongoDB Find performance: single compound index VS two single field indexes](https://stackoverflow.com/questions/47893613/mongodb-find-performance-single-compound-index-vs-two-single-field-indexes) – turivishal May 18 '21 at 17:04
  • @turivishal Does it make a difference which way to index `date_created` (asc or desc) inside the compound index if the majority of the queries are for the most recent dates? – codemonkey May 18 '21 at 17:09
  • 1
    its a order of scanning documents in index blocks, if we have specified desc order in index field then it will begin search from most recent index block.. so yes this will search first then ascending order.. – turivishal May 18 '21 at 17:16
  • 1
    see this question as well [How does the order of compound indexes matter in MongoDB performance-wise?](https://stackoverflow.com/questions/33545339/how-does-the-order-of-compound-indexes-matter-in-mongodb-performance-wise) – turivishal May 18 '21 at 17:26

0 Answers0