When not to index in MongoDB - Deep Dive

Overview - When not to index

What is it?

Indexing in MongoDB is a way to speed up data searches by creating special data structures. However, not every field or collection should have an index. Knowing when not to index helps avoid slowing down your database and wasting storage. This topic explains situations where adding indexes can do more harm than good.

Why it matters

Without understanding when not to index, you might add too many indexes that slow down data writing and use extra disk space. This can make your app slower and more expensive to run. Avoiding unnecessary indexes keeps your database fast and efficient, saving time and resources.

Where it fits

Before this, you should know what indexes are and how they work in MongoDB. After this, you can learn about index types, how to create them, and how to optimize queries using indexes.

Mental Model

Core Idea

Indexes speed up searches but slow down writes and use space, so only add them when the benefit outweighs the cost.

Think of it like...

Think of an index like a table of contents in a book: it helps you find pages quickly, but if every page had its own tiny table of contents, the book would get bulky and harder to update.

┌───────────────┐       ┌───────────────┐
│   Data Table  │──────▶│   Full Scan   │
└───────────────┘       └───────────────┘
        │                      ▲
        │                      │
        ▼                      │
┌───────────────┐       ┌───────────────┐
│    Index      │──────▶│  Fast Search  │
└───────────────┘       └───────────────┘

Note: More indexes mean more maintenance during writes.

Build-Up - 7 Steps

1

Foundation

What is an index in MongoDB

Concept: Introduce the basic idea of an index and its role in speeding up queries.

An index in MongoDB is a special data structure that stores a small portion of the data in an easy-to-search way. When you query a collection, MongoDB can use the index to find matching documents faster instead of looking at every document.

Result

Queries that use indexes run faster because MongoDB skips scanning all documents.

Understanding what an index is helps you see why it can speed up searches but also why it needs extra space.

2

Foundation

How indexes affect writes and storage

Concept: Explain the trade-off between read speed and write cost with indexes.

Every time you add, update, or delete a document, MongoDB must update all indexes on that collection. This means writes take longer and use more CPU. Also, indexes take up disk space, which grows with the number of indexes and data size.

Result

More indexes mean slower writes and more storage used.

Knowing the cost of indexes on writes and storage helps you balance performance needs.

3

Intermediate

When indexing low-cardinality fields hurts

🤔Before reading on: do you think indexing a field with only a few unique values speeds up queries or not? Commit to your answer.

Concept: Explain why indexing fields with few unique values (low-cardinality) is often not helpful.

Fields like 'gender' or 'boolean flags' have very few unique values. Indexes on these fields don't filter data well because many documents share the same value. MongoDB may still scan many documents, making the index less useful and still costly to maintain.

Result

Indexes on low-cardinality fields often do not improve query speed and slow down writes.

Understanding cardinality helps you avoid useless indexes that waste resources.

4

Intermediate

Avoid indexing fields rarely queried

🤔Before reading on: is it better to index every field or only those used often in queries? Commit to your answer.

Concept: Teach that indexes should be created only on fields frequently used in queries.

If a field is rarely used in queries, its index will rarely help. But MongoDB still updates it on writes, causing overhead. It's better to add indexes only on fields that queries use often to get a good speedup.

Result

Fewer, well-chosen indexes improve overall performance.

Knowing query patterns guides smart index choices that balance speed and cost.

5

Intermediate

When collection size is small enough

Concept: Explain why small collections may not need indexes.

If a collection has very few documents, scanning all documents is fast enough. Adding indexes adds overhead without much benefit. MongoDB can quickly check all documents without indexes in small collections.

Result

Skipping indexes on small collections can simplify design and improve write speed.

Recognizing when data size is small helps avoid premature optimization with indexes.

6

Advanced

Impact of index on write-heavy workloads

🤔Before reading on: do you think adding many indexes helps or hurts a database with mostly writes? Commit to your answer.

Concept: Show how indexes affect performance in write-heavy applications.

In write-heavy workloads, every insert, update, or delete must update all indexes. This can cause significant slowdowns and increase resource use. Sometimes, it's better to have fewer indexes or none to keep writes fast.

Result

Write-heavy systems often use minimal indexes to maintain speed.

Understanding workload type is key to choosing the right indexing strategy.

7

Expert

Hidden costs of over-indexing in production

🤔Before reading on: do you think having many indexes always improves production performance? Commit to your answer.

Concept: Reveal subtle problems caused by too many indexes in real systems.

Too many indexes increase disk space, memory usage, and CPU load. They can cause longer backup times and slow down replication. Also, index fragmentation can degrade performance over time. Monitoring and pruning indexes is essential in production.

Result

