Separate indexes versus compound indexes

Working with Data
1

I have a collection called ‘articles’ with the following fields (among others):

  • authorIds (an array)
  • serialId (string)
  • sourceId (string)
  • published (a number / timestamp)

I currently already have these indexes:
{ v: 2, key: { authorIds: 1, published: 1 }, name: ‘authorIds_1_published_1’ }
{ v: 2, key: { sourceId: 1, published: 1 }, name: ‘sourceId_1_published_1’}

But now the need arises for another compound index:
{ v: 2, key: { serialId: 1, published: 1 }, name: ‘serialId_1_published_1’}

I am wondering at this point if its more efficient to just have an index on the published, sourceId, serialId and authorIds seperately instead of having 3 compound indexes?

2

Nobody can know for sure what is the best for your data size, traffic and use-cases. Only you can determine the best performing indexes based on your query patterns. It simply depends on too many factors. For example, if for a given serialId you only have 1 published value, have published in the index is kind of useless for performance, but the size penalty will be so small that you might as well have it especially if you query on serialId equality and project on published. Not that documents need to be fetched if a queried,sorted or projected field is not in the index; so if you have really big documents you would want to avoid fetching.