Menu
From what I read from mongodb documents, only one index is used in a query. However, what I find is that the presence of some other compound indexes affect the quality of this query. Here is an example:
db.products.ensureIndex({'b' : 1, 'l.d' : 1, 'l.i' : 1})
db.products.find({'b' : {$in : b.ct}, 'l.d' : {$lt : d}}).limit(24).sort({'l.i' : 1}).explain()
{ "cursor" : "BtreeCursor b_1_l.d_1_l.i_1 multi",
"isMultiKey" : true,
"n" : 24,
"nscannedObjects" : 1079,
"nscanned" : 1102,
"nscannedObjectsAllPlans" : 1182,
"nscannedAllPlans" : 1205,
"scanAndOrder" : true,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
....}
db.products.ensureIndex({'l.i' :1, 'b' : 1, 'l.d' : 1})
db.products.find({'b' : {$in : b.ct}, 'l.d' : {$lt : d}}).limit(24).sort({'l.i' : 1}).explain()
{ "cursor" : "BtreeCursor b_1_l.d_1_l.i_1 multi",
"isMultiKey" : true,
"n" : 24,
"nscannedObjects" : 614,
"nscanned" : 624,
"nscannedObjectsAllPlans" : 1283,
"nscannedAllPlans" : 1875,
"scanAndOrder" : true,
"indexOnly" : false,
"nYields" : 1,
"nChunkSkips" : 0,
....}
The value of nscanned is reduced by almost half. Why?
================================================================
Based on the comments, I updated my command line sequence to provide more detailed information. Note the index names are changed because I modified the database. The result is the same. Two indices are better, but why?
db.products.stats()
{
"ns" : "mytest.products",
"count" : 209607,
"size" : 90155636,
"avgObjSize" : 430.11748653432375,
"storageSize" : 123936768,
"numExtents" : 11,
"nindexes" : 1,
"lastExtentSize" : 37625856,
"paddingFactor" : 1,
"systemFlags" : 0,
"userFlags" : 0,
"totalIndexSize" : 5927600,
"indexSizes" : {
"_id_" : 5927600
},
"ok" : 1
}
b.ct
[
2020,
3564969011,
2021,
15762981,
271619011,
2023,
2024,
2027,
3825141,
505092,
2025,
2028,
10825721,
2080,
2026,
2085,
2029,
2030,
2032,
3564970011,
2081,
2082,
2083,
2084,
271621011,
2087
]
d
ISODate("2012-11-30T00:00:00Z")
db.products.ensureIndex({'b': 1, 'd': 1, 'i' : 1})
db.products.stats()
{
"ns" : "mytest.products",
"count" : 209607,
"size" : 90155636,
"avgObjSize" : 430.11748653432375,
"storageSize" : 123936768,
"numExtents" : 11,
"nindexes" : 2,
"lastExtentSize" : 37625856,
"paddingFactor" : 1,
"systemFlags" : 0,
"userFlags" : 0,
"totalIndexSize" : 22614816,
"indexSizes" : {
"_id_" : 5927600,
"b_1_d_1_i_1" : 16687216
},
"ok" : 1
}
db.products.find({'b' : {$in : b.ct}, 'd' : {$lt : d}}).limit(24).sort({'i' : 1}).explain()
{
"cursor" : "BtreeCursor b_1_d_1_i_1 multi",
"isMultiKey" : true,
"n" : 24,
"nscannedObjects" : 1294,
"nscanned" : 1300,
"nscannedObjectsAllPlans" : 1395,
"nscannedAllPlans" : 1401,
"scanAndOrder" : true,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 12,
"indexBounds" : {
"b" : [
[
2020,
2020
],
[
2021,
2021
],
[
2023,
2023
],
[
2024,
2024
],
[
2025,
2025
],
[
2026,
2026
],
[
2027,
2027
],
[
2028,
2028
],
[
2029,
2029
],
[
2030,
2030
],
[
2032,
2032
],
[
2080,
2080
],
[
2081,
2081
],
[
2082,
2082
],
[
2083,
2083
],
[
2084,
2084
],
[
2085,
2085
],
[
2087,
2087
],
[
505092,
505092
],
[
3825141,
3825141
],
[
10825721,
10825721
],
[
15762981,
15762981
],
[
271619011,
271619011
],
[
271621011,
271621011
],
[
3564969011,
3564969011
],
[
3564970011,
3564970011
]
],
"d" : [
[
true,
ISODate("2012-11-30T00:00:00Z")
]
],
"i" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
},
"server" : "li91-182:27017"
}
db.products.ensureIndex({'i': 1, 'b': 1, 'd' : 1})
db.products.stats()
{
"ns" : "mytest.products",
"count" : 209607,
"size" : 90155636,
"avgObjSize" : 430.11748653432375,
"storageSize" : 123936768,
"numExtents" : 11,
"nindexes" : 3,
"lastExtentSize" : 37625856,
"paddingFactor" : 1,
"systemFlags" : 0,
"userFlags" : 0,
"totalIndexSize" : 39302032,
"indexSizes" : {
"_id_" : 5927600,
"b_1_d_1_i_1" : 16687216,
"i_1_b_1_d_1" : 16687216
},
"ok" : 1
}
db.products.find({'b' : {$in : b.ct}, 'd' : {$lt : d}}).limit(24).sort({'i' : 1}).explain()
{
"cursor" : "BtreeCursor b_1_d_1_i_1 multi",
"isMultiKey" : true,
"n" : 24,
"nscannedObjects" : 206,
"nscanned" : 206,
"nscannedObjectsAllPlans" : 445,
"nscannedAllPlans" : 619,
"scanAndOrder" : true,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 6,
"indexBounds" : {
"b" : [
[
2020,
2020
],
[
2021,
2021
],
[
2023,
2023
],
[
2024,
2024
],
[
2025,
2025
],
[
2026,
2026
],
[
2027,
2027
],
[
2028,
2028
],
[
2029,
2029
],
[
2030,
2030
],
[
2032,
2032
],
[
2080,
2080
],
[
2081,
2081
],
[
2082,
2082
],
[
2083,
2083
],
[
2084,
2084
],
[
2085,
2085
],
[
2087,
2087
],
[
505092,
505092
],
[
3825141,
3825141
],
[
10825721,
10825721
],
[
15762981,
15762981
],
[
271619011,
271619011
],
[
271621011,
271621011
],
[
3564969011,
3564969011
],
[
3564970011,
3564970011
]
],
"d" : [
[
true,
ISODate("2012-11-30T00:00:00Z")
]
],
"i" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
},
"server" : "li91-182:27017"
}
db.products.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"ns" : "mytest.products",
"name" : "_id_"
},
{
"v" : 1,
"key" : {
"b" : 1,
"d" : 1,
"i" : 1
},
"ns" : "mytest.products",
"name" : "b_1_d_1_i_1"
},
{
"v" : 1,
"key" : {
"i" : 1,
"b" : 1,
"d" : 1
},
"ns" : "mytest.products",
"name" : "i_1_b_1_d_1"
}
]
db.products.dropIndex({'i': 1, 'b': 1, 'd' : 1}) { "nIndexesWas" : 3, "ok" : 1 }
db.products.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"ns" : "mytest.products",
"name" : "_id_"
},
{
"v" : 1,
"key" : {
"b" : 1,
"d" : 1,
"i" : 1
},
"ns" : "mytest.products",
"name" : "b_1_d_1_i_1"
}
]
db.products.find({'b' : {$in : b.ct}, 'd' : {$lt : d}}).limit(24).sort({'i' : 1}).explain()
{
"cursor" : "BtreeCursor b_1_d_1_i_1 multi",
"isMultiKey" : true,
"n" : 24,
"nscannedObjects" : 1294,
"nscanned" : 1300,
"nscannedObjectsAllPlans" : 1395,
"nscannedAllPlans" : 1401,
"scanAndOrder" : true,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 131,
"indexBounds" : {
"b" : [
[
2020,
2020
],
[
2021,
2021
],
[
2023,
2023
],
[
2024,
2024
],
[
2025,
2025
],
[
2026,
2026
],
[
2027,
2027
],
[
2028,
2028
],
[
2029,
2029
],
[
2030,
2030
],
[
2032,
2032
],
[
2080,
2080
],
[
2081,
2081
],
[
2082,
2082
],
[
2083,
2083
],
[
2084,
2084
],
[
2085,
2085
],
[
2087,
2087
],
[
505092,
505092
],
[
3825141,
3825141
],
[
10825721,
10825721
],
[
15762981,
15762981
],
[
271619011,
271619011
],
[
271621011,
271621011
],
[
3564969011,
3564969011
],
[
3564970011,
3564970011
]
],
"d" : [
[
true,
ISODate("2012-11-30T00:00:00Z")
]
],
"i" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
},
"server" : "li91-182:27017"
}
859
According to 10gen BSON is a binary-encoded serialization of JSON-like documents. However the order of fields in BSON documents does matter:
> db.things.insert({b:1,d:1,i:1});
> db.things.insert({i:2,b:2,d:2});
> db.things.insert({d:3,i:3,b:3});
> db.things.find();
{ "_id" : ObjectId("50904ee4875db529686c5775"), "b" : 1, "d" : 1, "i" : 1 }
{ "_id" : ObjectId("50904ef0875db529686c5776"), "i" : 2, "b" : 2, "d" : 2 }
{ "_id" : ObjectId("50904efc875db529686c5777"), "d" : 3, "i" : 3, "b" : 3 }
So, whenever you're creating index with db.products.ensureIndex({'b' : 1, 'l.d' : 1, 'l.i' : 1}) and then with db.products.ensureIndex({'l.i' :1, 'b' : 1, 'l.d' : 1}) you got 2 indexes with different order of fields. This can be checked via result of db.products.getIndexes() you've kindly provided:
[{
"v" : 1,
"key" : {
"b" : 1,
"d" : 1,
"i" : 1
},
"ns" : "mytest.products",
"name" : "b_1_d_1_i_1"
},
{
"v" : 1,
"key" : {
"i" : 1,
"b" : 1,
"d" : 1
},
"ns" : "mytest.products",
"name" : "i_1_b_1_d_1"
}]
And ifferent order of fields obviously may lead to different nscanned value - the number of items (including index tree nodes) to be scanned:
Number of items (documents or index entries) examined. Items might be objects or index keys.
98
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
