We are using MongoDb with php yii2. There are more than 5000K records in a single collection.
We have following index in MongoDb:
variable1: 1,
variable1: 1, date_time:-1
variable1: 1, variable2:-1
when we execute
db.getCollection('XXX').find({variable1: 1},{ date_time: { $gte: "2015-10-10" } }).sort(date_time : -1)
it takes more than 5 seconds to display the output. we are using variable2 in another query. I would like to know why the query is taking long 5 second to fetch the result from Database as we are doing query on index fields
Query takes the time to fetch the result for the first time only
When I do the same query second time it gives result in milliseconds