When not to index in MongoDB - Step-by-Step Execution

Concept Flow - When not to index

Start: Need to decide on indexing?

↓

Check: Is the field frequently queried?

No→Do NOT index

Yes↓

Check: Is the collection small?

Yes→Indexing may be unnecessary

No↓

Check: Is the field updated very often?

Yes→Avoid indexing to reduce overhead

No↓

Create index

↓

End

Decide whether to index by checking query frequency, collection size, and update frequency to avoid unnecessary overhead.

Execution Sample

MongoDB

db.users.find({age: 30})
// Query on 'age' field
// Decide if 'age' should be indexed
// Conditions: frequent query? collection size? update frequency?

This example shows deciding whether to index the 'age' field in the users collection based on usage patterns.

Execution Table

Step	Condition Checked	Condition Result	Action Taken	Reason
1	Is 'age' frequently queried?	Yes	Proceed to next check	Field is used often in queries
2	Is collection small (<1000 docs)?	No	Proceed to next check	Collection is large enough to benefit from index
3	Is 'age' field updated frequently?	Yes	Do NOT index	Indexing overhead on frequent updates is high
4	Exit	N/A	No index created	Avoid performance cost on writes

💡 Stopped indexing because 'age' field is updated frequently, causing high overhead

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
frequent_query	unknown	Yes	Yes	Yes	Yes
collection_small	unknown	unknown	No	No	No
field_updated_often	unknown	unknown	unknown	Yes	Yes
index_decision	undecided	undecided	undecided	No	No

Key Moments - 2 Insights

Why should we avoid indexing fields that are updated very often?

Because each update requires updating the index too, which slows down write operations as shown in step 3 of the execution_table.

If the collection is very small, why might indexing be unnecessary?

Because scanning a small collection is fast enough, so the overhead of maintaining an index may not be worth it, as seen in step 2.

Visual Quiz - 3 Questions

Look at the execution_table, what is the reason for not creating an index?

A The field is not frequently queried

B The field is updated frequently

C The collection is too small

D The query is too complex

💡 Hint

Check step 3 in the execution_table where the decision to not index is made due to frequent updates

At which step does the decision to avoid indexing happen?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look at the 'Action Taken' column in the execution_table to find when indexing is stopped

If the 'age' field was not updated frequently, what would be the next step after confirming frequent queries and large collection?

A Do NOT index

B Check if collection is small

C Create index

D Stop querying

💡 Hint

Refer to the flow in concept_flow and the execution_table steps 1 and 2

Concept Snapshot

When not to index:
- Avoid indexing fields updated very often to reduce write overhead.
- Small collections may not need indexes as scans are fast.
- Index fields frequently used in queries.
- Indexing adds overhead on writes.
- Decide based on query frequency, collection size, and update frequency.

Full Transcript

This lesson shows when you should not create an index in MongoDB. First, check if the field is often used in queries. If not, no index is needed. If yes, check if the collection is small; small collections may not benefit from indexes. Then check if the field is updated frequently. If it is, avoid indexing because indexes slow down updates. The example shows a field 'age' that is queried often but updated frequently, so indexing is avoided to keep writes fast.

Practice

1. Which of the following is a good reason not to create an index on a MongoDB collection?

easy

A. The collection is very small and queries are fast without indexes.

B. The field is frequently used in queries to filter data.

C. The collection has millions of documents and needs fast lookups.

D. The field has many unique values and is often sorted.

Solution

1. Step 1: Understand index impact on small collections

   Indexes add overhead and use space, so on very small collections, queries are already fast without them.

2. Step 2: Compare with other options

   The other options describe good reasons to create indexes: frequent filtering, large collections, and fields with many unique values used in sorting.

3. Final Answer:

   The collection is very small and queries are fast without indexes. -> Option A

4. Quick Check:

   Small collections often don't need indexes [OK]

Hint: Avoid indexing tiny collections to save space and speed [OK]

Common Mistakes:

• Thinking all collections always need indexes
• Ignoring index overhead on small data
• Confusing frequent query fields with rare ones

2. Which MongoDB command correctly creates an index on the email field of the users collection?

easy

A. db.users.create_index(email)

