- Indexes >
- Index Concepts >
- Index Properties >
- Sparse Indexes
Sparse Indexes¶
On this page
Sparse indexes only contain entries for documents that have the indexed field, even if the index field contains a null value. The index skips over any document that is missing the indexed field. The index is “sparse” because it does not include all documents of a collection. By contrast, non-sparse indexes contain all documents in a collection, storing null values for those documents that do not contain the indexed field.
The following example in the mongo
shell creates a sparse
index on the xmpp_id
field of the addresses
collection:
By default, sparse
is false
on MongoDB indexes.
Warning
Using these indexes will sometimes result in incomplete results when filtering or sorting results, because sparse indexes are not complete for all documents in a collection.
Note
Do not confuse sparse indexes in MongoDB with block-level indexes in other databases. Think of them as dense indexes with a specific filter.
Tip
You can specify a sparse and unique index, that rejects documents that have duplicate values for a field, but allows multiple documents that omit that key.
Examples¶
Sparse Index On A Collection Can Result In Incomplete Results¶
Consider a collection scores
that contains the following documents:
The collection has a sparse index on the field score
:
Then, the following query to return all documents in the scores
collection sorted by the score
field gives incomplete
results:
Because the document for the userid "newbie"
does not contain
the score
field, the query, which uses the sparse index, will
return incomplete results that omit that document:
Sparse Index with Unique Constraint¶
Consider a collection scores
that contains the following documents:
You could create an index with a unique constraint and sparse filter on the score
field using
the following operation:
This index would permit inserting documents that had unique
values for the score
field or did not include a score
field. Consider the following insert operation:
However, this index would not permit adding the following documents: