Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why would the index on a field be a different size depending on if it is ascending or descending in MongoDB

I have two single indexes for the "created" field in my collection. One index is sorted ascending, and the other is sorted descending. The index that is sorted descending is larger than the index sorted ascending. The created field holds a Javascript Date object. What would cause this?

"indexSizes" : {
    "_id_" : 212862160,
    "created_1" : 136424736,
    "created_-1" : 252376768
},

Here is the details from collection.getIndexes(). The only difference is the descending index was created in the background.

{
    "v" : 1,
    "key" : {
        "created" : 1
    },
    "name" : "created_1",
    "ns" : "Production.accounts"
},
{
    "v" : 1,
    "key" : {
        "created" : -1
    },
    "name" : "created_-1",
    "ns" : "Production.accounts",
    "background" : true
}
like image 205
Matthew Antolovich Avatar asked Nov 01 '22 02:11

Matthew Antolovich


1 Answers

The difference is due to the ascending index being created in the foreground, and the descending index being created in the background.

From the docs on background index creation:

Background index builds take longer to complete and result in an index that is initially larger, or less compact, than an index built in the foreground. Over time, the compactness of indexes built in the background will approach foreground-built indexes.

So create your index in the foreground if you want it as compact as possible, but a background index will also become more compact over time.

like image 86
JohnnyHK Avatar answered Nov 15 '22 05:11

JohnnyHK