Show
My index is :
{
"v" : 1,
"key" : {
"channelId" : 1,
"searchField" : 1
},
"name" : "channelId_1_searchField_1",
"ns" : "search.searchSeriesOnChannel1"
}
Here's a working query hinting not to use the index which returns 2 results :
db.searchSeriesOnChannel1.find({$and : [{ "searchField" : { "$regex" : "_dr" , "$options" : "i"}, "channelId" : { "$in" : [ "CTVTo" , "CTVHT"]}}]}).sort({searchField:1}).hint({$natural:1}).explain()
{
"cursor" : "BasicCursor",
"isMultiKey" : false,
"n" : 2,
"nscannedObjects" : 21743,
"nscanned" : 21743,
"nscannedObjectsAllPlans" : 21743,
"nscannedAllPlans" : 21743,
"scanAndOrder" : true,
"indexOnly" : false,
"nYields" : 169,
"nChunkSkips" : 0,
"millis" : 59,
"server" : "fonsemongo:27017",
"filterSet" : false
}
Using the index, it returns 62 elements because it discards the searchField $regex filter.
db.searchSeriesOnChannel1.find({$and : [{ "searchField" : { "$regex" : "_dr" , "$options" : "i"}, "channelId" : { "$in" : [ "CTVTo" , "CTVHT"]}}]}).sort({searchField:1}).explain()
{
"clauses" : [
{
"cursor" : "BtreeCursor channelId_1_searchField_1",
"isMultiKey" : false,
"n" : 62,
"nscannedObjects" : 62,
"nscanned" : 62,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"channelId" : [
[
"CTVHT",
"CTVHT"
]
],
"searchField" : [
[
"",
{
}
],
[
/_dr/i,
/_dr/i
]
]
}
},
{
"cursor" : "BtreeCursor channelId_1_searchField_1",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 0,
"nscanned" : 0,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"channelId" : [
[
"CTVTo",
"CTVTo"
]
],
"searchField" : [
[
"",
{
}
],
[
/_dr/i,
/_dr/i
]
]
}
}
],
"cursor" : "QueryOptimizerCursor",
"n" : 62,
"nscannedObjects" : 62,
"nscanned" : 62,
"nscannedObjectsAllPlans" : 62,
"nscannedAllPlans" : 62,
"scanAndOrder" : false,
"nYields" : 1,
"nChunkSkips" : 0,
"millis" : 0,
"server" : "fonsemongo:27017",
"filterSet" : false
}