Indexing for Performance Optimization

Now that you've mastered querying and working with complex data operations, let's tackle one of the most crucial aspects of database performance: indexing. In this lesson, you'll learn how MongoDB indexes work, when to use them, and how they can dramatically speed up your queries.

Learning Goals:

• Understand what indexes are and how they improve query performance
• Learn to create and manage different types of indexes
• Discover how to analyze query performance using explain()
• Identify when and what to index for optimal results

What Are Indexes and Why Do We Need Them?

Think of a database index like a book's index. Instead of scanning every page to find a topic, you can look it up in the index and go directly to the relevant pages. Similarly, MongoDB indexes are special data structures that store a small portion of your collection's data in an easy-to-traverse form.

Without indexes, MongoDB must perform a collection scan - examining every document in a collection to find those that match the query. This becomes increasingly slow as your data grows.

Example: Collection with 1 million users

// Without indexes, this query scans all 1 million documents
db.users.find({ email: "john@example.com" })

// With an index on email, MongoDB can find the document instantly

Creating Basic Indexes

Let's start with the most common type of index: the single field index.

Creating a single field index

// Create an index on the 'email' field
db.users.createIndex({ email: 1 })

// Now queries using email will be much faster
db.users.find({ email: "alice@company.com" })

The number 1 specifies ascending order. You can also use -1 for descending order, though for single field equality queries, the direction typically doesn't matter.

tip

Create indexes on fields you frequently query, sort, or use in aggregation pipelines. Common candidates include user IDs, email addresses, timestamps, and status fields.

Compound Indexes

When queries involve multiple fields, compound indexes can provide even better performance.

Creating and using compound indexes

// Create a compound index on category and price
db.products.createIndex({ category: 1, price: -1 })

// These queries can use the compound index efficiently:
db.products.find({ category: "electronics" }).sort({ price: -1 })
db.products.find({ category: "books", price: { $gt: 20 } })

warning

The order of fields in a compound index matters! MongoDB can use a compound index for queries that include:

• All fields in the index
• A prefix of the index fields (first field, first two fields, etc.)

But it cannot use the index for queries that skip the prefix fields.

Unique Indexes

Unique indexes ensure that no two documents have the same value for the indexed field(s).

Creating unique indexes

// Ensure email addresses are unique across all users
db.users.createIndex({ email: 1 }, { unique: true })

// Compound unique index
db.orders.createIndex({ customerId: 1, orderDate: 1 }, { unique: true })

Analyzing Query Performance with explain()

The explain() method shows how MongoDB executes a query, helping you understand if indexes are being used.

Using explain() to analyze queries

// See the query execution plan
db.users.find({ email: "test@example.com" }).explain("executionStats")

// Look for these key indicators:
// - "stage": "IXSCAN" (index scan) is good, "COLLSCAN" (collection scan) is bad
// - "nReturned": number of documents returned
// - "executionTimeMillis": total time in milliseconds
// - "totalKeysExamined": number of index keys examined
// - "totalDocsExamined": number of documents examined

TTL Indexes for Expiring Data

TTL (Time To Live) indexes automatically remove documents after a specified time period.

Creating TTL indexes

// Remove sessions older than 1 hour (3600 seconds)
db.sessions.createIndex({ "lastAccess": 1 }, { expireAfterSeconds: 3600 })

// Documents will be automatically deleted when:
// lastAccess + 3600 seconds < current time

Text Indexes for Full-Text Search

Text indexes support text search queries on string content.

Creating and using text indexes

// Create a text index on product name and description
db.products.createIndex({ name: "text", description: "text" })

// Search for products containing specific terms
db.products.find({ $text: { $search: "wireless bluetooth speaker" } })

Managing Indexes

You can view, modify, and remove indexes as needed.

Index management commands

// List all indexes in a collection
db.users.getIndexes()

// Get index size information
db.users.stats()

// Drop a specific index
db.users.dropIndex("email_1")

// Drop all indexes (except _id)
db.users.dropIndexes()

Common Pitfalls

• Over-indexing: Too many indexes slow down write operations (inserts, updates, deletes) because each index must be maintained
• Wrong field order: In compound indexes, field order matters for query coverage
• Indexing low-selectivity fields: Indexing fields with few unique values (like "gender" or "status") may not improve performance much
• Ignoring index size: Large indexes consume memory and disk space
• Missing index on sort operations: Queries with .sort() that don't use indexes can be very slow

Summary

Indexes are essential for maintaining good database performance as your data grows. Remember to:

• Create indexes on frequently queried fields
• Use compound indexes for multi-field queries
• Analyze query performance with explain()
• Balance read performance with write overhead
• Monitor index usage and remove unused indexes

Show quiz

1. What is the primary purpose of creating indexes in MongoDB?

   • A) To reduce storage space
   • B) To improve query performance
   • C) To enforce data validation
   • D) To backup data automatically

2. Which query execution stage indicates that an index is being used?

   • A) COLLSCAN
   • B) IXSCAN
   • C) SORT
   • D) PROJECTION

3. What happens when you create a unique index on an existing collection with duplicate values?

   • A) The index creation succeeds and removes duplicates
   • B) The index creation fails with an error
   • C) MongoDB automatically fixes the duplicates
   • D) The index is created but marked as invalid

4. In a compound index { category: 1, price: -1 }, which queries can use this index efficiently?

   • A) db.products.find({ price: { $gt: 100 } })
   • B) db.products.find({ category: "electronics" })
   • C) db.products.find({ price: { $lt: 50 } }).sort({ category: 1 })
   • D) db.products.find({ brand: "Samsung" })

5. What is a potential downside of having too many indexes?

   • A) Increased query performance
   • B) Slower write operations
   • C) Reduced storage requirements
   • D) Automatic data compression

---

Answers:

1. B - Indexes primarily improve query performance by avoiding full collection scans
2. B - IXSCAN indicates index scan, COLLSCAN indicates collection scan
3. B - Unique index creation fails if duplicate values exist
4. B - Compound indexes support queries that use a prefix of the indexed fields
5. B - Each index adds overhead to write operations since all indexes must be updated