0

I have the following query

db.getCollection("order_error").find({
   "$and":[
      {
         "type":"orderResult"
      },
      {
         "Origin.SN":{
            "$in":[
               "5701097",
               "5701099"
            ]
         }
      },
      {
         "productLowerCase":{
            "$regex":"capp",
            "$options":"i"
         }
      }
   ]
}).sort({"timestamp.milliseconds" : -1}).skip(1).limit(100).explain("executionStats")

And the index this query is using is

type_1_Origin.SN_1_timestamp.milliseconds_-1

The stages are

IXSCAN (For Origin.SN = 5701099) -> IXSCAN (For Origin.SN = 5701097) -> SORT_MERGE -> FETCH -> SKIP -> LIMIT

I can see that the sorting is also happening with index so that part is optimised. But the issue is the FETCH stage. There the filter is as follows

          {
             "productLowerCase":{
                "$regex":"capp",
                "$options":"i"
             }
          }

This part is not optimised. Even if I create an index like productLowerCase_1 and change the query to use the left anchored regex also that index is not even getting used in FETCH stage. Does MongoDB use only one index in a query? Is there any way I can make it use the productLowerCase_1 index along with type_1_Origin.SN_1_timestamp.milliseconds_-1 index in its queries?

Jithin Variyar
  • 21
  • 6
  • Do you have this index? `{type:1, "Origin.SN":1, productLowerCase:1, "timestamp.milliseconds":-1}` Note: if `Origin` and `timestamp` are arrays, you can't create the index and you need to think to change your data structure – Valijon Dec 22 '21 at 10:20
  • @Valijon Currently, I don't have the index that you specified. And Origin.SN is an array field. timestamp.milliseconds is not – Jithin Variyar Dec 22 '21 at 10:22
  • Create this index and MongoDB should use it – Valijon Dec 22 '21 at 10:22

0 Answers0