Best Practices for Indexing in MongoDB to Optimize Query Performance

Indexes are essential for optimizing query performance in MongoDB, especially as your dataset grows. While MongoDB’s default indexing on the _id field is useful for primary key lookups, designing custom indexes tailored to your query patterns is crucial for achieving high performance. This article delves into best practices for indexing in MongoDB, covering various types of indexes such as single-field, compound, multikey, geospatial, and text indexes, with code examples to illustrate their use.

Understanding Indexes in MongoDB

Indexes in MongoDB are data structures that store a portion of a collection's data in a format that is quick to traverse. By providing efficient access to data, indexes can significantly reduce the time it takes to execute queries. However, indexes also come with trade-offs, such as increased storage use and slower write operations. Therefore, it’s important to carefully plan your indexing strategy.

Key Considerations:

• Query Patterns: Indexes should be created based on the fields frequently used in query filters, sorts, and joins.
• Cardinality: High-cardinality fields (fields with many unique values) typically benefit more from indexing.
• Write Performance: Each index must be updated during insert, update, and delete operations, potentially slowing down write performance.

Best Practices for Indexing in MongoDB

Single-Field Indexes

Scenario: You are developing an application that manages a large user database. The application frequently performs searches by email to retrieve user information for authentication and notifications. To improve the speed of these queries, you decide to implement a single-field index on the email field.

Without Index: When querying the database without an index, MongoDB performs a collection scan, meaning it must check each document in the collection to find the matching email. This approach becomes increasingly inefficient as the number of users grows, leading to slower query response times.

With Single-Field Index: By creating an index on the email field, MongoDB can quickly locate the user’s document by navigating the index structure, significantly reducing query execution time.

Implementation:

// Create a single-field index on the "email" field in the users collection
db.users.createIndex({ email: 1 });

Query:

// Query to find a user by email
db.users.find({ email: "user@example.com" });

Impact:

• Before Index: MongoDB performs a full collection scan, which is inefficient for large datasets.
• After Index: MongoDB directly uses the index to retrieve the user’s data in O(log n) time, significantly improving performance as the user base grows.

Best Practice:

• Single-field indexes should be applied to high-cardinality fields (like email), where each value is unique or has very few duplicates, as these fields will benefit most from indexing. This indexing strategy is optimal when your queries consistently target a specific field.

Compound Indexes

Scenario: An E-commerce Application

Imagine you are developing an e-commerce application that allows users to search for products by category, filter by price, and sort by popularity. The products collection in your MongoDB database stores fields like category, price, and popularity. Users often query for products within a specific category, filter them by a price range, and sort the results by popularity.

Schema Example:

{
   "_id": ObjectId("..."),
   "name": "Laptop",
   "category": "Electronics",
   "price": 1200,
   "popularity": 500,
   "inStock": true
}

Query Example:

A typical query in this scenario might look like:

db.products
  .find({ category: "Electronics", price: { $lte: 1500 } })
  .sort({ popularity: -1 });

Without indexing, this query would require MongoDB to perform a full collection scan, checking every document to find matches. This becomes inefficient as the dataset grows.

Solution: Using a Compound Index

To optimize the query, you can create a compound index on the category, price, and popularity fields:

db.products.createIndex({ category: 1, price: 1, popularity: -1 });

• Category: This field comes first because it is most selective, i.e., filtering by category significantly reduces the number of documents to examine.
• Price: Second, because it is used to filter documents further.
• Popularity: Comes last, as it is only used for sorting the results.

Benefits:

• Efficient Filtering: The index allows MongoDB to quickly filter documents by category and price, avoiding a full collection scan.
• Optimized Sorting: Since the popularity field is included in the index, MongoDB can return results sorted by popularity without additional overhead.
• Reduced I/O: The compound index reduces disk I/O by narrowing the search space to only the relevant documents.

Performance Improvement: By using this compound index, you drastically improve query performance, especially as the number of products grows, leading to faster response times for users.

