Mongodb sparse index

Sparse indexes are like regular indexes in mongodb. The differece is that sparse indexes only include documents that have the indexed field, whereas regular indexdes include all documents no matter if the indexded field exist or not.

For example, when you create an index on a age field of customers collection that not every customer has a age info on file, sparse index will exclude customers don’t have a age field whereas the regular index will include all customers even if the customer don’t have a value for age. Imagine there is a billion of documents in the customer collection and half of them don’t have age on file, a sparse index on the age will save a lot of memory spaces.

Create a sparse index on the age of the customers collection by adding {sparse: true}

db.customers.createIndex( { age: 1 }, { sparse: true } );

This query will use the sparse index because the criteria in the query goes well with the sparse index. We only want to return customers whose age is greater than 21, if the customer doens’t have age on file, we don’t include them.

db.customers.find({age: {$gt:21}});

This query will not use the sparse index, it will return all documents including those don’t have age field.

db.customers.sort({age:1});

This query will use the sparse index, it will only return documents that have age field.

db.customers.sort({age:1}).hint({age:1});