0

I have the following index

Origin.SN_1_timestamp.milliseconds_-1_error_1

And I am running the following query

db.getCollection("error").aggregate([
{
   "$match":{
      "$and":[
         {
            "error":{
               "$ne":"No Error"
            }
         },
         {
            "timestamp.milliseconds":{
               "$gte":1617561000000
            }
         },
         {
            "timestamp.milliseconds":{
               "$lte":1617627557579
            }
         },
         {
            "Origin.SN":{
               "$in":[
                  "4090",
                  "4080"
               ]
            }
         }
      ]
   }
},
{
   "$group":{
      "_id":"$error",
      "count":{
         "$sum":1
      }
   }
},
{
   "$project":{
      "_id":0,
      "count":"$count",
      "error":"$_id"
   }
},
{
   "$sort":{
      "count":-1
   }
},
{
   "$limit":1
}
])

You can see that the order of the composite index is reversed in the query. Even though this query uses the above index in IXSCAN. Is there any performance loss in this? Do I need to create another compound index in reverse order for a better performance?

Jithin Variyar
  • 21
  • 6
  • Have a look at https://docs.mongodb.com/manual/applications/indexes/ the index is used in the $match stage, I would say you don't loose anything. – Wernfried Domscheit Dec 30 '21 at 08:08

1 Answers1

0

I think the performance is not affected by the order of the index but you can explain the query and check the execution time:

Return Information on Aggregation Pipeline Operation

zelmario
  • 96
  • 5