What is a 'Covered Query' and how do you design one using Projection?

Question

Accepted Answer

A covered query in MongoDB is a performance optimization where all the data required by a query can be found within the index itself, eliminating the need for MongoDB to fetch documents from the collection resulting in significantly faster query execution because index data is typically cached in memory and requires fewer disk I/O operations. For a query to be covered, three conditions must be met: First, all fields in the query filter must be part of an index. Second, all fields returned in the projection must be included in the same index. Third, none of the query fields can be searching for null values (i.e., `{field: null}` or `{field: {$eq: null}}`). Additionally, when working with array fields, covered queries have limitations because multikey indexes cannot cover queries over array fields. ```javascript
// Step 1: Create an index that includes all necessary fields
db.users.createIndex(
  { "firstName": 1, "lastName": 1, "email": 1 }
)

// Step 2: Query with projection that excludes _id and includes only indexed fields
// This query is NOT covered (includes _id by default)
db.users.find(
  { "firstName": "John", "lastName": "Doe" },
  { "firstName": 1, "lastName": 1, "email": 1 }
)

// This query IS covered (_id explicitly excluded)
db.users.find(
  { "firstName": "John", "lastName": "Doe" },
  { "_id": 0, "firstName": 1, "lastName": 1, "email": 1 }
)

// Step 3: Verify with explain() - look for PROJECTION_COVERED stage
// totalDocsExamined should be 0
db.users.find(
  { "firstName": "John", "lastName": "Doe" },
  { "_id": 0, "firstName": 1, "lastName": 1, "email": 1 }
).explain("executionStats")
```

The key insight is that `_id` is returned by default even when not specified in the projection[citation:2][citation:10]. To achieve a covered query, you must explicitly exclude `_id` with `{_id: 0}` unless it's included in your index[citation:2]. You can verify whether a query is covered by examining the explain plan. Look for two key indicators: a `PROJECTION_COVERED` stage in the execution plan, and `totalDocsExamined: 0` in the execution statistics. If you see a `FETCH` stage or `totalDocsExamined` greater than zero, the query is not covered and MongoDB is reading documents from the collection. ```javascript
// Covered query output
"executionStats" : {
  "executionSuccess" : true,
  "nReturned" : 2048,
  "totalKeysExamined" : 5118976,
  "totalDocsExamined" : 0,  // Zero documents fetched!
  "executionStages" : {
    "stage" : "PROJECTION_COVERED",  // Covered query indicator
    // ...
  }
}

// Non-covered query output
"executionStats" : {
  "executionSuccess" : true,
  "nReturned" : 2048,
  "totalKeysExamined" : 5118976,
  "totalDocsExamined" : 2048,  // Documents were fetched
  "executionStages" : {
    "stage" : "PROJECTION_SIMPLE",  // Not covered
    "inputStage" : {
      "stage" : "FETCH",  // Fetch stage present
      // ...
    }
  }
}
```

The presence of `PROJECTION_COVERED` and `totalDocsExamined: 0` confirms a covered query[citation:2][citation:8]. **Multikey indexes** cannot cover queries over array fields. If any field in the index is an array, the index is multikey and cannot provide covered queries, even for parts of the query that don't involve the array[citation:1][citation:4][citation:5]. **$expr queries** currently do not support covered queries. When using `$expr` with `$match`, MongoDB cannot determine that only indexed fields are needed, forcing a document fetch. **Partial indexes** can be used with covered queries, but the partial filter expression must be carefully designed[citation:7]. **Update operations** cannot be covered. Even if the query part is covered, the update itself requires fetching the document to modify it. Consider a collection of users where you frequently search by phone number and need only the name fields. Start by creating a compound index on `phoneNumber`, `firstName`, and `lastName`[citation:8]. When querying, explicitly project only the fields you need and exclude `_id`. The explain plan will show a `PROJECTION_COVERED` stage and `totalDocsExamined: 0`, confirming the query is covered[citation:8]. This optimization can dramatically improve query performance, especially for high-traffic queries[citation:10].

What is a 'Covered Query' and how do you design one using Projection?

What is a 'Covered Query' and how do you design one using Projection?

Scenario Questions

Follow-up Questions