Mongodb 4 poor performance in indexed fields

Viewed 19

Please lemme know why the performance is so poor in aggregation query in my examples Query 1, count of records in my audio_details collection is around 3M+ and sample records are like :

{
   _id : xxx,
    status : 'BUY', /* This is indexed field */
    active : 't', /* This is indexed field */
    created_date2 : "2022-09-23T09:00:00.000Z", /* This is indexed field */

    audio_details : [
      {id : 123 /* This is indexed field */ , created_date : "2022-XXX", /* Other fields goes here */},
      {id : 124 /* This is indexed field */ , created_date : "2022-XXX", /* Other fields goes here */},
          ...

    ],
    /* Other 60 fields goes here */
}

Query 1: This is very slow (300 s)

db.audio_details.aggregate([{$match : { status : 'BUY',active: 't','audio_history.id' : {$in: [123]}}}, {$sort : {created_date2 : -1}}]);

Query 2: This is very fast (0.5 s)

db.audio_details.find({ status : 'BUY',active: 't','audio_history.id' : {$in: [123]}
}).sort({created_date2 : -1})

Please share why the query 1 is slow Regards Kris

0 Answers
Related