Mongodb Covered Queries And Index Hints Complete Guide

Complete Examples, Step-by-Step for Beginners: MongoDB Covered Queries and Index Hints

Prerequisites

• Basic knowledge of MongoDB
• MongoDB installation (you can use a local instance or a cloud service like MongoDB Atlas)

Covered Queries in MongoDB

A covered query is a query that retrieves all the required data directly from the index, without scanning the documents in the collection. This results in faster query execution times because it avoids I/O operations on the document.

Example Scenario

Let's say you have a collection named products with documents like this:

{
  "_id": ObjectId("..."),
  "product_name": "Widget",
  "price": 29.99,
  "category": "Gadgets"
}

Step 1: Create the Collection and Insert Sample Data

First, create the products collection and insert some sample documents.

use mydatabase;

db.products.insertMany([
  { product_name: "Widget", price: 29.99, category: "Gadgets" },
  { product_name: "Gadget", price: 19.99, category: "Gadgets" },
  { product_name: "Tech Tool", price: 49.99, category: "Tools" },
  { product_name: "Tool Set", price: 99.99, category: "Tools" },
  { product_name: "Doodad", price: 29.99, category: "Gadgets" }
]);

Step 2: Create an Index

To enable covered queries, you'll need to create an index that includes all the fields required by the query. For example, consider a query that retrieves product_name and price for Gadgets:

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

Step 3: Run a Covered Query

Now, run a query that can be covered by the index.

db.products.find(
  { category: "Gadgets" },
  { product_name: 1, price: 1, _id: 0 }
).explain("executionStats");

In the explain output, look at the covered field in the queryPlanner section. If the value is true, the query is covered.

Verifying Covered Query

db.products.find({
  category: "Gadgets"
}, {
  product_name: 1,
  price: 1,
  _id: 0
}).explain("executionStats");

Check the output:

{
  "queryPlanner": {
    "winningPlan": {
      "stage": "PROJECTION_SIMPLE",
      "transformBy": {
        "product_name": 1,
        "price": 1,
        "_id": 0
      },
      "inputStage": {
        "stage": "FETCH",
        "inputStage": {
          "stage": "IXSCAN",
          "keyPattern": {
            "category": 1,
            "product_name": 1,
            "price": 1
          },
          "indexName": "category_1_product_name_1_price_1",
          // ...
        }
      }
    },
    "rejectedPlans": [],
    "coveredIndexScanPlans": [
      {
        "stage": "PROJECTION_SIMPLE",
        "transformBy": {
          "product_name": 1,
          "price": 1,
          "_id": 0
        },
        "inputStage": {
          "stage": "IXSCAN",
          "keyPattern": {
            "category": 1,
            "product_name": 1,
            "price": 1
          },
          "indexName": "category_1_product_name_1_price_1",
          // ...
        }
      }
    ]
  },
  // ...
  "executionStats": {
    "nReturned": 3,
    "executionSuccess": true,
    "nScannedObjects": 0, // Indicates no documents were scanned
    "nYields": 0,
    "millis": 1,
    // ...
  }
}

Notice that nScannedObjects is 0, and there's an entry in coveredIndexScanPlans indicating that a covered plan was used.

Index Hints in MongoDB

An index hint in MongoDB is a directive to the query optimizer to use a specific index for a query. This can be useful when you have multiple indexes on a collection and want to control which one MongoDB uses.

Example Scenario

Continuing with the products collection, let's create another index and use an index hint to force MongoDB to use a particular index.

Step 1: Create an Additional Index

Create another index on the price field.

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

Step 2: Run a Query Without Hint

First, run a query without specifying an index hint to see which index MongoDB chooses.

db.products.find({ price: { $gt: 29 } }).explain("executionStats");

Step 3: Use an Index Hint

Now, use an index hint to force MongoDB to use the price index.

db.products.find({ price: { $gt: 29 } }).hint({ price: -1 }).explain("executionStats");

Verifying Index Hint

db.products.find({ price: { $gt: 29 } }).hint({ price: -1 }).explain("executionStats");

Check the output:

{
  "queryPlanner": {
    "winningPlan": {
      "stage": "FETCH",
      "inputStage": {
        "stage": "IXSCAN",
        "keyPattern": {
          "price": -1
        },
        "indexName": "price_-1",
        // ...
      }
    },
    // ...
  },
  "executionStats": {
    "nReturned": 4,
    "executionSuccess": true,
    // ...
  }
}

In the executionStats output, you can see the winningPlan stage indicates that MongoDB used the IXSCAN stage with the price_-1 index, confirming that the index hint was applied.

Summary

• Covered Queries: Queries that retrieve all required data from an index without scanning documents. Use explain("executionStats") to verify.
• Index Hints: Directives to MongoDB optimizer to use a specific index. Use the hint() method to apply an index hint.

By understanding and using covered queries and index hints, you can significantly improve the performance of your MongoDB queries.

Further Reading

• MongoDB Documentation on Covered Queries
• MongoDB Documentation on Index Hints