How indexing works for the combination of $and and $or

[Faiz_ul_haque]

Hello Community,

I have one query which is the combination of $and and $or.

e.g.

db.collection.find({"$and": [
  {
    "Obj.status": {
      "$ne": "REJECTED"
    }
  },
  {
    "show": {
      "$ne": false
    }
  },
  {
    "priority": {
      "$ne": true
    }
  },
  {
    "$or": [
      {
        "pc": {
          "$exists": false
        }
      },
      {
        "pc": {
          "$in": [
            "VALUE 1",
            "VALUE 2",
            "VALUE 3"
          ]
        }
      }
    ]
  },
  {
    "$or": [
      {
        "pcv": {
          "$exists": false
        }
      },
      {
        "pcv": "VALUE 4"
      },
      {
        "pcv": "VALUE 5",
        "pcvs": {
          "$ne": "VALUE 6"
        }
      },
      {
        "pcv": "VALUE 7"
      }
    ]
  },
  {
    "$or": [
      {
        "uo": {
          "$exists": false
        }
      },
      {
        "uo": {
          "$elemMatch": {
            "status": "COMPLETED"
          }
        }
      },
      {
        "isLS": true,
        "isLSO": true,
        "isDeleted": {
          "$ne": true
        }
      }
    ]
  },
  {
    "category": {
      "$exists": false
    }
  },
  {
    "type": {
      "$in": [
        "V",
        "I"
      ]
    }
  }
]
})

Above query is just a sample, actually I have 7 $or sections inside the $and.

I saw that there is a document available for $or query, and it’s recommended to have separate index on each of the columns used in $or.
Ref: https://www.mongodb.com/docs/manual/reference/operator/query/or/#or-clauses-and-indexes

I have few questions here:

1. As you can see there are multiple $or sections, so should I create a separate index for each attributes used in multiple $or?
2. How should I treat the $and?
3. Is the ordering of the multiple $or section matters?
4. Is the ordering of the attributes inside the $or section matters?

Any help/tip to improve and index creation would be highly appreciated.

Thank you,
Faiz

[Faiz_ul_haque]

Any help or suggestion?

[Asya_Kamsky]

Are any of these filters selective? If not then I’m not sure you’ll be able to create a good index.

The docs are referring to top level $or clause where if your query is

{$or: [ 
    { something }
    { something-else }
]]

then having separate indexes on something and something-else can both be used since $or is a union of those two results sets. What you have is a complex $and with some of the branches being $or groups. The best thing you can do is figure out which of the top level conditions are most selective and index those. Unfortunately they all seem to be range queries - if some of them are point queries then you can more easily combine them into a compound index.

Asya

[Faiz_ul_haque]

Thank you @Asya_Kamsky

Can you please help me understand what do you mean by “selective filters”?

All of the $or groups always present in the query. Some of them have $in query where value can be changed. But attribute will be there.

[Asya_Kamsky]

When you have a large number of documents a selective filter would narrow them down to very few, a less selective filter might match a majority of them.

[Faiz_ul_haque]

Got your point. Thanks @Asya_Kamsky

Unfortunately, I don’t have such attrs/filter in the that query.