|
I just tested if an index which satisfies the sort request will be covered as suggested by david.storch. So, either one of the following indexes would do the trick.
{
|
time: 1,
|
action: 1,
|
userId: 1
|
}
|
The query which is using this index is covered:
> db.act.find(q,p).sort(s).hint(h2).explain(true)
|
{
|
"queryPlanner" : {
|
"plannerVersion" : 1,
|
"namespace" : "test.act",
|
"indexFilterSet" : false,
|
"parsedQuery" : {
|
"$and" : [
|
{
|
"time" : {
|
"$lt" : ISODate("2015-12-28T15:59:52.636Z")
|
}
|
},
|
{
|
"time" : {
|
"$gte" : ISODate("2014-05-15T11:04:18.193Z")
|
}
|
},
|
{
|
"action" : {
|
"$in" : [
|
"ANSAttempt",
|
"GooglePlusAttempt",
|
"TwitterAttempt",
|
"articleLike",
|
"articleShare",
|
"fbMessengerAttempt",
|
"fbShare",
|
"follow",
|
"forumComment_add",
|
"forumPost_add",
|
"forumPost_edit",
|
"like",
|
"objectLike_forumComment",
|
"objectLike_forumPost",
|
"objectLike_userProfileImage",
|
"objectLike_userUpdatedTagline",
|
"objectShare_forumComment",
|
"objectShare_forumPost",
|
"share",
|
"signin",
|
"userUpdatedPicture",
|
"userUpdatedTagline"
|
]
|
}
|
},
|
{
|
"userId" : {
|
"$in" : [
|
ObjectId("5373bcfef88919d05eee3149"),
|
ObjectId("537468fff88919d05eee322c"),
|
ObjectId("537f543778f03be40e598ade"),
|
ObjectId("5382f987832983fb3a5ae9d4"),
|
ObjectId("53a561b2663e61b528241bc4"),
|
ObjectId("53a80878cde71b653ed82539"),
|
ObjectId("53a8cc6511b9cc964959c365"),
|
ObjectId("53acbfb3cb9f89250d02c48a"),
|
ObjectId("53af045ebcc79931692a4aa7"),
|
ObjectId("53b494fb9809bceb3fd25c89"),
|
ObjectId("53b6eae20c4a07fe63db10b3"),
|
ObjectId("53b831b49809bceb3fd2cfab"),
|
ObjectId("53bab1e2b5067de0061a4567"),
|
ObjectId("53bb05e1386b18ce5b20d86b")
|
]
|
}
|
}
|
]
|
},
|
"winningPlan" : {
|
"stage" : "PROJECTION",
|
"transformBy" : {
|
"action" : 1,
|
"userId" : 1,
|
"time" : 1,
|
"_id" : 0
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"time" : 1,
|
"action" : 1,
|
"userId" : 1
|
},
|
"indexName" : "time_1_action_1_userId_1",
|
"isMultiKey" : false,
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : false,
|
"indexVersion" : 1,
|
"direction" : "backward",
|
"indexBounds" : {
|
"time" : [
|
"(new Date(1451318392636), new Date(1400151858193)]"
|
],
|
"action" : [
|
"[\"userUpdatedTagline\", \"userUpdatedTagline\"]",
|
"[\"userUpdatedPicture\", \"userUpdatedPicture\"]",
|
"[\"signin\", \"signin\"]",
|
"[\"share\", \"share\"]",
|
"[\"objectShare_forumPost\", \"objectShare_forumPost\"]",
|
"[\"objectShare_forumComment\", \"objectShare_forumComment\"]",
|
"[\"objectLike_userUpdatedTagline\", \"objectLike_userUpdatedTagline\"]",
|
"[\"objectLike_userProfileImage\", \"objectLike_userProfileImage\"]",
|
"[\"objectLike_forumPost\", \"objectLike_forumPost\"]",
|
"[\"objectLike_forumComment\", \"objectLike_forumComment\"]",
|
"[\"like\", \"like\"]",
|
"[\"forumPost_edit\", \"forumPost_edit\"]",
|
"[\"forumPost_add\", \"forumPost_add\"]",
|
"[\"forumComment_add\", \"forumComment_add\"]",
|
"[\"follow\", \"follow\"]",
|
"[\"fbShare\", \"fbShare\"]",
|
"[\"fbMessengerAttempt\", \"fbMessengerAttempt\"]",
|
"[\"articleShare\", \"articleShare\"]",
|
"[\"articleLike\", \"articleLike\"]",
|
"[\"TwitterAttempt\", \"TwitterAttempt\"]",
|
"[\"GooglePlusAttempt\", \"GooglePlusAttempt\"]",
|
"[\"ANSAttempt\", \"ANSAttempt\"]"
|
],
|
"userId" : [
|
"[ObjectId('53bb05e1386b18ce5b20d86b'), ObjectId('53bb05e1386b18ce5b20d86b')]",
|
"[ObjectId('53bab1e2b5067de0061a4567'), ObjectId('53bab1e2b5067de0061a4567')]",
|
"[ObjectId('53b831b49809bceb3fd2cfab'), ObjectId('53b831b49809bceb3fd2cfab')]",
|
"[ObjectId('53b6eae20c4a07fe63db10b3'), ObjectId('53b6eae20c4a07fe63db10b3')]",
|
"[ObjectId('53b494fb9809bceb3fd25c89'), ObjectId('53b494fb9809bceb3fd25c89')]",
|
"[ObjectId('53af045ebcc79931692a4aa7'), ObjectId('53af045ebcc79931692a4aa7')]",
|
"[ObjectId('53acbfb3cb9f89250d02c48a'), ObjectId('53acbfb3cb9f89250d02c48a')]",
|
"[ObjectId('53a8cc6511b9cc964959c365'), ObjectId('53a8cc6511b9cc964959c365')]",
|
"[ObjectId('53a80878cde71b653ed82539'), ObjectId('53a80878cde71b653ed82539')]",
|
"[ObjectId('53a561b2663e61b528241bc4'), ObjectId('53a561b2663e61b528241bc4')]",
|
"[ObjectId('5382f987832983fb3a5ae9d4'), ObjectId('5382f987832983fb3a5ae9d4')]",
|
"[ObjectId('537f543778f03be40e598ade'), ObjectId('537f543778f03be40e598ade')]",
|
"[ObjectId('537468fff88919d05eee322c'), ObjectId('537468fff88919d05eee322c')]",
|
"[ObjectId('5373bcfef88919d05eee3149'), ObjectId('5373bcfef88919d05eee3149')]"
|
]
|
}
|
}
|
},
|
"rejectedPlans" : [ ]
|
},
|
"executionStats" : {
|
"executionSuccess" : true,
|
"nReturned" : 2209,
|
"executionTimeMillis" : 4,
|
"totalKeysExamined" : 2209,
|
"totalDocsExamined" : 0,
|
"executionStages" : {
|
"stage" : "PROJECTION",
|
"nReturned" : 2209,
|
"executionTimeMillisEstimate" : 0,
|
"works" : 2210,
|
"advanced" : 2209,
|
"needTime" : 0,
|
"needYield" : 0,
|
"saveState" : 17,
|
"restoreState" : 17,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"transformBy" : {
|
"action" : 1,
|
"userId" : 1,
|
"time" : 1,
|
"_id" : 0
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"nReturned" : 2209,
|
"executionTimeMillisEstimate" : 0,
|
"works" : 2210,
|
"advanced" : 2209,
|
"needTime" : 0,
|
"needYield" : 0,
|
"saveState" : 17,
|
"restoreState" : 17,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"keyPattern" : {
|
"time" : 1,
|
"action" : 1,
|
"userId" : 1
|
},
|
"indexName" : "time_1_action_1_userId_1",
|
"isMultiKey" : false,
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : false,
|
"indexVersion" : 1,
|
"direction" : "backward",
|
"indexBounds" : {
|
"time" : [
|
"(new Date(1451318392636), new Date(1400151858193)]"
|
],
|
"action" : [
|
"[\"userUpdatedTagline\", \"userUpdatedTagline\"]",
|
"[\"userUpdatedPicture\", \"userUpdatedPicture\"]",
|
"[\"signin\", \"signin\"]",
|
"[\"share\", \"share\"]",
|
"[\"objectShare_forumPost\", \"objectShare_forumPost\"]",
|
"[\"objectShare_forumComment\", \"objectShare_forumComment\"]",
|
"[\"objectLike_userUpdatedTagline\", \"objectLike_userUpdatedTagline\"]",
|
"[\"objectLike_userProfileImage\", \"objectLike_userProfileImage\"]",
|
"[\"objectLike_forumPost\", \"objectLike_forumPost\"]",
|
"[\"objectLike_forumComment\", \"objectLike_forumComment\"]",
|
"[\"like\", \"like\"]",
|
"[\"forumPost_edit\", \"forumPost_edit\"]",
|
"[\"forumPost_add\", \"forumPost_add\"]",
|
"[\"forumComment_add\", \"forumComment_add\"]",
|
"[\"follow\", \"follow\"]",
|
"[\"fbShare\", \"fbShare\"]",
|
"[\"fbMessengerAttempt\", \"fbMessengerAttempt\"]",
|
"[\"articleShare\", \"articleShare\"]",
|
"[\"articleLike\", \"articleLike\"]",
|
"[\"TwitterAttempt\", \"TwitterAttempt\"]",
|
"[\"GooglePlusAttempt\", \"GooglePlusAttempt\"]",
|
"[\"ANSAttempt\", \"ANSAttempt\"]"
|
],
|
"userId" : [
|
"[ObjectId('53bb05e1386b18ce5b20d86b'), ObjectId('53bb05e1386b18ce5b20d86b')]",
|
"[ObjectId('53bab1e2b5067de0061a4567'), ObjectId('53bab1e2b5067de0061a4567')]",
|
"[ObjectId('53b831b49809bceb3fd2cfab'), ObjectId('53b831b49809bceb3fd2cfab')]",
|
"[ObjectId('53b6eae20c4a07fe63db10b3'), ObjectId('53b6eae20c4a07fe63db10b3')]",
|
"[ObjectId('53b494fb9809bceb3fd25c89'), ObjectId('53b494fb9809bceb3fd25c89')]",
|
"[ObjectId('53af045ebcc79931692a4aa7'), ObjectId('53af045ebcc79931692a4aa7')]",
|
"[ObjectId('53acbfb3cb9f89250d02c48a'), ObjectId('53acbfb3cb9f89250d02c48a')]",
|
"[ObjectId('53a8cc6511b9cc964959c365'), ObjectId('53a8cc6511b9cc964959c365')]",
|
"[ObjectId('53a80878cde71b653ed82539'), ObjectId('53a80878cde71b653ed82539')]",
|
"[ObjectId('53a561b2663e61b528241bc4'), ObjectId('53a561b2663e61b528241bc4')]",
|
"[ObjectId('5382f987832983fb3a5ae9d4'), ObjectId('5382f987832983fb3a5ae9d4')]",
|
"[ObjectId('537f543778f03be40e598ade'), ObjectId('537f543778f03be40e598ade')]",
|
"[ObjectId('537468fff88919d05eee322c'), ObjectId('537468fff88919d05eee322c')]",
|
"[ObjectId('5373bcfef88919d05eee3149'), ObjectId('5373bcfef88919d05eee3149')]"
|
]
|
},
|
"keysExamined" : 2209,
|
"dupsTested" : 0,
|
"dupsDropped" : 0,
|
"seenInvalidated" : 0
|
}
|
},
|
"allPlansExecution" : [ ]
|
},
|
"serverInfo" : {
|
"host" : "mongo-082",
|
"port" : 27017,
|
"version" : "3.2.12",
|
"gitVersion" : "ef3e1bc78e997f0d9f22f45aeb1d8e3b6ac14a14"
|
},
|
"ok" : 1
|
}
|
Or using the index:
{
|
time: 1,
|
userId: 1,
|
action: 1
|
}
|
This query is covered too:
> db.act.find(q,p).sort(s).hint(h3).explain(true)
|
{
|
"queryPlanner" : {
|
"plannerVersion" : 1,
|
"namespace" : "test.act",
|
"indexFilterSet" : false,
|
"parsedQuery" : {
|
"$and" : [
|
{
|
"time" : {
|
"$lt" : ISODate("2015-12-28T15:59:52.636Z")
|
}
|
},
|
{
|
"time" : {
|
"$gte" : ISODate("2014-05-15T11:04:18.193Z")
|
}
|
},
|
{
|
"action" : {
|
"$in" : [
|
"ANSAttempt",
|
"GooglePlusAttempt",
|
"TwitterAttempt",
|
"articleLike",
|
"articleShare",
|
"fbMessengerAttempt",
|
"fbShare",
|
"follow",
|
"forumComment_add",
|
"forumPost_add",
|
"forumPost_edit",
|
"like",
|
"objectLike_forumComment",
|
"objectLike_forumPost",
|
"objectLike_userProfileImage",
|
"objectLike_userUpdatedTagline",
|
"objectShare_forumComment",
|
"objectShare_forumPost",
|
"share",
|
"signin",
|
"userUpdatedPicture",
|
"userUpdatedTagline"
|
]
|
}
|
},
|
{
|
"userId" : {
|
"$in" : [
|
ObjectId("5373bcfef88919d05eee3149"),
|
ObjectId("537468fff88919d05eee322c"),
|
ObjectId("537f543778f03be40e598ade"),
|
ObjectId("5382f987832983fb3a5ae9d4"),
|
ObjectId("53a561b2663e61b528241bc4"),
|
ObjectId("53a80878cde71b653ed82539"),
|
ObjectId("53a8cc6511b9cc964959c365"),
|
ObjectId("53acbfb3cb9f89250d02c48a"),
|
ObjectId("53af045ebcc79931692a4aa7"),
|
ObjectId("53b494fb9809bceb3fd25c89"),
|
ObjectId("53b6eae20c4a07fe63db10b3"),
|
ObjectId("53b831b49809bceb3fd2cfab"),
|
ObjectId("53bab1e2b5067de0061a4567"),
|
ObjectId("53bb05e1386b18ce5b20d86b")
|
]
|
}
|
}
|
]
|
},
|
"winningPlan" : {
|
"stage" : "PROJECTION",
|
"transformBy" : {
|
"action" : 1,
|
"userId" : 1,
|
"time" : 1,
|
"_id" : 0
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"time" : 1,
|
"userId" : 1,
|
"action" : 1
|
},
|
"indexName" : "time_1_userId_1_action_1",
|
"isMultiKey" : false,
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : false,
|
"indexVersion" : 1,
|
"direction" : "backward",
|
"indexBounds" : {
|
"time" : [
|
"(new Date(1451318392636), new Date(1400151858193)]"
|
],
|
"userId" : [
|
"[ObjectId('53bb05e1386b18ce5b20d86b'), ObjectId('53bb05e1386b18ce5b20d86b')]",
|
"[ObjectId('53bab1e2b5067de0061a4567'), ObjectId('53bab1e2b5067de0061a4567')]",
|
"[ObjectId('53b831b49809bceb3fd2cfab'), ObjectId('53b831b49809bceb3fd2cfab')]",
|
"[ObjectId('53b6eae20c4a07fe63db10b3'), ObjectId('53b6eae20c4a07fe63db10b3')]",
|
"[ObjectId('53b494fb9809bceb3fd25c89'), ObjectId('53b494fb9809bceb3fd25c89')]",
|
"[ObjectId('53af045ebcc79931692a4aa7'), ObjectId('53af045ebcc79931692a4aa7')]",
|
"[ObjectId('53acbfb3cb9f89250d02c48a'), ObjectId('53acbfb3cb9f89250d02c48a')]",
|
"[ObjectId('53a8cc6511b9cc964959c365'), ObjectId('53a8cc6511b9cc964959c365')]",
|
"[ObjectId('53a80878cde71b653ed82539'), ObjectId('53a80878cde71b653ed82539')]",
|
"[ObjectId('53a561b2663e61b528241bc4'), ObjectId('53a561b2663e61b528241bc4')]",
|
"[ObjectId('5382f987832983fb3a5ae9d4'), ObjectId('5382f987832983fb3a5ae9d4')]",
|
"[ObjectId('537f543778f03be40e598ade'), ObjectId('537f543778f03be40e598ade')]",
|
"[ObjectId('537468fff88919d05eee322c'), ObjectId('537468fff88919d05eee322c')]",
|
"[ObjectId('5373bcfef88919d05eee3149'), ObjectId('5373bcfef88919d05eee3149')]"
|
],
|
"action" : [
|
"[\"userUpdatedTagline\", \"userUpdatedTagline\"]",
|
"[\"userUpdatedPicture\", \"userUpdatedPicture\"]",
|
"[\"signin\", \"signin\"]",
|
"[\"share\", \"share\"]",
|
"[\"objectShare_forumPost\", \"objectShare_forumPost\"]",
|
"[\"objectShare_forumComment\", \"objectShare_forumComment\"]",
|
"[\"objectLike_userUpdatedTagline\", \"objectLike_userUpdatedTagline\"]",
|
"[\"objectLike_userProfileImage\", \"objectLike_userProfileImage\"]",
|
"[\"objectLike_forumPost\", \"objectLike_forumPost\"]",
|
"[\"objectLike_forumComment\", \"objectLike_forumComment\"]",
|
"[\"like\", \"like\"]",
|
"[\"forumPost_edit\", \"forumPost_edit\"]",
|
"[\"forumPost_add\", \"forumPost_add\"]",
|
"[\"forumComment_add\", \"forumComment_add\"]",
|
"[\"follow\", \"follow\"]",
|
"[\"fbShare\", \"fbShare\"]",
|
"[\"fbMessengerAttempt\", \"fbMessengerAttempt\"]",
|
"[\"articleShare\", \"articleShare\"]",
|
"[\"articleLike\", \"articleLike\"]",
|
"[\"TwitterAttempt\", \"TwitterAttempt\"]",
|
"[\"GooglePlusAttempt\", \"GooglePlusAttempt\"]",
|
"[\"ANSAttempt\", \"ANSAttempt\"]"
|
]
|
}
|
}
|
},
|
"rejectedPlans" : [ ]
|
},
|
"executionStats" : {
|
"executionSuccess" : true,
|
"nReturned" : 2209,
|
"executionTimeMillis" : 4,
|
"totalKeysExamined" : 2209,
|
"totalDocsExamined" : 0,
|
"executionStages" : {
|
"stage" : "PROJECTION",
|
"nReturned" : 2209,
|
"executionTimeMillisEstimate" : 10,
|
"works" : 2210,
|
"advanced" : 2209,
|
"needTime" : 0,
|
"needYield" : 0,
|
"saveState" : 17,
|
"restoreState" : 17,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"transformBy" : {
|
"action" : 1,
|
"userId" : 1,
|
"time" : 1,
|
"_id" : 0
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"nReturned" : 2209,
|
"executionTimeMillisEstimate" : 10,
|
"works" : 2210,
|
"advanced" : 2209,
|
"needTime" : 0,
|
"needYield" : 0,
|
"saveState" : 17,
|
"restoreState" : 17,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"keyPattern" : {
|
"time" : 1,
|
"userId" : 1,
|
"action" : 1
|
},
|
"indexName" : "time_1_userId_1_action_1",
|
"isMultiKey" : false,
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : false,
|
"indexVersion" : 1,
|
"direction" : "backward",
|
"indexBounds" : {
|
"time" : [
|
"(new Date(1451318392636), new Date(1400151858193)]"
|
],
|
"userId" : [
|
"[ObjectId('53bb05e1386b18ce5b20d86b'), ObjectId('53bb05e1386b18ce5b20d86b')]",
|
"[ObjectId('53bab1e2b5067de0061a4567'), ObjectId('53bab1e2b5067de0061a4567')]",
|
"[ObjectId('53b831b49809bceb3fd2cfab'), ObjectId('53b831b49809bceb3fd2cfab')]",
|
"[ObjectId('53b6eae20c4a07fe63db10b3'), ObjectId('53b6eae20c4a07fe63db10b3')]",
|
"[ObjectId('53b494fb9809bceb3fd25c89'), ObjectId('53b494fb9809bceb3fd25c89')]",
|
"[ObjectId('53af045ebcc79931692a4aa7'), ObjectId('53af045ebcc79931692a4aa7')]",
|
"[ObjectId('53acbfb3cb9f89250d02c48a'), ObjectId('53acbfb3cb9f89250d02c48a')]",
|
"[ObjectId('53a8cc6511b9cc964959c365'), ObjectId('53a8cc6511b9cc964959c365')]",
|
"[ObjectId('53a80878cde71b653ed82539'), ObjectId('53a80878cde71b653ed82539')]",
|
"[ObjectId('53a561b2663e61b528241bc4'), ObjectId('53a561b2663e61b528241bc4')]",
|
"[ObjectId('5382f987832983fb3a5ae9d4'), ObjectId('5382f987832983fb3a5ae9d4')]",
|
"[ObjectId('537f543778f03be40e598ade'), ObjectId('537f543778f03be40e598ade')]",
|
"[ObjectId('537468fff88919d05eee322c'), ObjectId('537468fff88919d05eee322c')]",
|
"[ObjectId('5373bcfef88919d05eee3149'), ObjectId('5373bcfef88919d05eee3149')]"
|
],
|
"action" : [
|
"[\"userUpdatedTagline\", \"userUpdatedTagline\"]",
|
"[\"userUpdatedPicture\", \"userUpdatedPicture\"]",
|
"[\"signin\", \"signin\"]",
|
"[\"share\", \"share\"]",
|
"[\"objectShare_forumPost\", \"objectShare_forumPost\"]",
|
"[\"objectShare_forumComment\", \"objectShare_forumComment\"]",
|
"[\"objectLike_userUpdatedTagline\", \"objectLike_userUpdatedTagline\"]",
|
"[\"objectLike_userProfileImage\", \"objectLike_userProfileImage\"]",
|
"[\"objectLike_forumPost\", \"objectLike_forumPost\"]",
|
"[\"objectLike_forumComment\", \"objectLike_forumComment\"]",
|
"[\"like\", \"like\"]",
|
"[\"forumPost_edit\", \"forumPost_edit\"]",
|
"[\"forumPost_add\", \"forumPost_add\"]",
|
"[\"forumComment_add\", \"forumComment_add\"]",
|
"[\"follow\", \"follow\"]",
|
"[\"fbShare\", \"fbShare\"]",
|
"[\"fbMessengerAttempt\", \"fbMessengerAttempt\"]",
|
"[\"articleShare\", \"articleShare\"]",
|
"[\"articleLike\", \"articleLike\"]",
|
"[\"TwitterAttempt\", \"TwitterAttempt\"]",
|
"[\"GooglePlusAttempt\", \"GooglePlusAttempt\"]",
|
"[\"ANSAttempt\", \"ANSAttempt\"]"
|
]
|
},
|
"keysExamined" : 2209,
|
"dupsTested" : 0,
|
"dupsDropped" : 0,
|
"seenInvalidated" : 0
|
}
|
},
|
"allPlansExecution" : [ ]
|
},
|
"serverInfo" : {
|
"host" : "mongo-082",
|
"port" : 27017,
|
"version" : "3.2.12",
|
"gitVersion" : "ef3e1bc78e997f0d9f22f45aeb1d8e3b6ac14a14"
|
},
|
"ok" : 1
|
}
|
>
|
However, both index sizes are much higher than the initially used index:
"indexSizes" : {
|
"_id_" : 32768,
|
"action_1_userId_1_time_1" : 36864,
|
"time_1_action_1_userId_1" : 90112,
|
"time_1_userId_1_action_1" : 90112
|
},
|
In our case it was better to build a bigger index than to have an uncovered query because the latter had to load documents into memory which resulted in spikes due to reads upto 600 MB/sec which made the database far less responsive during these spikes.
|