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:

1. IXSCAN on {"field_id":1, "anotherField":1} index, yielding 500k keysExamined, which are the 500k documents that have field_id = 'abcd'
2. FETCH the rest of the filter, returning 20k as count

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

CodePudding user response：

Mongo can sometimes uses the wrong index, I recommend you read my answer here which explains why this could happen.

In order to "fix" the behavior you should use $hint, it will force Mongo to use the specific index you specify.

CodePudding user response：

Does this index

{"field_id":1, "anotherField":1}

work for most queries against your db?

In my experience, MongoDB will often choose a very generic index even if sometimes a more specific index is better. I fixed this by making the indexes more specific to their uses (and removing "catch-all" indexes). I don't know whether this applies to your use case because the reasoning for the chosen index isn't known.