Understanding Mongo Explain Index for Many Results
I'm trying to figure out how best to design indexes for Mongo queries that I see run with long queries.
I am currently seeing something like this:
"cursor" : "BtreeCursor modTime_-1_color_1 multi",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 0,
"nscanned" : 104936,
"nscannedObjectsAllPlans" : 104935,
"nscannedAllPlans" : 314806,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 70,
"nChunkSkips" : 0,
"millis" : 14237,
"indexBounds" : {
"modTime" : [
[
{
"$maxElement" : 1
},
{
"sec" : 1360267645,
"usec" : 0
}
]
],
"color" : [
[
"Green",
"Green"
],
[
"Blue",
"Blue"
],
[
"Yellow",
"Yellow"
]
]
},
with a query similar to:
{"modTime":{"$gte":{"sec":1360267645,"usec":0}},"color":{"$in":["Green","Blue","Yellow"]}}
Is there something else I should be doing to create this index so that it doesn't scan as many results?
Thank you in advance for your thoughts and suggestions.
source to share
nscanned count - total number of documents with a point in time greater than the specified time.
If there is good variability in the color box, you may need to reverse the index so that color is the first box and modTime is the second.
db.<collection>.createIndex( { color: 1, modTime : -1 } )
Then only the number of documents of the desired color with the time point exceeding the waiting time will be checked. If the only colors in the collection are Green, Blue, Yellow, then there is no use. If there are even a few complementary colors, you should get some benefit.
Warning. If you have queries that only use modTime, they won't be able to use the new index.
Another potential solution is to close the modTime range using the $ lt (or $ lte) expression. You will need to determine if this is possible for your request, or if it will affect the number of possible documents for assessment.
source to share