Over-indexing can cause unexpected slowdowns and higher costs in real-world systems.

Knowing hidden costs prevents common production mistakes and helps maintain healthy databases.

Under the Hood

MongoDB stores indexes as B-tree data structures that map key values to document locations. When a query uses an index, MongoDB traverses the B-tree to quickly find matching documents. However, every write operation must update all relevant B-trees, which requires extra CPU and disk I/O. Indexes also consume RAM to keep parts of the B-tree in memory for fast access.

Why designed this way?

B-trees were chosen because they balance fast reads and writes and keep data sorted. MongoDB uses this structure to optimize common query patterns. The trade-off is that maintaining these trees during writes costs resources, so indexes must be used carefully to avoid slowing down the system.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│   Write Data  │──────▶│ Update Indexes │──────▶│  B-tree Nodes │
└───────────────┘       └───────────────┘       └───────────────┘
        │                      ▲                       │
        │                      │                       ▼
┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ Query Request │──────▶│ Use Indexes   │──────▶│  Find Docs    │
└───────────────┘       └───────────────┘       └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does adding more indexes always make queries faster? Commit yes or no.

Common Belief: More indexes always speed up queries.

Reality: Adding too many indexes slows down writes and can even hurt overall performance.

Why it matters: Ignoring write costs leads to slow applications and wasted resources.

Quick: Is it good to index every field in your documents? Commit yes or no.

Common Belief: Indexing every field is best to cover all queries.

Reality: Indexing every field wastes space and slows writes without meaningful query speedup.

Why it matters: Over-indexing causes maintenance headaches and performance degradation.

Quick: Does indexing a boolean field always improve query speed? Commit yes or no.

Common Belief: Indexing boolean or low-cardinality fields always helps.

Reality: Indexes on low-cardinality fields rarely improve queries and add overhead.

Why it matters: Misusing indexes wastes resources and complicates query planning.

Quick: Should small collections always have indexes? Commit yes or no.

Common Belief: Small collections always benefit from indexes.

Reality: Small collections often do not need indexes because full scans are fast.

Why it matters: Unnecessary indexes add complexity and slow down writes for no gain.

Expert Zone

1

Index intersection allows MongoDB to combine multiple indexes to satisfy a query, but over-indexing can confuse the optimizer and reduce performance.

2

Partial indexes can reduce index size and write overhead by indexing only documents that meet a filter condition, useful when only a subset of data is queried frequently.

3

TTL (Time To Live) indexes automatically remove expired documents, but adding TTL indexes unnecessarily can cause unexpected deletions and overhead.

When NOT to use

Avoid indexing fields with low cardinality, rarely queried fields, or in write-heavy collections where write speed is critical. Instead, consider schema redesign, caching, or using aggregation pipelines without indexes.

Production Patterns

In production, teams monitor query patterns and index usage with MongoDB tools, regularly remove unused indexes, and use compound or partial indexes to optimize performance while minimizing overhead.

Connections

Caching

Alternative optimization technique

Knowing when not to index helps you decide when to use caching to speed up data access without adding index overhead.

Data Normalization

Schema design impacts indexing needs

Understanding schema design helps reduce unnecessary indexes by organizing data efficiently.

Operating System File Caching

Underlying system affects index performance

Knowing how OS caches files helps explain why indexes sometimes perform differently in production.

Common Pitfalls

#1 Indexing every field without considering query patterns.

Wrong approach:

db.collection.createIndex({ field1: 1 });
db.collection.createIndex({ field2: 1 });
db.collection.createIndex({ field3: 1 });
// and so on for every field

Correct approach:

db.collection.createIndex({ field1: 1 }); // only index fields used often in queries

Root cause:

Misunderstanding that more indexes always improve performance.

#2 Indexing low-cardinality fields like booleans.

Wrong approach:

db.collection.createIndex({ isActive: 1 }); // isActive is true/false

Correct approach:

// Avoid indexing isActive unless combined in a compound index with higher cardinality fields

Root cause:

Not knowing that indexes on fields with few unique values are ineffective.

#3 Adding indexes on small collections unnecessarily.

Wrong approach:

db.smallCollection.createIndex({ name: 1 }); // collection has only 10 documents

Correct approach:

// Skip index; full collection scan is fast enough

Root cause:

Assuming indexes always help regardless of data size.

Key Takeaways

Indexes speed up queries but slow down writes and consume storage, so use them wisely.

Avoid indexing fields with few unique values or those rarely used in queries to prevent wasted resources.

Small collections often do not need indexes because scanning all documents is fast.

Write-heavy workloads benefit from fewer indexes to maintain fast data changes.

Regularly review and remove unused indexes in production to keep your database efficient.

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