I tried the query using Mongo compass and Mongoose (NodeJS) and the index quizzes.done
is NOT used even though a multikey index should be created as you say.
Take a look at the following screenshots for confirmation:
https://i.ibb.co/JvXmxr9/Screen-Shot-2020-08-19-at-11-18-43-PM.png
https://i.ibb.co/WDxxcZC/Screen-Shot-2020-08-19-at-11-18-51-PM.png
If I modify the query to be like
Answer.find({ 'quizzes.done': true }).explain('queryPlanner');
The index gets used.
However the fastest index that works that I found is quizzes.0.done
but this is not dynamic enough.
This means I would have to create quizzes.1.done
quizzes.2.done
…etc which does not make sense.
Thanks!