Sparse indexes in MongoDB - Step-by-Step Execution

Concept Flow - Sparse indexes

Create Sparse Index

↓

Check Each Document

↓

Does Document Have Indexed Field?

No→Skip Document

Yes↓

Add Document to Index

↓

Index Ready for Queries

When creating a sparse index, MongoDB checks each document. It only adds documents that have the indexed field to the index. Documents missing the field are skipped.

Execution Sample

MongoDB

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

Creates a sparse index on the 'email' field in the 'users' collection, indexing only documents that have the 'email' field.

Execution Table

Step	Document _id	Has 'email' Field?	Action	Index State
1	1	Yes	Add to index	Index contains _id:1
2	2	No	Skip document	Index contains _id:1
3	3	Yes	Add to index	Index contains _id:1, _id:3
4	4	No	Skip document	Index contains _id:1, _id:3
5	5	Yes	Add to index	Index contains _id:1, _id:3, _id:5
6	End	-	All documents checked	Final index: _id:1, _id:3, _id:5

💡 All documents processed; only those with 'email' field included in index.

Variable Tracker

Variable	Start	After Doc 1	After Doc 2	After Doc 3	After Doc 4	After Doc 5	Final
Index Contents	empty	[_id:1]	[_id:1]	[_id:1, _id:3]	[_id:1, _id:3]	[_id:1, _id:3, _id:5]	[_id:1, _id:3, _id:5]

Key Moments - 2 Insights

Why are some documents not included in the sparse index?

Documents without the indexed field are skipped during index creation, as shown in steps 2 and 4 of the execution_table where documents without 'email' are not added.

Does a sparse index include documents with the indexed field set to null?

Yes, documents with the field present, even if null, are included. Only documents missing the field entirely are skipped.

Visual Quiz - 3 Questions

Look at the execution_table, which document is the first to be skipped from the index?

A Document with _id:2

B Document with _id:1

C Document with _id:3

D Document with _id:5

💡 Hint

Check the 'Has email Field?' and 'Action' columns in the execution_table for step 2.

At the end of the process, how many documents are included in the sparse index?

A 2

B 4

C 3

D 5

💡 Hint

Look at the 'Index State' column in the final row of the execution_table.

If a document has the 'email' field set to null, will it be included in the sparse index?

A No, because null is treated as missing

B Yes, because the field exists

C No, because sparse indexes exclude null values

D Only if the index is unique

💡 Hint

Refer to the key_moments section explaining inclusion of documents with null fields.

Concept Snapshot

Sparse indexes in MongoDB index only documents that have the indexed field.
Documents missing the field are skipped.
Useful to save space and speed up queries on optional fields.
Syntax: createIndex({field: 1}, {sparse: true})
Queries on sparse indexes ignore documents without the field.

Full Transcript

Sparse indexes in MongoDB work by including only those documents that have the indexed field. When you create a sparse index, MongoDB checks each document in the collection. If the document has the field you want to index, it adds it to the index. If the document does not have that field, it skips it. This means the index is smaller and faster for queries that involve that field. For example, if you create a sparse index on the 'email' field, only documents with an 'email' will be in the index. Documents without 'email' are not indexed. This behavior is shown step-by-step in the execution table, where documents with and without the 'email' field are processed. Sparse indexes also include documents where the field exists but is null. This is important to remember. Sparse indexes are useful when the field is optional and not present in every document.

Practice

1. What is the main purpose of a sparse index in MongoDB?

easy

A. To index only documents that contain the indexed field

B. To index all documents regardless of field presence

C. To create a backup of the database

D. To speed up writes by disabling indexing

Solution

1. Step 1: Understand sparse index behavior

   Sparse indexes only include documents that have the indexed field present.

2. Step 2: Compare with other index types

   Unlike regular indexes, sparse indexes skip documents missing the field, saving space and improving query speed on sparse data.

3. Final Answer:

   To index only documents that contain the indexed field -> Option A

4. Quick Check:

   Sparse index = indexes only documents with the field [OK]

Hint: Sparse indexes skip documents missing the field [OK]

Common Mistakes:

