I've got hundreds of thousands of documents like this :
{
"field_id" : "abcd",
"aField" : 0,
"parentList": [
{
"field": "value1",
"list": ["element1,element2"]
}
,
{
"field": "value2",
"list": ["element1, element3"]
}
]
}
(this is an oversemplified version of a much bigger document in my db, containing more fields. The DB contains millions of documents). Here's the filter that I want to use for a count execution:
{ 'parentList.0.list':
{ '$in':
[ 'element1',
'element2',
'element3',
'element4'
]
},
aField: { '$ne': 1 },
field_id: { '$in': [ 'abcd' ] }
}
What I'm trying to do is to create an index like this:
{"field_id" : 1, "parentList.list" :1, "aField" : 1}
and let the query use it. But mongo is actually ignoring it. Instead, mongo is using another index, which is
{"field_id":1, "anotherField":1}
The execution stats shows this staging:
Of course, if mongo used the correct index, I'd expect it to retrieve already the 20k documents from the IXSCAN, or at least a number closer to that.
I just can't understand why mongo is not using that index. I've also tried to change the order of the fields in the index, with no success. I'm using Mongo 4.4.6
So this was actually easy to solve. MongoDB Compass wouldn't let me create an index on a certain position of array (in this case parentList.0.list), because when I tried to insert it, it automatically replaced it with parentList.list.
Eventually, I found out that an index on a certain position of an array is actually doable, if done in the shell. So I created the index on parentList.0.list and the index worked fine.