-
Type:
Bug
-
Resolution: Duplicate
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: Querying
-
None
-
ALL
The server version is 2.4.8. The following query is not using indexes.
S-haystackdbec2-0:PRIMARY> db.streams.find({$or:[ {statusNeedsRefresh:true},{site:'youtube', status:'ondemand', publishedDate:{$exists:false}}] }).explain();
"cursor" : "BasicCursor",
"isMultiKey" : false,
"n" : 6,
"nscannedObjects" : 1992659,
"nscanned" : 1992659,
"nscannedObjectsAllPlans" : 1992659,
"nscannedAllPlans" : 1992659,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 178,
"nChunkSkips" : 0,
"millis" : 10890,
"indexBounds" : {
},
"server" : "ip-172-31-42-250:27017"
This query seems to work
RS-haystackdbec2-0:PRIMARY> db.streams.find({$or:[{statusNeedsRefresh:true}, {publishedDate:null, site:'youtube',status:'ondemand'}]}).explain();
{
"clauses" : [
{
"cursor" : "BtreeCursor statusNeedsRefresh_1",
"isMultiKey" : false,
"n" : 6,
"nscannedObjects" : 6,
"nscanned" : 6,
"nscannedObjectsAllPlans" : 6,
"nscannedAllPlans" : 6,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 1,
"indexBounds" : {
"statusNeedsRefresh" : [
[
true,
true
]
]
}
},
{
"cursor" : "BtreeCursor status_1_lang_1_publishedDate_1",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 3,
"nscanned" : 75,
"nscannedObjectsAllPlans" : 255,
"nscannedAllPlans" : 327,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 2,
"indexBounds" : {
"status" : [
[
"ondemand",
"ondemand"
]
],
"lang" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"publishedDate" : [
[
null,
null
]
]
}
}
],
"n" : 6,
"nscannedObjects" : 9,
"nscanned" : 81,
"nscannedObjectsAllPlans" : 261,
"nscannedAllPlans" : 333,
"millis" : 3,
"server" : "ip-172-31-42-250:27017"
}
Here are all the indexes
RS-haystackdbec2-0:PRIMARY> db.streams.getIndexes();
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"ns" : "haystackdb.streams",
"name" : "_id_"
},
{
"v" : 1,
"key" : {
"sourceUrl" : 1
},
"unique" : true,
"ns" : "haystackdb.streams",
"name" : "sourceUrl_1",
"background" : true,
"safe" : null
},
{
"v" : 1,
"key" : {
"tsLastUpdated" : 1
},
"ns" : "haystackdb.streams",
"name" : "tsLastUpdated_1",
"background" : true
},
{
"v" : 1,
"key" : {
"tweetsPerHour" : 1
},
"ns" : "haystackdb.streams",
"name" : "tweetsPerHour_1",
"background" : true
},
{
"v" : 1,
"key" : {
"publishedDate" : 1
},
"ns" : "haystackdb.streams",
"name" : "publishedDate_1",
"background" : true
},
{
"v" : 1,
"key" : {
"twitterTags" : 1
},
"ns" : "haystackdb.streams",
"name" : "twitterTags_1",
"background" : true
},
{
"v" : 1,
"key" : {
"popularityScore" : 1
},
"ns" : "haystackdb.streams",
"name" : "popularityScore_1",
"background" : true
},
{
"v" : 1,
"key" : {
"_id" : 1,
"sourceUrl" : 1
},
"ns" : "haystackdb.streams",
"name" : "_id_1_sourceUrl_1",
"background" : true
},
{
"v" : 1,
"key" : {
"site" : 1,
"author" : 1,
"category" : 1
},
"ns" : "haystackdb.streams",
"name" : "site_1_author_1_category_1",
"background" : true
},
{
"v" : 1,
"key" : {
"tags" : 1
},
"ns" : "haystackdb.streams",
"name" : "tags_1",
"background" : true
},
{
"v" : 1,
"key" : {
"lang" : 1,
"tweetsPerHour" : 1
},
"ns" : "haystackdb.streams",
"name" : "lang_1_tweetsPerHour_1",
"background" : true
},
{
"v" : 1,
"key" : {
"trackedChannel" : 1,
"site" : 1,
"publishedDate" : 1
},
"ns" : "haystackdb.streams",
"name" : "trackedChannel_1_site_1_publishedDate_1",
"background" : true
},
{
"v" : 1,
"key" : {
"site" : 1,
"status" : 1,
"tsLastBookkeeping" : 1,
"trackedChannel" : 1
},
"ns" : "haystackdb.streams",
"name" : "site_1_status_1_tsLastBookkeeping_1_trackedChannel_1",
"background" : true
},
{
"v" : 1,
"key" : {
"title" : 1
},
"ns" : "haystackdb.streams",
"name" : "title_1"
},
{
"v" : 1,
"key" : {
"titleReverse" : 1
},
"ns" : "haystackdb.streams",
"name" : "titleReverse_1",
"sparse" : true,
"background" : true
},
{
"v" : 1,
"key" : {
"highestPopularityScore" : 1
},
"ns" : "haystackdb.streams",
"name" : "highestPopularityScore_1",
"sparse" : true,
"background" : true
},
{
"v" : 1,
"key" : {
"status" : 1,
"lang" : 1,
"publishedDate" : 1
},
"ns" : "haystackdb.streams",
"name" : "status_1_lang_1_publishedDate_1"
},
{
"v" : 1,
"key" : {
"haystackTags" : 1,
"publishedDate" : -1
},
"ns" : "haystackdb.streams",
"name" : "haystackTags_1_publishedDate_-1",
"background" : true
},
{
"v" : 1,
"key" : {
"category" : 1,
"publishedDate" : -1,
"popularityScore" : -1
},
"ns" : "haystackdb.streams",
"name" : "category_1_publishedDate_-1_popularityScore_-1",
"background" : true
},
{
"v" : 1,
"key" : {
"author" : 1,
"publishedDate" : 1,
"popularityScore" : -1
},
"ns" : "haystackdb.streams",
"name" : "author_1_publishedDate_1_popularityScore_-1",
"background" : true
},
{
"v" : 1,
"key" : {
"statusNeedsRefresh" : 1
},
"ns" : "haystackdb.streams",
"name" : "statusNeedsRefresh_1",
"sparse" : true,
"background" : true
}
]
RS-haystackdbec2-0:PRIMARY>