kulama
Reputation: 13
mongodb 2.6.3 use wrong index
I have a query like below.Obviously the better index is "platform_1_keyuserAccountID_1_weiboAccountID_1_postTime_1",but mongodb choose "weiboAccountID_1",am i make something wrong?
db.inbox_weibo.find({platform: "sina", keyuserAccountID:"1665337047", weiboAccountID: "2271338624"}).explain(true);
{
"cursor" : "BtreeCursor weiboAccountID_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"nscannedObjectsAllPlans" : 24,
"nscannedAllPlans" : 634425,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 1,
"nChunkSkips" : 0,
"millis" : 2912,
"indexBounds" : {
"weiboAccountID" : [
[
"2271338624",
"2271338624"
]
]
},
"allPlans" : [
{
"cursor" : "BtreeCursor weiboAccountID_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"weiboAccountID" : [
[
"2271338624",
"2271338624"
]
]
}
},
{
"cursor" : "BtreeCursor platform_1_keyuserAccountID_1_dealed_1_operatorID_1_weiboAccountID_1_postTIme_-1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 62,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"platform" : [
[
"sina",
"sina"
]
],
"keyuserAccountID" : [
[
"1665337047",
"1665337047"
]
],
"dealed" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"operatorID" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"weiboAccountID" : [
[
"2271338624",
"2271338624"
]
],
"postTIme" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
}
},
{
"cursor" : "BtreeCursor platform_1_keyuserAccountID_1_weiboAccountID_1_postTime_-1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"platform" : [
[
"sina",
"sina"
]
],
"keyuserAccountID" : [
[
"1665337047",
"1665337047"
]
],
"weiboAccountID" : [
[
"2271338624",
"2271338624"
]
],
"postTime" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
}
},
{
"cursor" : "BtreeCursor platform_keyuserAccountID_dealed_postTime_weiboAccountID",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 634331,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"platform" : [
[
"sina",
"sina"
]
],
"keyuserAccountID" : [
[
"1665337047",
"1665337047"
]
],
"dealed" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"postTime" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
],
"weiboAccountID" : [
[
"2271338624",
"2271338624"
]
]
}
},
{
"cursor" : "BtreeCursor platform_1_keyuserAccountID_1_mid_1",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 2,
"nscanned" : 3,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"platform" : [
[
"sina",
"sina"
]
],
"keyuserAccountID" : [
[
"1665337047",
"1665337047"
]
],
"mid" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
}
},
{
"cursor" : "BtreeCursor platform_1_keyuserAccountID_1_dealerID_1",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 2,
"nscanned" : 3,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"platform" : [
[
"sina",
"sina"
]
],
"keyuserAccountID" : [
[
"1665337047",
"1665337047"
]
],
"dealerID" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
}
},
{
"cursor" : "BtreeCursor distinctAccountAgg",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 2,
"nscanned" : 3,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"platform" : [
[
"sina",
"sina"
]
],
"keyuserAccountID" : [
[
"1665337047",
"1665337047"
]
],
"dealed" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"operatorID" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"postTime" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
}
},
{
"cursor" : "BtreeCursor fansCountUndealed",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 2,
"nscanned" : 3,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"platform" : [
[
"sina",
"sina"
]
],
"keyuserAccountID" : [
[
"1665337047",
"1665337047"
]
],
"dealed" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"fromType" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"fansCount" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
],
"postTime" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
],
"usePicture" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
}
},
{
"cursor" : "BtreeCursor retweetCountUndealed",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 2,
"nscanned" : 3,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"platform" : [
[
"sina",
"sina"
]
],
"keyuserAccountID" : [
[
"1665337047",
"1665337047"
]
],
"dealed" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"fromType" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"sourceRetweetCount" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
],
"postTime" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
],
"usePicture" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
}
},
{
"cursor" : "BtreeCursor interactUndealed",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 2,
"nscanned" : 3,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"platform" : [
[
"sina",
"sina"
]
],
"keyuserAccountID" : [
[
"1665337047",
"1665337047"
]
],
"dealed" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"fromType" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"userInteractionCount" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
],
"postTime" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
],
"usePicture" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
}
},
{
"cursor" : "BtreeCursor platform_1_keyuserAccountID_1_dealed_1_dealTime_-1",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 2,
"nscanned" : 3,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"platform" : [
[
"sina",
"sina"
]
],
"keyuserAccountID" : [
[
"1665337047",
"1665337047"
]
],
"dealed" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"dealTime" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
}
},
{
"cursor" : "BtreeCursor platform_1_keyuserAccountID_1_dealerID_1_postTime_-1",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 2,
"nscanned" : 3,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"platform" : [
[
"sina",
"sina"
]
],
"keyuserAccountID" : [
[
"1665337047",
"1665337047"
]
],
"dealerID" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"postTime" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
}
},
{
"cursor" : "BtreeCursor platform_1_keyuserAccountID_1_postTime_-1",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 2,
"nscanned" : 3,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"platform" : [
[
"sina",
"sina"
]
],
"keyuserAccountID" : [
[
"1665337047",
"1665337047"
]
],
"postTime" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
}
},
{
"cursor" : "BtreeCursor keyuserAccountID_dealed_createTime_-1",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 2,
"nscanned" : 3,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"keyuserAccountID" : [
[
"1665337047",
"1665337047"
]
],
"dealed" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"createTime" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
}
}
Upvotes: 1
Views: 73
Answers (1)
Levente Dobson
Reputation: 773
I don't think the other index is "better". It seems that both indexes (the one you would suggest and the one mongodb uses) return the queried object with "nscanned:1", so they are equally perfect for this query. I say that since "weiboAccountID" in your test dataset defines exactly the one record you are looking for, there is no need for other indexes in this case - mongodb will choose the first good fit. Try adding more records with this same exact weiboAccountID (if duplicated Ids are at all valid in your dataset) and re-run the query, see how it changes the execution.
Upvotes: 1