MongoDB Index Usage

Introduction

MongoDB indexes serve as critical tools for enhancing database performance. While creating appropriate indexes is the first step, understanding how MongoDB uses these indexes and confirming they're being utilized effectively is equally important. This guide will help you understand how MongoDB leverages indexes, how to verify index usage, and how to optimize queries with proper index selection.

Why MongoDB Index Usage Matters

Imagine having a well-organized library with an excellent catalog system, but the librarians never consult it when looking for books. Similarly, having indexes doesn't guarantee MongoDB will use them for your queries. Understanding index usage helps you:

• Verify that your queries are using indexes as intended
• Identify queries that aren't leveraging available indexes
• Diagnose performance issues by analyzing how MongoDB executes queries
• Make informed decisions about creating, modifying, or dropping indexes

Checking Index Usage

Using explain()

The explain() method is your primary tool for analyzing how MongoDB executes queries and whether it uses indexes.

javascript

db.collection.find({ field: "value" }).explain()

This outputs detailed information about query execution. For more specific information, you can use different verbosity modes:

javascript

// Basic mode - shows query planner info
db.collection.find({ field: "value" }).explain("queryPlanner")

// Execution mode - includes execution statistics
db.collection.find({ field: "value" }).explain("executionStats")

// All plans mode - most detailed information
db.collection.find({ field: "value" }).explain("allPlansExecution")

Let's look at a practical example:

javascript

// Create a sample collection with data
db.products.insertMany([
  { name: "Laptop", price: 1200, category: "Electronics" },
  { name: "Phone", price: 800, category: "Electronics" },
  { name: "Desk", price: 350, category: "Furniture" },
  { name: "Chair", price: 150, category: "Furniture" }
]);

// Create an index on the category field
db.products.createIndex({ category: 1 });

// Now examine a query using explain
db.products.find({ category: "Electronics" }).explain("executionStats");

Output:

javascript

{
  "queryPlanner": {
    "plannerVersion": 1,
    "namespace": "test.products",
    "indexFilterSet": false,
    "parsedQuery": {
      "category": { "$eq": "Electronics" }
    },
    "winningPlan": {
      "stage": "FETCH",
      "inputStage": {
        "stage": "IXSCAN",
        "keyPattern": { "category": 1 },
        "indexName": "category_1",
        // other index details...
      }
    },
    "rejectedPlans": []
  },
  "executionStats": {
    "executionSuccess": true,
    "nReturned": 2,
    "executionTimeMillis": 0,
    "totalKeysExamined": 2,
    "totalDocsExamined": 2,
    // other stats...
  },
  // other information...
}

Key elements to look for in explain output

1. Stage: In the winning plan, look for:

   • IXSCAN: Indicates an index scan (good)
   • COLLSCAN: Indicates a full collection scan (potentially problematic)
   • FETCH: Retrieves documents after finding them via an index
   • SORT: Performs an in-memory sort (could be expensive)

2. executionStats:

   • nReturned: Number of documents returned
   • totalKeysExamined: Number of index keys examined
   • totalDocsExamined: Number of documents examined
   • executionTimeMillis: Time taken for the query to execute

Index Usage Statistics

MongoDB provides statistics on index usage since server start-up, which helps identify unused or underused indexes:

javascript

db.collection.aggregate([
  { $indexStats: {} }
])

Example output:

javascript

[
  {
    "name": "category_1",
    "key": { "category": 1 },
    "host": "mongodb:27017",
    "accesses": {
      "ops": 42,
      "since": ISODate("2023-06-01T10:30:15Z")
    }
  },
  {
    "name": "_id_",
    "key": { "_id": 1 },
    "host": "mongodb:27017",
    "accesses": {
      "ops": 105,
      "since": ISODate("2023-06-01T10:30:15Z")
    }
  }
]

This shows how many times each index has been used. Indexes with low or zero usage might be candidates for removal to optimize write performance and storage space.

Common Index Usage Patterns