B. db.users.createIndex({email: 1})

C. db.users.index({email: 'asc'})

D. db.users.addIndex('email')

Solution

1. Step 1: Recall MongoDB index creation syntax

   The correct method is createIndex with a document specifying field and order (1 for ascending).

2. Step 2: Check each option

   db.users.createIndex({email: 1}) matches the correct syntax. The distractors use wrong method names or formats: create_index(email) (incorrect casing, underscore, and argument style), index({email: 'asc'}) (non-existent method), addIndex('email') (wrong method).

3. Final Answer:

   db.users.createIndex({email: 1}) -> Option B

4. Quick Check:

   Use createIndex({field: 1}) syntax [OK]

Hint: Use createIndex({field: 1}) to create ascending index [OK]

Common Mistakes:

• Using wrong method names like create_index
• Passing field name as string instead of object
• Confusing ascending with string 'asc'

3. Given a collection logs with a field status that has only two values: 'success' or 'fail', what is the likely effect of creating an index on status?

medium

A. The index will use extra space but provide no significant speedup.

B. The index will cause errors because of low cardinality.

C. Queries filtering by status will be much faster due to the index.

D. The index will automatically compress the data to save space.

Solution

1. Step 1: Understand low cardinality fields and indexing

   Fields with few distinct values (low cardinality) like 'success'/'fail' do not benefit much from indexes because many documents share the same value.

2. Step 2: Analyze options

   Claims of much faster queries overestimate benefits for low cardinality. No errors occur from low cardinality indexes. Automatic data compression does not happen. The index uses extra space with no significant speedup.

3. Final Answer:

   The index will use extra space but provide no significant speedup. -> Option A

4. Quick Check:

   Low cardinality fields rarely benefit from indexes [OK]

Hint: Avoid indexing fields with few unique values [OK]

Common Mistakes:

• Assuming all indexes speed up queries
• Believing low cardinality causes errors
• Thinking indexes compress data automatically

4. You created an index on the category field of a MongoDB collection, but queries filtering by category are still slow. What is a likely cause?

medium

A. MongoDB does not support indexing on string fields.

B. The index was created with the wrong field name syntax.

C. The category field has very few distinct values causing low index efficiency.

D. The collection is empty, so indexes do not work.

Solution

1. Step 1: Consider index usefulness on low distinct values

   If category has few unique values, the index won't help much because many documents share the same value.

2. Step 2: Evaluate other options

   Wrong field name syntax is unlikely if the index exists; MongoDB supports indexing on string fields; empty collections have no data to slow down queries.

3. Final Answer:

   The category field has very few distinct values causing low index efficiency. -> Option C

4. Quick Check:

   Low distinct values reduce index speedup [OK]

Hint: Check field cardinality if index doesn't speed queries [OK]

Common Mistakes:

• Assuming all indexes always speed queries
• Believing MongoDB can't index strings
• Thinking empty collections cause index failure

5. You have a MongoDB collection orders with 100 documents. The status field has values 'pending', 'shipped', 'delivered'. You want to optimize queries filtering by status. What is the best indexing strategy?

hard

A. Create multiple indexes on each possible status value separately.

B. Create a single-field index on status to speed up queries.

C. Create a compound index on status and orderDate even if orderDate is rarely queried.

D. Do not create an index because the collection is small and status has few distinct values.

Solution

1. Step 1: Analyze collection size and field cardinality

   The collection is very small (100 documents), and status has only three distinct values, so indexing will add overhead without much benefit.

2. Step 2: Evaluate indexing options

   Create a single-field index on status to speed up queries. creates an index but likely unnecessary here. Create a compound index on status and orderDate even if orderDate is rarely queried. adds complexity with rarely used field. Create multiple indexes on each possible status value separately. is invalid as MongoDB does not create separate indexes per value.

3. Final Answer:

   Do not create an index because the collection is small and status has few distinct values. -> Option D

4. Quick Check:

   Small collections and low cardinality fields often don't need indexes [OK]

Hint: Skip indexes on small collections with few unique values [OK]

Common Mistakes:

• Creating unnecessary indexes on small data
• Misunderstanding compound index benefits
• Trying to index each value separately