This use case highlights how compound indexes can be tailored to match query patterns involving multiple fields, ensuring efficient filtering and sorting in MongoDB applications.

Multikey Indexes

Scenario

You are developing a blogging platform where each blog post can have multiple tags. Users often search for blog posts by specific tags, and you want to ensure that queries filtering by tags are efficient.

Schema:

{
   "_id": ObjectId("..."),
   "title": "Optimizing MongoDB Performance",
   "content": "An in-depth guide on indexing...",
   "tags": ["mongodb", "performance", "indexing"],
   "author": "John Doe",
   "publishedAt": ISODate("2024-08-31T00:00:00Z")
}

In this scenario, the tags field is an array containing multiple tags for each blog post. To optimize searches that filter by tags, you can create a multikey index on the tags array.

Multikey Index Example:

// Create a multikey index on the "tags" array in the posts collection
db.posts.createIndex({ tags: 1 });

With this index, MongoDB will index each element in the tags array, enabling efficient querying when filtering blog posts by one or more tags.

Optimized Query Example:

// Find posts tagged with "mongodb"
db.posts.find({ tags: "mongodb" });

Without the multikey index, MongoDB would need to perform a collection scan to find all documents containing the tag "mongodb," resulting in slower performance. The multikey index allows MongoDB to quickly locate posts with the desired tag.

Key Considerations:

• Multikey indexes are efficient for querying arrays, but they can increase the size of the index significantly if arrays are large.
• Avoid creating multikey indexes on large or deeply nested arrays to minimize performance overhead.

This use case illustrates how multikey indexes optimize queries involving arrays, making them essential for applications with dynamic, multi-valued fields like tags, categories, or attributes.

Geospatial Indexes

Scenario: A ride-hailing app needs to find the nearest drivers to a passenger requesting a ride. The app stores the locations of both passengers and drivers in MongoDB using latitude and longitude coordinates. To optimize query performance and provide fast results, the app uses geospatial indexing.

Schema: The drivers collection stores the current location of each driver as a location field using GeoJSON format:

{
   "_id": ObjectId("..."),
   "name": "John Doe",
   "location": {
      "type": "Point",
      "coordinates": [40.730610, -73.935242] // [longitude, latitude]
   },
   "status": "available"
}

Geospatial Index: To efficiently query the nearest available drivers, we create a 2dsphere index on the location field:

// Create a 2dsphere index on the location field
db.drivers.createIndex({ location: "2dsphere" });

Query: When a passenger requests a ride, the app uses the passenger's location (e.g., [40.712776, -74.005974]) to find the closest available drivers within a 5-kilometer radius:

db.drivers.find({
  location: {
    $near: {
      $geometry: {
        type: "Point",
        coordinates: [40.712776, -74.005974], // Passenger's location
      },
      $maxDistance: 5000, // 5 kilometers
    },
  },
  status: "available",
});

Result: This query returns a list of available drivers sorted by proximity to the passenger, enabling the app to quickly dispatch the closest driver. The geospatial index on the location field ensures the query is executed efficiently, even when the drivers collection contains thousands or millions of records.

Best Practice:

