Mastering Compound Indexes in MongoDB

Mastering Compound Indexes in MongoDB

This guide delves into the intricacies of compound indexes in MongoDB, a crucial technique for optimizing database performance. We’ll cover everything from understanding their fundamental workings to analyzing their effectiveness and troubleshooting common issues.

1. Understanding Compound Indexes
2. Creating Compound Indexes
3. Querying with Compound Indexes
4. Analyzing Index Usage
5. Best Practices and Optimization
6. Frequently Asked Questions

1. Understanding Compound Indexes

In MongoDB, a compound index is an index spanning multiple fields within a single document. Unlike single-field indexes, which only accelerate queries on a single field, compound indexes dramatically boost the speed of queries involving multiple fields, significantly impacting application performance. The order of fields within the index is critical; MongoDB utilizes this order to efficiently locate documents.

Queries matching the leading fields of the index gain the most benefit. If a query uses only the leading fields, the index is fully utilized for both filtering and sorting. However, if a query uses fields beyond the index, MongoDB might partially utilize the index, potentially falling back to a collection scan for the remaining criteria, negating the performance gains.

For example, consider a products collection with fields category, price, and name. A compound index on { "category": 1, "price": -1 } optimizes queries filtering by category and then sorting by price in descending order.

2. Creating Compound Indexes

Creating compound indexes is straightforward using the db.collection.createIndex() method. The argument is a document where keys represent field names and values represent the sort order (1 for ascending, -1 for descending).

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

// Create a compound index on multiple fields, with varying sort orders
db.users.createIndex( { firstName: 1, lastName: -1, age: 1 } );

// Add unique and sparse options
db.products.createIndex( { category: 1, price: -1 }, { unique: true, sparse: true } );

unique: true enforces uniqueness across the indexed fields, while sparse: true indexes only documents where the indexed fields are not null.

3. Querying with Compound Indexes

MongoDB’s query optimizer automatically utilizes a compound index if the query matches the leading fields and the sort order aligns. For example, the following query benefits from the { "category": 1, "price": -1 } index:

db.products.find( { category: "Electronics" } ).sort( { price: -1 } );

This query filters by category (the leading index field) and sorts by price (matching the index’s sort order). However, this query would not benefit as much:

db.products.find( { price: { $lt: 100 } } ).sort( { category: 1 } );

price is not the leading field, and the sort order doesn’t align with the index.

4. Analyzing Index Usage

Use db.collection.find().explain() to analyze index usage. This provides detailed information about the query execution plan, including which indexes were used (or not) and execution times.

db.products.find( { category: "Electronics" } ).sort( { price: -1 } ).explain();

Examine the “executionStats” section. If the index is used, you’ll see index key usage and the number of documents examined. “COLLSCAN” indicates a full collection scan, less efficient than index usage.

5. Best Practices and Optimization

Careful index selection is crucial. Over-indexing slows down write operations, while under-indexing leads to inefficient queries. Prioritize frequently used query patterns, adding indexes incrementally as needed. Monitor index usage regularly and adjust based on application needs. Consider using the MongoDB Compass GUI for simpler index management.

6. Frequently Asked Questions

• How many compound indexes should I create? The optimal number depends on your application’s query patterns. A balance is needed to avoid over-indexing.
• What if my query doesn’t perfectly match the index? MongoDB might use parts of the index but may still resort to a collection scan for unmatched parts.
• Can I create compound indexes with mixed ascending/descending fields? Yes, the order is crucial for optimal performance.
• How do I delete a compound index? Use db.collection.dropIndex(), specifying the index name or specification:

// Delete by name
db.products.dropIndex("category_1_price_-1");

// Delete by specification
db.products.dropIndex( { category: 1, price: -1 } );