1

I have the following collection:

{
   _id: 12345,
   quizzes: [
     { 
        _id: 111111,
        done: true
     }
   ]
},
{
   _id: 78910,
   quizzes: [
     { 
        _id: 22222,
        done: false
     }
   ]
}

I want to select the documents where a certain quiz from the quizzes was done and want to make sure that it uses the appropriate index. So I use the following query:

Answer.find({ 'quizzes.0.done': true }).explain('queryPlanner');

Which returns:

queryPlanner: {
  plannerVersion: 1,
  namespace: 'iquiz.answers',
  indexFilterSet: false,
  parsedQuery: { 'quizzes.0.done': [Object] },
  winningPlan: { stage: 'COLLSCAN', filter: [Object], direction: 'forward' },
  rejectedPlans: []
}

The query is not using any index as seen from the output. I have tried the following indexes and none get used:

{ quizzes.done: 1 }
{ quizzes.[$**].done: 1 }
{ quizzes: 1 }

The only 1 that actually gets used:

{ quizzes.0.done: 1 }

However this is not really practical as I may target any quiz from the quizzes array not just the first one. Is there a certain syntax for the index in my case or this is a current limitation of mongodb? Thanks!

4
  • None of the indexes you stated you created match the syntax stated in docs.mongodb.com/manual/core/index-wildcard as far as I can tell.
    – D. SM
    Aug 19, 2020 at 18:55
  • I tried the wildcard quizzes.$** which works but makes the query significantly slower than without it. I would rather want a wildcard on the array index somethinglike { quizzes.[$**].done: 1 } as it is fast enough but this is wrong syntax i guess Aug 19, 2020 at 19:06
  • I do not understand what you are asking then. You used invalid syntax and your index isn't used for your queries. Seems like expected behavior to me.
    – D. SM
    Aug 19, 2020 at 20:11
  • That's just it tho, what is wrong on the syntax that I'm using? I'm frustrated and feel like I'm missing something obvious. What should I try next? Aug 19, 2020 at 20:58

1 Answer 1

2

When MongoDB indexes an array, or a field in an array of subdocuments, the index is essentially a list of value -> recordId pairs.

The value part is the actual field value, and does not include any information about the index of the value within the array.

The query executor's index scan matches the queried value with the indexed values and returns a list of recordIds that then need to be fetched.

If you have additional requirements, such as an elemMatch clause or require the value to be in a specific position in the array, the documents will have to be examined after the index scan.

This means that if you are using an index on {"quizzes.done":1} to match "quizzes.0.done": true, the executory would need to retrieve all documents that contain at least 1 quiz that is done, and examine each to determine if it is the first index. In that case, a collection scan is quite likely more efficient.

You noted that the index on {"quizzes.0.done":1} works for that query, but that is because the query executor can guarantee the location of the match within the array with that index, and be certain that no other array element will match.

I'm not certain how the { quizzes.[$**].done: 1 } index might be handled, but I suspect it won't look much different than { quizzes.done: 1 }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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