Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why does one mongoDB compound index affect another compound index?

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"
}
like image 859
Fei Avatar asked Oct 23 '22 06:10

Fei


1 Answers

According to 10gen BSON is a bin­ary-en­coded seri­al­iz­a­tion of JSON-like doc­u­ments. 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.

like image 98
Vladimir Avatar answered Nov 15 '22 07:11

Vladimir