Mongodb sparse index and general index

Question

I have created a collection with 100 documents (fields x & y), and created a normal index on fieldx and a sparse index on field y, as shown below :

for(i=1;i<100;i++)db.coll.insert({x:i,y:i})

db.coll.createIndex({x:1})
db.coll.createIndex({y:1},{sparse:true})

Then, I added a few docs without fields x & y as shown below:

for(i=1;i<100;i++)db.coll.insert({z:"stringggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg"})

Looking at db.coll.stats(), I found the sizes of the indexes:

storageSize:36864
_id:32768
x_1:32768
y_1:16384

As per the definition of sparse index, only documents containing the indexed field y are considered, hence y_1 occupies less space. But _id & x_1 indexes seem to contain all the documents in them.

If I perform a query - db.coll.find({z:99}).explain('executionStats')

It is doing a COLLSCAN and fetching the record. If this is the case, I am not clear on why MongoDB stores all the documents under _id & x_1 indexes, as it is a waste of storage space. Please help me understand. Pardon my ignorance if i missed something.

Thank you for your help.

kevinadi kevinadi · Accepted Answer · 2018-01-03T22:37:40

In a "normal" index, missing fields are indexed with a null value. For example, if you have index of {a:1} and you insert {b:10} into the collection, the document will be indexed as a: null.

You can see this behaviour using a unique index:

> db.test.createIndex({a:1}, {unique:true})
{
    "createdCollectionAutomatically" : true,
    "numIndexesBefore" : 1,
    "numIndexesAfter" : 2,
    "ok" : 1
}

> db.test.insert({b:1})
WriteResult({ "nInserted" : 1 })

> db.test.insert({c:1})
WriteResult({
    "nInserted" : 0,
    "writeError" : {
        "code" : 11000,
        "errmsg" : "E11000 duplicate key error collection: test.test index: a_1 dup key: { : null }"
    }
})

Both {b:1} and {c:1} are indexed as a: null, hence the duplicate key error message.

In your collection, you have 200 documents:

100 documents with {x:..., y:...}
100 documents with {z:...}

And your indexes are:

{x:1} (normal index)
{y:1} (sparse index)

The documents will be indexed as follows:

200 documents will be in the _id index, which is always created by MongoDB
200 documents will be in the {x:1} index, from {x:.., y:..} and {z:..} documents
100 documents will be in the {y:1} index

Note that the index sizes you posted shows the same ratio as the numbers above.

Regarding your questions:

The _id index is for MongoDB internal use, see Default _id index. You cannot drop this index, and attempts to remove it could render your database inaccessible.
The x_1 index is there because you told MongoDB to build it. It contains all the documents in your collection because it's a normal index. In the case of your collection, half of the values in the index are null.
The sparse y_1 index is half the size of the x_1 index because only 100 out of 200 documents contain the y field.
The query db.coll.find({z:99}) does not use any index because you don't have an index on the z field, hence it's doing a collection scan.

For more information about indexing, please see Create Indexes to Support Your Queries

Mongodb sparse index and general index

1 Answers