• Combine geospatial queries with additional filters (such as the driver's availability status) to narrow the search and improve performance.
• Use the 2dsphere index for accurate results when working with spherical data like global positioning (GPS) coordinates.

Text Indexes

Scenario: A content management system (CMS) for a blogging platform where users search for blog posts based on keywords, titles, and descriptions.

In this scenario, the posts collection contains documents with fields such as title, description, and content. Users often search the platform by entering keywords to find relevant posts. To make these searches efficient, text indexes are crucial for enabling fast full-text search across multiple fields.

Schema Example:

{
   "_id": ObjectId("..."),
   "title": "Advanced MongoDB Indexing",
   "description": "A deep dive into MongoDB's indexing capabilities and best practices.",
   "content": "In this article, we explore MongoDB indexing...",
   "author": "John Doe",
   "createdAt": ISODate("2024-01-15T12:00:00Z"),
   "tags": ["MongoDB", "indexing", "database"]
}

Applying:

To allow users to search across the title, description, and content fields, we can create a text index that spans these fields.

// Create a text index on title, description, and content fields
db.posts.createIndex({
  title: "text",
  description: "text",
  content: "text",
});

Query Example:

A user searches for posts that include the keyword "indexing" and have been published recently. This query uses the $text operator combined with additional filters.

db.posts.find({
  $text: { $search: "indexing" },
  createdAt: { $gte: ISODate("2024-01-01T00:00:00Z") },
});

Benefits of Using Text Indexes:

• Full-text search: Text indexes enable efficient searches within string fields across multiple documents and fields, such as searching for specific words or phrases in titles, descriptions, and content.
• Scalability: Text indexing provides the ability to scale search operations, making it effective even as the number of blog posts increases.
• Relevance Scoring: MongoDB text queries return results with a relevance score, allowing you to prioritize more relevant documents.

Best Practices:

• Use text indexes for fields that require search capabilities, such as titles, descriptions, and body content.
• Ensure that the fields indexed contain searchable content and that text indexes are used with other filters (e.g., date range, category) to further optimize query performance.

Wildcard Indexes

Scenario : Imagine you are developing a content management system (CMS) where users can create and store a variety of content types, including articles, blog posts, reviews, and product descriptions. Each content type has a different structure and set of fields, and users may dynamically add new fields over time. Given the dynamic nature of the content, it is challenging to predict all possible fields and their combinations in advance.

Problem : In traditional MongoDB indexing, you would need to create and maintain specific indexes for each possible field or combination of fields, which can be cumbersome and impractical due to the ever-evolving schema. Additionally, managing indexes for dynamic fields can lead to increased complexity and potential performance issues.

Solution:

Wildcard indexes are a versatile solution for this use case. They allow you to index all fields within documents or fields matching a specific pattern. This approach eliminates the need to predict and explicitly define each field to be indexed, simplifying index management for dynamic schemas.

Implementation :

Here’s how you can implement a wildcard index in the CMS:

• Create a Wildcard Index

  To handle the varying structure of documents, create a wildcard index that covers all fields in the documents collection. This ensures that any field added by users will be indexed automatically.

  // Create a wildcard index on all fields in the documents collection
  db.documents.createIndex({ "$**": 1 });
  

• Querying with Wildcard Index

  With the wildcard index in place, you can efficiently query documents regardless of the field names. For example, to find documents where a specific keyword appears in any field, you can perform a query like this:

  // Find documents where any field contains the keyword "MongoDB"
  db.documents.find({ $text: { $search: "MongoDB" } });
  

• Optimizing Queries

  Although wildcard indexes simplify indexing for dynamic fields, they can increase the size of the index and may impact performance. Therefore, monitor the index usage and query performance. For specific queries that become performance-critical, consider creating targeted indexes for those fields if their structure stabilizes.

Advantages :

• Flexibility: Wildcard indexes handle dynamic and evolving schemas without requiring constant updates to the indexing strategy.
• Simplicity: Reduces the complexity of managing multiple indexes for different fields, especially in a CMS with varied content types.

Disadvantages :

• Index Size: Wildcard indexes can grow large, especially if documents contain many fields, potentially impacting performance and storage.
• Query Performance: While wildcard indexes provide broad coverage, they may not be as optimized as specific compound indexes for complex queries.

Monitoring and Optimizing Index Performance

Using the explain() Method

MongoDB’s explain() method provides detailed information on how a query is executed, including which indexes are used. This helps in diagnosing performance issues and optimizing query execution.

Example:

db.users.find({ email: "johndoe@example.com" }).explain("executionStats");

Best Practice:

• Regularly analyze your queries with explain() to ensure they’re using indexes effectively. Look for queries that result in collection scans, as these are typically slow and can benefit from indexing.

Index Management

Indexes consume disk space and can slow down write operations, so it’s important to manage them carefully. MongoDB provides commands to view and manage indexes, such as getIndexes() and dropIndex().

Example:

// View all indexes on the users collection
db.users.getIndexes();

// Drop an index on the email field
db.users.dropIndex("email_1");

Best Practice:

• Review Indexes Regularly: Periodically review your indexes to ensure they’re still necessary. Drop unused or redundant indexes to save storage and improve write performance.
• Index TTL (Time-To-Live): For data that expires after a certain period, use TTL indexes to automatically delete documents, keeping your database size manageable.

Example:

// Create a TTL index to automatically delete logs older than 7 days
db.logs.createIndex({ createdAt: 1 }, { expireAfterSeconds: 604800 });

Compound vs. Multiple Single-Field Indexes

When optimizing query performance in MongoDB, one critical consideration is whether to use compound indexes or multiple single-field indexes. Each approach has its advantages and potential drawbacks, and the choice between them can significantly impact the efficiency of your queries.

Compound Indexes

Compound indexes involve creating an index on multiple fields within a single index structure. They are particularly beneficial for queries that filter or sort by more than one field, providing an efficient way to access documents that match complex query patterns.

Example:

// Create a compound index on the "status" and "createdAt" fields in the orders collection
db.orders.createIndex({ status: 1, createdAt: -1 });

Use Case:

• When you frequently query the orders collection to find documents with a specific status and sort them by createdAt, a compound index on these fields optimizes performance. For example, a query like this would benefit from the compound index:

db.orders.find({ status: "shipped" }).sort({ createdAt: -1 });

Best Practice:

• Field Order Matters: The order of fields in a compound index is crucial. The index is most effective when queries filter or sort in the same order as the fields in the index. For example, if you frequently filter by status and then sort by createdAt, placing status first in the index is optimal.

• Prefix Queries: MongoDB can use the prefix of a compound index, so it can be effective for queries that only use the leading portion of the index. However, if you only query on createdAt without status, the compound index may not be utilized as efficiently.

Multiple Single-Field Indexes

Multiple single-field indexes involve creating separate indexes on each field that is frequently used in queries. This approach provides flexibility but can be less efficient than compound indexes for certain query patterns.

Example:

// Create separate indexes on the "status" and "createdAt" fields in the orders collection
db.orders.createIndex({ status: 1 });
db.orders.createIndex({ createdAt: -1 });

Use Case:

• Single-field indexes are beneficial for queries that filter or sort by one field at a time. For instance, if you often query the orders collection based on the status field alone, a single-field index on status will speed up these queries:

db.orders.find({ status: "shipped" });

Best Practice:

• Index Intersection: MongoDB can use index intersection to combine multiple single-field indexes when executing a query. While this allows for some flexibility, it can be less efficient compared to a compound index, as it involves scanning multiple indexes and merging results.

• Query Performance: For queries that involve multiple fields, multiple single-field indexes may not provide the same level of performance as a well-designed compound index. The database may need to perform more complex operations to intersect multiple indexes.

Choosing Between Compound and Single-Field Indexes

• Query Patterns: Analyze your query patterns to determine whether your queries filter or sort by multiple fields together or individually. For complex queries involving multiple fields, compound indexes are generally more efficient.

• Field Selectivity: Ensure the most selective (high-cardinality) field is placed first in a compound index to maximize its effectiveness. For queries that use prefixes of the compound index, this will help in utilizing the index more efficiently.

• Performance Testing: Regularly test and analyze your queries using the explain() method to determine the effectiveness of your indexes. Monitor performance to make informed decisions about when to use compound indexes versus multiple single-field indexes.

Practical Example: Optimizing a Complex Query

To illustrate the application of indexing best practices in MongoDB, let's consider a practical example where we need to optimize a complex query for an e-commerce application. The goal is to find products based on a search term, price range, and stock availability.

Scenario

Your e-commerce application has a products collection with the following schema:

{
   "_id": ObjectId("..."),
   "name": "Laptop",
   "description": "High-performance laptop with 16GB RAM",
   "price": 1200,
   "stock": 50,
   "tags": ["electronics", "laptop"]
}

You frequently run a query to search for products that:

1. Match a search term in either the name or description field.
2. Are within a specified price range.
3. Have a non-zero stock.

Example Query:

db.products.find({
  $text: { $search: "laptop" },
  price: { $gte: 1000, $lte: 1500 },
  stock: { $gt: 0 },
});

To optimize this query, follow these steps:

Create Text Indexes

Since the query involves a full-text search on name and description, create a text index on these fields. This index will accelerate the text search process.

Code Example:

db.products.createIndex({ name: "text", description: "text" });

Explanation:

• The text index allows MongoDB to efficiently perform text searches on the name and description fields. Ensure that the search term is indexed to speed up text searches.

Create Compound Index for Price and Stock

For the price range and stock conditions, create a compound index that includes both the price and stock fields. This index will optimize range queries and filter conditions.

Code Example:

db.products.createIndex({ price: 1, stock: -1 });

Explanation:

• The compound index on price and stock supports efficient querying for products within a price range and with non-zero stock. The order of fields (price first, stock second) ensures that the index is used effectively for both filtering and sorting.

Analyze Query Performance with explain()

Use the explain() method to check how MongoDB executes the query and ensure that indexes are being utilized as expected.

Code Example:

db.products
  .find({
    $text: { $search: "laptop" },
    price: { $gte: 1000, $lte: 1500 },
    stock: { $gt: 0 },
  })
  .explain("executionStats");

Explanation:

• The explain() output will provide insights into whether the query uses the text index and compound index, and how efficiently it performs. Look for metrics such as executionTimeMillis and totalDocsExamined to assess performance.

Review and Optimize Indexes

Regularly review your indexes based on query patterns and performance metrics. Drop unused or redundant indexes to save resources and improve write performance.

Code Example:

// Drop an unused index if necessary
db.products.dropIndex("name_text_description_text");

Explanation:

• Regular index review ensures that your indexes remain relevant to your query patterns and that the database performance remains optimal.

By following these steps, you can significantly enhance the performance of complex queries in MongoDB. Creating appropriate indexes tailored to your query needs and analyzing query execution with explain() helps ensure that your MongoDB database performs efficiently, even as data volume and complexity increase.

Next.Js FAQ

db.orders.createIndex(
  { status: 1 },
  { partialFilterExpression: { status: { $exists: true } } },
);

db.locations.createIndex({ location: "2dsphere" });

// Find restaurants near a specific point
db.locations.find({
  location: {
    $near: {
      $geometry: { type: "Point", coordinates: [-73.97, 40.77] },
      $maxDistance: 5000, // 5 kilometers
    },
  },
});

db.articles.createIndex({ content: "text" });

// Search for articles containing the word "mongodb"
db.articles.find({ $text: { $search: "mongodb" } });

// Check how a query is using indexes
db.orders.find({ customerId: "123" }).explain("executionStats");

// Output will show if the query used an index, the number of documents scanned, and overall performance stats

Summary

This article explores best practices for indexing in MongoDB, focusing on optimizing query performance in large datasets. Indexes are critical for efficient data access, and their design must be based on the specific query patterns used in your application.

Key types of indexes include:

• Single-field indexes: Used for simple queries on a single field.
• Compound indexes: Useful when filtering or sorting by multiple fields, where field order plays a crucial role.
• Multikey indexes: Effective for queries on arrays, indexing each element within the array.
• Geospatial indexes: Applied to queries involving spatial data like locations.
• Text indexes: Enable full-text search for string content within fields.
• Wildcard indexes: Useful for dynamic schemas where fields are not known in advance.

Best practices include using the explain() method to analyze query execution, managing indexes to avoid redundant or unused indexes, and using compound indexes for frequent query patterns. Additionally, it is important to balance indexing needs with the impact on write performance, as each index increases storage and write overhead.

Check out this article https://medium.com/@farihatulmaria/what-are-the-best-practices-for-indexing-in-mongodb-to-optimize-query-performance-c2bea64453fb