Sparse Index in MongoDB: Definition, Usage, and Examples

A sparse index in MongoDB is an index that only includes documents where the indexed field exists and is not null. This helps save space and improve performance when many documents lack the indexed field.

How It Works

Imagine you have a big filing cabinet with many folders, but only some folders have a specific type of document inside. A sparse index is like a special list that only points to folders containing that document type, ignoring all others. This makes the list smaller and faster to search.

In MongoDB, a sparse index only includes entries for documents that have the indexed field present and not null. If a document does not have that field, it is simply skipped in the index. This differs from a regular index, which includes all documents regardless of whether the field exists.

This approach reduces the size of the index and speeds up queries that filter on that field, especially when many documents do not have the field.

Example

This example shows how to create a sparse index on the email field in a MongoDB collection. Only documents with an email field will be indexed.

mongodb

db.users.createIndex({ email: 1 }, { sparse: true })

// Insert documents
db.users.insertMany([
  { name: "Alice", email: "[e-mail]" },
  { name: "Bob" },
  { name: "Charlie", email: "[e-mail]" },
  { name: "David" }
])

// Query using the sparse index
db.users.find({ email: { $exists: true } })

Output

[
  { "_id": ObjectId("..."), "name": "Alice", "email": "[e-mail]" },
  { "_id": ObjectId("..."), "name": "Charlie", "email": "[e-mail]" }
]

When to Use

Use a sparse index when many documents in your collection do not have the field you want to index. This saves space and improves query speed for searches on that field.

For example, if you have a user database where only some users provide a phone number, a sparse index on the phone field will index only those users who have a phone number. This avoids bloating the index with entries for users without phone numbers.

Sparse indexes are also useful when you want to enforce uniqueness only on documents that have a certain field.

Key Points

• A sparse index only includes documents where the indexed field exists and is not null.
• It reduces index size and improves query performance for fields missing in many documents.
• Sparse indexes can be combined with unique constraints to enforce uniqueness only on documents with the field.
• Queries that filter on the indexed field benefit most from sparse indexes.

Key Takeaways

Sparse indexes include only documents with the indexed field present and not null.

They save space and speed up queries when many documents lack the indexed field.

Use sparse indexes for optional fields that are not in every document.

Sparse indexes can enforce uniqueness only on documents that have the field.

Queries filtering on the indexed field benefit most from sparse indexes.