Covered Queries

A covered query is one where all the fields in the query are part of an index, and all the fields returned are also part of that same index. MongoDB doesn't need to access the actual documents, making these queries very efficient.

javascript

// Create a compound index
db.customers.createIndex({ lastName: 1, firstName: 1, email: 1 });

// A covered query that only returns fields in the index
db.customers.find(
  { lastName: "Smith" },
  { _id: 0, lastName: 1, firstName: 1, email: 1 }
).explain("executionStats");

In the explain output, you'll see "totalDocsExamined": 0, indicating that no documents were accessed, only the index.

Sort Operations

Indexes significantly improve sorting performance. Without an appropriate index, MongoDB must load all matching documents into memory to sort them.

javascript

// Bad - full collection scan with in-memory sort
db.products.find().sort({ price: -1 }).explain();

// Create index to optimize
db.products.createIndex({ price: -1 });

// Now the sort uses the index
db.products.find().sort({ price: -1 }).explain();

Multi-key Indexes

When querying arrays, MongoDB can use multi-key indexes:

javascript

// Create a collection with array fields
db.articles.insertMany([
  { title: "MongoDB Basics", tags: ["database", "nosql", "beginner"] },
  { title: "Advanced MongoDB", tags: ["database", "nosql", "advanced"] }
]);

// Create index on the array field
db.articles.createIndex({ tags: 1 });

// This query will use the index
db.articles.find({ tags: "nosql" }).explain();

Common Index Usage Issues

Index Intersection vs. Compound Indexes

MongoDB can use multiple indexes for a single query (index intersection), but a compound index is often more efficient:

javascript

// Separate indexes
db.users.createIndex({ age: 1 });
db.users.createIndex({ status: 1 });

// MongoDB might use both for this query
db.users.find({ age: { $gt: 30 }, status: "active" }).explain();

// A compound index is typically more efficient
db.users.createIndex({ age: 1, status: 1 });

Index Prefix

MongoDB can use an index efficiently if the query matches a prefix of the compound index. For example, with an index on { a: 1, b: 1, c: 1 }:

• Queries on a will use the index
• Queries on a and b will use the index
• Queries on b alone won't use the index efficiently
• Queries on a, b, and c will use the index fully

javascript

// Create a compound index
db.inventory.createIndex({ department: 1, category: 1, price: 1 });

// These will use the index efficiently
db.inventory.find({ department: "Electronics" }).explain();
db.inventory.find({ department: "Electronics", category: "Laptops" }).explain();

// This won't use the index efficiently
db.inventory.find({ category: "Laptops" }).explain();

Using Indexes for Range Queries

For queries that include both equality and range conditions, place equality fields first in your compound index:

javascript

// Efficient index for queries with both equality and range conditions
db.products.createIndex({ category: 1, price: 1 });

// This uses the index effectively
db.products.find({ category: "Electronics", price: { $gt: 500 } }).explain();

Real-world Examples

E-commerce Product Search

Imagine an e-commerce platform with millions of products:

javascript

// Collection structure
db.products.insertOne({
  name: "Ultra HD Smart TV",
  brand: "TechVision",
  category: "Electronics",
  subcategory: "Televisions",
  price: 899.99,
  inStock: true,
  features: ["4K", "HDR", "Smart", "WiFi"],
  ratings: { average: 4.7, count: 253 }
});

// Create indexes for common queries
db.products.createIndex({ category: 1, subcategory: 1 });
db.products.createIndex({ price: 1 });
db.products.createIndex({ "ratings.average": -1 });
db.products.createIndex({ features: 1 });
db.products.createIndex({ inStock: 1, category: 1, price: 1 });

// Common query patterns:
// 1. Browse by category and subcategory
db.products.find({ 
  category: "Electronics", 
  subcategory: "Televisions" 
}).explain("executionStats");

// 2. Filter by price range within a category
db.products.find({
  category: "Electronics",
  price: { $lt: 1000 }
}).explain("executionStats");

