MongoDB Indexing - How many fields should I index?

Question

I have a Collection in my db, called Post.

Here's how it looks:

Post {
 user: <EmbeddedObject>,
 school: <EmbeddedObject>,
 hashtag: <EmbeddedObject>,
 numberOfReports: <Number>,
 viewRanking: <Number>,
 type: <String>,
 isPollOfTheDay: <Bool>,
 createdAt: <Date>,
 endsAt: <Date>
}

(Obviously it also contains other, unrelated fields)

So this Post collection, is being queried by 5 different screens in my app. School, Hashtag, All Polls, Discover & Profile. All queries are very similar to each other, but they differ.

Let's have a look at them individually:

School

Here, I have 2 queries

1. I compare by school.name (equal to), numberOfReports (less than), user.id (for blocks checking (not contained in)) and lastly, we sort in descending order either by createdAt or viewRanking (depends on the user)

2. I compare by isPollOfTheDay (equal to true) and endsAt (greater than or equal to)

Hashtag

I compare by hashtag.id (equal to), numberOfReports (less than), user.id (for blocks checking (not contained in)) and lastly, we sort in descending order either by createdAt or viewRanking (depends on the user)

All Polls

I compare by type (equal to), isPollOfTheDay (equal to false), numberOfReports (less than), user.id (for blocks checking (not contained in)) and lastly, we sort in descending order either by createdAt or viewRanking (depends on the user)

Discover

I compare by school.name (not equal to), type (equal to), numberOfReports (less than), user.id (for blocks checking (not contained in)) and lastly, we sort in descending order by createdAt

Profile

I compare by user.id (equal to) and we sort in descending order by createdAt

These are all of my queries! I can say that they are called almost with the same frequency. My question is, should I just index all 9 fields? Are they too many to index? Should I ignore the isPollOfTheDay field, since it's a Boolean? (I've read that we shouldn't index Booleans)

EDIT: Every document occupies about 200 bytes. We currently have 25K documents and growing in a pace of ~300/day. The only fields that can change, are viewRanking and numberOfReports where the first one will change often, whereas the second far less often!

The selectivity of each query is high (I think), since the needed documents are found mainly by their first comparison. There are about 50 different school.names & another 50 hashtag.ids.

• Sotiris Kaniras: Version is v4.0.2

Answer 1

It seems that your collection is quite small (5MB?). I would create one index for each query and check execution plans. The limit of creating too many indexes are :

• If your indexes are too big and don't fit in RAM (I suppose you have several GB of RAM ?)
• It increases I/O during documents insert/update because each time you write a document, indexes are also updated.

Also, indexes are necessary when sort operation cannot fit in memory. There is a limit of 32MB, so here again you are below this limit.

Please refer to indexing strategies for more details.

• Sotiris Kaniras: I would create one index for each query - you mean a compound index, including all of the fields I mentioned?
• Sy10100: @SotirisKaniras no I mean one by query. You can try to create a first index on {user.id:1, createdAt: -1} then make an explain plan on each query to check if the index is used, how many documents/keys are examined and see if the winning plan looks good !