Mongo query isn't using the right index as per ESR rule-CodePudding

Below query has been executed in Mongo

  "command": {
    "getMore": 7229634113631845000,
    "collection": "data",
    "batchSize": 4899,

.......


  "originatingCommand": {
    "find": "data",
    "filter": {
      "accountId": "AAA-367YTGSA",
      "customIterator": {
        "$gte": {
          "$date": "2072-11-05T01:41:58.041Z"
        }
      },
      "startTime": {
        "$lte": {
          "$date": "2022-12-06T17:00:00Z"
        }
      },
      "type": {
        "$in": [
          "TYPE_A",
          "TYPE_B"
        ]
      }
    },
    "sort": {
      "accountId": 1,
      "customIterator": 1
    },
    "limit": 5000,
    "maxTimeMS": 300000,

.....



  "planSummary": [
    {
      "IXSCAN": {
        "accountId": 1,
        "customIterator": 1,
        "startTime": 1,
        "type": 1
      }
    }
  ],

I have two indexes as below:

First Index:

accountId_customIterator_startTime_type
accountId:1 customIterator:1 startTime:1 type:1

Second Index:

accountId_type_customIterator_startTime
accountId:1  type:1  customIterator:1  startTime:1

As per my understanding, the query should be using the second Index as per ESR rule but planSummary states the story otherwise.

"planSummary": [
    {
      "IXSCAN": {
        "accountId": 1,
        "customIterator": 1,
        "startTime": 1,
        "type": 1
      }

What I am missing here?

CodePudding user response：

Both the index have same fields and your filter also has same fields. So, either of the index can be used.

But, you also have a sort and it's shape matches the first index. So, it uses the first index to help in sorting. Second index will not support sorting in this case.

This is exactly in line with the ESR rule.