// 3. Find in-stock products by category, sorted by price
db.products.find({
  inStock: true,
  category: "Electronics"
}).sort({ price: 1 }).explain("executionStats");

User Activity Tracking

Consider a user activity tracking system:

javascript

// Collection structure
db.userActivity.insertOne({
  userId: ObjectId("5f8d7a6b9d3e2c1a3b5c7d9e"),
  action: "pageView",
  path: "/products/electronics",
  timestamp: ISODate("2023-06-01T15:32:56Z"),
  sessionId: "sess_12345",
  device: {
    type: "mobile",
    browser: "Chrome",
    os: "Android"
  }
});

// Create indexes for common queries
db.userActivity.createIndex({ userId: 1, timestamp: -1 });
db.userActivity.createIndex({ timestamp: -1 });
db.userActivity.createIndex({ action: 1, timestamp: -1 });

// 1. Get recent user activity
db.userActivity.find({
  userId: ObjectId("5f8d7a6b9d3e2c1a3b5c7d9e")
}).sort({ timestamp: -1 }).limit(10).explain("executionStats");

// 2. Analyze activity types within a timeframe
db.userActivity.find({
  action: "pageView",
  timestamp: {
    $gte: ISODate("2023-06-01T00:00:00Z"),
    $lt: ISODate("2023-06-02T00:00:00Z")
  }
}).explain("executionStats");

Monitoring and Optimizing Index Usage

Using MongoDB Compass

MongoDB Compass provides a visual interface to analyze query performance and index usage:

1. Connect to your database
2. Navigate to your collection
3. Go to the "Explain Plan" tab
4. Enter your query and analyze the results

Using the MongoDB Atlas Performance Advisor

If you're using MongoDB Atlas, the Performance Advisor automatically suggests indexes based on your query patterns:

1. Go to your Atlas Cluster
2. Click on "Performance Advisor"
3. Review the suggested indexes

Identifying Unused Indexes

Regularly check for unused indexes to maintain optimal write performance:

javascript

// Get index usage statistics
db.collection.aggregate([{ $indexStats: {} }]);

// Review the "ops" count to identify unused indexes

Best Practices for Index Usage

1. Use explain() regularly to verify your queries are using indexes efficiently

2. Create indexes to support your query patterns, not just based on the data model

3. Consider the order of fields in compound indexes based on your query conditions

4. Be aware of index limitations with certain operators and expressions

5. Watch for collection scans in your explain output, especially for large collections

6. Monitor index size and memory usage to ensure your working set fits in memory

7. Balance read and write performance - indexes improve reads but can slow writes

8. Consider dropping unused indexes to improve write performance

Summary

Understanding MongoDB index usage is crucial for application performance. By analyzing how MongoDB executes queries using tools like explain() and monitoring index statistics, you can ensure your database operations are efficient. Remember that creating indexes isn't enough—they must be used effectively by your queries to provide performance benefits.

Key takeaways:

• Use explain() to verify index usage in your queries
• Pay attention to IXSCAN vs. COLLSCAN stages in the query plan
• Monitor index usage statistics to identify unused indexes
• Structure compound indexes based on query patterns
• Consider covered queries for optimal performance
• Balance read and write performance considerations

Additional Resources

• MongoDB Documentation: Analyze Query Performance
• MongoDB University: M201 MongoDB Performance Course
• Practice analyzing your index usage with different query structures
• Try experimenting with different index types (compound, multikey, text) and observe how queries utilize them

Exercises

1. Create a sample collection with at least 1,000 documents and experiment with different indexes and queries, using explain() to analyze performance.

2. Compare the performance of a query using separate indexes versus a compound index.

3. Create a query that results in a covered query and verify with explain() that no documents are examined.

4. Identify a scenario where an index is not being used despite being available, then modify either the query or the index to improve performance.

5. Use $indexStats to identify the most and least used indexes in one of your collections.