Sparse indexes in MongoDB

Introduction

Sparse indexes help speed up searches by only indexing documents that have a specific field. This saves space and makes queries faster when some documents don't have that field.

When you have a collection where some documents do not have a certain field.

When you want to index only documents that contain a specific field to save storage.

When you want to speed up queries that filter on a field that is not present in all documents.

When you want to avoid indexing documents with null or missing values for a field.

Syntax

MongoDB

db.collection.createIndex({ field: 1 }, { sparse: true })

The sparse: true option tells MongoDB to only index documents where the field exists and is not null.

The 1 means ascending order for the index.

Examples

This creates a sparse index on the email field in the users collection. Only users with an email will be indexed.

MongoDB

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

This creates a sparse index on the discount field in the products collection. Only products with a discount will be indexed.

MongoDB

db.products.createIndex({ discount: 1 }, { sparse: true })

Sample Program

This example inserts items where some have a price field and some do not. Then it creates a sparse index on price. Finally, it finds and sorts items that have a price.

MongoDB

db.inventory.insertMany([
  { item: "apple", category: "fruit" },
  { item: "banana", category: "fruit", price: 1 },
  { item: "carrot", category: "vegetable", price: 2 },
  { item: "donut" }
])

// Create sparse index on price
 db.inventory.createIndex({ price: 1 }, { sparse: true })

// Find all items with price
 db.inventory.find({ price: { $exists: true } }).sort({ price: 1 })

Important Notes

Sparse indexes do not include documents where the indexed field is missing or null.

Using sparse indexes can reduce index size and improve query speed for fields not present in all documents.

Be careful: queries that look for documents without the field will not use the sparse index.

Summary

Sparse indexes only index documents that have the indexed field.

This saves space and speeds up queries on fields that are not in every document.

Use sparse indexes when many documents might not have the field you want to index.