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