Example
Sparse indexes:
These can be particularly useful for fields that are optional but which should also be unique.
{ "_id" : "[email protected]", "nickname" : "Johnnie" }
{ "_id" : "[email protected]" }
{ "_id" : "[email protected]", "nickname" : "Jules"}
{ "_id" : "[email protected]" }
Since two entries have no "nickname" specified and indexing will treat unspecified fields as null, the index creation would fail with 2 documents having 'null', so:
db.scores.createIndex( { nickname: 1 } , { unique: true, sparse: true } )
will let you still have 'null' nicknames.
Sparse indexes are more compact since they skip/ignore documents that don't specify that field. So if you have a collection where only less than 10% of documents specify this field, you can create much smaller indexes - making better use of limited memory if you want to do queries like:
db.scores.find({'nickname': 'Johnnie'})
Partial indexes:
Partial indexes represent a superset of the functionality offered by sparse indexes and should be preferred over sparse indexes. (New in version 3.2)
Partial indexes determine the index entries based on the specified filter.
db.restaurants.createIndex(
{ cuisine: 1 },
{ partialFilterExpression: { rating: { $gt: 5 } } }
)
If rating is greater than 5, then cuisine will be indexed. Yes, we can specify a property to be indexed based on the value of other properties also.
Difference between Sparse and Partial indexes:
Sparse indexes select documents to index solely based on the existence of the indexed field, or for compound indexes, the existence of the indexed fields.
Partial indexes determine the index entries based on the specified filter. The filter can include fields other than the index keys and can specify conditions other than just an existence check.
Still, a partial index can implement the same behavior as a sparse index
Eg:
db.contacts.createIndex(
{ name: 1 },
{ partialFilterExpression: { name: { $exists: true } } }
)
Note: Both the partialFilterExpression option and the sparse option cannot be specified at the same time.
