1

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.

0

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.

  • I would create one index for each query you mean a compound index, including all of the fields I mentioned? – Sotiris Kaniras Jan 10 at 14:54
  • 1
    @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 ! – Sy10100 Jan 10 at 19:17

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.