mongoDB not picking index-CodePudding

I have a mongoDB query as below.

    db.mobiles.find({
  $or: [
    {
      status: "roaming"
    },
    {
      status: "local"
    },
    {
      inUse: true
    },
    {
      available: true
    },
    {
      color: true
    }
  ],
  updatedAt: {
    $lte: 1639992579831
  }
})

I have created index as below

db.mobiles.createIndex( { “status” : 1 ,   “inUse” : 1 ,  “available” : 1 ,  “color” : 1 , “updatedAt” :  -1} )

When i do explain() , i don't see index getting used. Am i doing something wrong. ?

I can see index got created when i execute db.mobiles.getIndexes()

CodePudding user response：

https://docs.mongodb.com/manual/core/index-compound/#prefixes reads:

For a compound index, MongoDB can use the index to support queries on the index prefixes.

db.mobiles.createIndex( { “status” : 1 ,   “inUse” : 1 ,  “available” : 1 ,  “color” : 1 , “updatedAt” :  -1} )

is a compound index, so the only query that can benefit from it is the one that has at least "status" predicate.

Your query does have it, but in the $or statement, means you are happy to select documents with either status as long as at least 1 other $or condition matches, e.g. colour. In this case mongo cannot use the field to search in the index.

This is how it looks in the explain() output:

"parsedQuery": {
  "$and": [
    {
      "$or": [
        {
          "$or": [
            {
              "available": {
                "$eq": true
              }
            },
            {
              "color": {
                "$eq": true
              }
            },
            {
              "inUse": {
                "$eq": true
              }
            }
          ]
        },
        {
          "status": {
            "$in": [
              "local",
              "roaming"
            ]
          }
        }
      ]
    },
    {
      "updatedAt": {
        "$lte": 1639992579831
      }
    }
  ]
},

But it's not the whole story. The query planner analyses such parameters as index selectivity. Considering it's a boolean, there are not much options, and with normal distribution it must be like half of the collection matches the criteria. There is no much benefits of using index in this case.

Considering the query, the only meaningful index would be by updatedAt:

db.mobiles.createIndex( { “updatedAt” :  -1} )