Code Summary: Optimizing MongoDB Queries with Indexes

Covered Queries

A covered query is a query that can be satisfied entirely using an index and does not have to examine any documents. An index covers a query when all of the following apply:

• All the fields in the query (both as specified by the application and as needed internally such as for sharding purposes) are part of an index.
• All the fields returned in the results are in the same index.
• No fields in the query are equal to null. For example, the following query predicates cannot result in covered queries:
  • { "field": null }
  • { "field": { $eq: null } }

For example, the listingsAndReviews collection has the following index on the name and summary fields.

db.listingsAndReviews.createIndex({name: 1, summary: 1 } )

The index covers the following operation which queries on the name and summary fields and returns only those fields:

db.listingsAndReviews.find({name: 'Private Room in Bushwick'}, {name: 1, summary: 1, _id:0})

Validating a Covered Query

To validate the query was covered by the index, use the explain() command:

db.listingsAndReviews.find({name: 'Private Room in Bushwick'}, {name: 1, summary: 1, _id:0}).explain()

You should be able to see PROJECTION_COVERED for the stage under the winningPlan section:

winningPlan: {
      isCached: false,
      stage: 'PROJECTION_COVERED',
      transformBy: { name: 1, summary: 1, _id: 0 },
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: { name: 1, summary: 1 },
        indexName: 'name_1_summary_1',
        isMultiKey: false,
        multiKeyPaths: { name: [], summary: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: {
          name: [
            '["Private Room in Bushwick", "Private Room in Bushwick"]'
          ],
          summary: [ '[MinKey, MaxKey]' ]
        }
      }
    }