MongoDB: Indexing Design Fundamentals Skill Check - Question 4

For sure you need an index that starts with "userId" as it is a selective equality predicate. Then, for the second key field:

• "dueDate" is great for the first query to avoid a sort, but I guess the task list per user is not that huge so avoiding a sort in memory is not a high priority. It would be different if there were a .limit() for pagination as this would be a selective filter.
• "priority" is good for the second query, especially if it is selective (few "high" priority), to go to a specific range.

So, basically, there's not a single good answer. If you create all indexes, the query planner will choose { userId: 1, dueDate: -1 } for the first query and { userId: 1, priority: 1 } for the second query. If there's only one index to create { userId: 1, dueDate: -1, priority: 1 } is good for the first query and it's advantage for the second query is that it doesn't have to read the document to filter on "priority". I think it was put there to illustrate the Equality, Sort, Range guideline. So it's more an academic answer that is expected.

You answer { userId: 1, dueDate: -1 } is correct but adding "priority" has a little advantage on the second query as it reduces the number of documents fetched because it can filter already on the index entries.

Now, if I was the developer looking for the most optimal index access, and as I guess that there's a limited number of well known priorities, ideally constrained by schema validation, I would create an index on { userId:1, priority:1, dueDate:1} and change the second query to list all priorities:

db.tasks.find({
  userId: "3f9ab41",
  priority: { $in: ["high", "medium", "low"] }
}).sort({ dueDate: -1 })

This reads the three ranges from the index, each sorted on "dueDate", and with a SORT_MERGE on top 🤓

Justification is in the course: indexes should cover fields used in filters and sort, and sort fields must be just after the fields used for equality filter, so only { userId: 1, dueDate: -1, priority: 1 }. Chosing { userId: 1, dueDate: -1} is ok but covering all filters in query 2 is better regarding this.

I like your index at the bottom - as I agree that the cardinality of priority couldn't be very big. It's just odd they'd not provide a justification for their and almost leave it open to picking one or the other.

Thanks for the response.

My mistake, sorry Franck - I meant justification in the exam feedback.

Thanks for pointing that out.