Hi Team,
I have query and when it is fired form application side it is using one type of index which is present in that collection(I have checked this system.profile collection).
When I execute the same query manually in the database it is picking the another index.
Please explain why this is happening and how it is picking different index for the same query
Output from system.profile:
rs0:SECONDARY> db.system.profile.findOne()
{
"op" : "query",
"ns" : "penguin-consumer.notification_mail_response_logs",
"command" : {
"find" : "notification_mail_response_logs",
"filter" : {
"userId" : NumberLong(91149349),
"profileId" : NumberLong(107020334),
"notificationType" : "recommendedjobs",
"emailStatus" : "SUCCESS"
},
"sort" : {
"createdAt" : -1
},
"projection" : {
"notificationUuid" : 1,
"mailedJobIds" : 1
},
"limit" : 1,
"planSummary" : "IXSCAN { userId: 1, profileId: 1, notificationType: 1, emailStatus: 1 }",
Output of explain plan:
rs0:SECONDARY> db.notification_mail_response_logs.find(
... {
... "userId": NumberLong(33965519),
... "profileId": NumberLong(26164350),
... "notificationType": "recommendedjobs",
... "emailStatus": "SUCCESS"
... },
... {
... "notificationUuid": 1,
... "mailedJobIds": 1
... }
... ).sort(
... {
... "createdAt": -1
... }
... ).limit(1).explain("executionStats").executionStats
{
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 6,
"totalKeysExamined" : 1,
"totalDocsExamined" : 1,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1,
"keyPattern" : {
"userId" : 1,
"profileId" : 1,
"createdAt" : -1
},
"indexName" : "userId_1_profileId_1_createdAt_-1",