• Thinking sparse indexes index all documents
• Confusing sparse indexes with backups
• Assuming sparse indexes disable indexing

2. Which of the following is the correct way to create a sparse index on the field email in MongoDB?

easy

A. db.collection.createIndex({ email: 1 }, { unique: true })

B. db.collection.createIndex({ email: 1 }, { background: true })

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

D. db.collection.createIndex({ email: 1 }, { expireAfterSeconds: 3600 })

Solution

1. Step 1: Identify sparse index syntax

   The option { sparse: true } is used to create a sparse index in MongoDB.

2. Step 2: Verify the correct command

   The command db.collection.createIndex({ email: 1 }, { sparse: true }) correctly creates a sparse index on the email field.

3. Final Answer:

   db.collection.createIndex({ email: 1 }, { sparse: true }) -> Option C

4. Quick Check:

   Sparse index creation uses { sparse: true } [OK]

Hint: Use { sparse: true } option to create sparse indexes [OK]

Common Mistakes:

• Using unique instead of sparse option
• Confusing background with sparse
• Using expireAfterSeconds for sparse index

3. Given a collection with documents:

{ _id: 1, name: "Alice", age: 30 }

{ _id: 2, name: "Bob" }

{ _id: 3, name: "Charlie", age: 25 }

and a sparse index on age, what documents will be returned by db.collection.find({ age: { $gte: 25 } })?

medium

A. Documents with _id 1 and 3 only

B. Documents with _id 1, 2, and 3

C. Documents with _id 2 only

D. No documents returned

Solution

1. Step 1: Understand sparse index effect on query

   The sparse index on age only indexes documents that have the age field. Document with _id 2 has no age field, so it is not indexed.

2. Step 2: Evaluate the query filter

   The query { age: { $gte: 25 } } matches documents where age is 25 or more. Documents with _id 1 (age 30) and _id 3 (age 25) match. Document _id 2 is excluded because it has no age field.

3. Final Answer:

   Documents with _id 1 and 3 only -> Option A

4. Quick Check:

   Sparse index excludes docs missing field, so only _id 1 and 3 match [OK]

Hint: Sparse index excludes docs missing field, so only those with field match [OK]

Common Mistakes:

• Including documents without the indexed field
• Assuming all documents are returned
• Confusing sparse index with regular index

4. You created a sparse index on phone field but your queries on phone are slow. What is a likely cause?

medium

A. Sparse indexes index all documents regardless of field presence

B. Sparse indexes always slow down queries

C. You forgot to create the index

D. Many documents do not have the phone field, so index is small and not used

Solution

1. Step 1: Analyze sparse index behavior

   Sparse indexes only index documents with the field. If many documents lack the phone field, the index is small.

2. Step 2: Understand query performance impact

   If the index is small or not selective, MongoDB may choose not to use it, causing slower queries.

3. Final Answer:

   Many documents do not have the phone field, so index is small and not used -> Option D

4. Quick Check:

   Sparse index small if field missing in many docs, may not speed queries [OK]

Hint: Sparse index small if many docs lack field, may not improve speed [OK]

Common Mistakes:

• Assuming sparse indexes always slow queries
• Forgetting to create the index
• Thinking sparse indexes index all documents

5. You want to create an index on the status field, but only for documents where status exists and is not null. Which MongoDB index type should you use and why?

hard

A. TTL index, because it removes documents after a time

B. Sparse index, because it indexes only documents with the field present

C. Regular index, because it indexes all documents regardless of field

D. Unique index, because it enforces unique values

Solution

1. Step 1: Identify index requirement

   You want to index only documents where status exists and is not null, skipping documents without status.

2. Step 2: Match index type to requirement

   Sparse indexes index only documents that have the indexed field, which fits the requirement perfectly.

3. Final Answer:

   Sparse index, because it indexes only documents with the field present -> Option B

4. Quick Check:

   Use sparse index to index only docs with the field [OK]

Hint: Use sparse index to index only documents with the field present [OK]

Common Mistakes:

• Choosing regular index which indexes all documents
• Confusing TTL or unique indexes with sparse
• Not understanding sparse index skips missing fields