Query Optimization

Create Indexes to Support Queries

Create indexes for commonly issued queries. If a query searches multiple fields, create a compound index. Using an index improves performance because without an index the query must scan every document in the collection.

For example, consider the following query on the type field in the inventory collection:

let typeValue = <someUserInput>;
db.inventory.find( { type: typeValue } );

To improve performance for this query, add an index to the inventory collection on the type field. [1] In mongosh, create indexes using the db.collection.createIndex() method:

db.inventory.createIndex( { type: 1 } )

To analyze query performance, see Interpret Explain Plan Results.

Create Selective Queries

Query selectivity refers to how well the query predicate filters out documents in a collection. Query selectivity determines whether queries can use indexes effectively.

More selective queries match a smaller percentage of documents. For instance, an equality match on the unique _id field is highly selective as it can match at most one document.

Less selective queries match a larger percentage of documents and cannot use indexes effectively.

For instance, the inequality operators $nin and $ne are not very selective since they often match a large portion of the index. As a result, in many cases, a $nin or $ne query with an index may perform no better than a $nin or $ne query that must scan all documents in a collection.

The selectivity of regular expressions depends on the expressions themselves. For details, see regular expression and index use.

Project Only Necessary Data

When you need a subset of fields from documents, you can improve performance by returning only the fields you need. Projections reduce network traffic and processing time.

For example, if your query to the posts collection needs only the timestamp, title, author, and abstract fields, specify those fields in the projection:

db.posts.find(
   {},
   { timestamp : 1, title : 1, author : 1, abstract : 1}
).sort( { timestamp : -1 } )

When you use a $project aggregation stage it should typically be the last stage in your pipeline, used to specify which fields to return to the client.

Using a $project stage at the beginning or middle of a pipeline to reduce the number of fields passed to subsequent pipeline stages is unlikely to improve performance, because the database performs this optimization automatically.

For more information on using projections, see Project Fields to Return from Query.

Limit Query Results

MongoDB cursors return results in batches. If you know the number of results you want, specify that value in the limit() method. Limiting results reduces the demand on network resources.

Generally, limiting results is most useful when results are sorted so you know which documents will be returned. For example, if you need only 10 results from your query to the posts collection, run the following query:

db.posts.find().sort( { timestamp : -1 } ).limit(10)

For more information on limiting results, see limit().

Use Index Hints

The query optimizer typically selects the optimal index for a specific operation. However, you can force MongoDB to use a specific index using the hint() method. Use hint() to support performance testing or when you are querying a field that appears in several indexes to guarantee that MongoDB uses the correct index.

Use Server-Side Operations

Use MongoDB's $inc operator to increment or decrement values in documents. The operator increments the value of the field on the server side, as an alternative to selecting a document, making simple modifications in the client, and then writing the entire document to the server. The $inc operator can also help avoid race conditions that occur when two application instances query for a document, manually increment a field, and save the entire document back at the same time.

Run 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 } }

db.inventory.createIndex( { type: 1, item: 1 } )

db.inventory.find(
   { type: "food", item:/^c/ },
   { item: 1, _id: 0 }
)

db.userdata.insertOne(
   { _id: 1, user: { login: "tester" } }
)

db.userdata.createIndex(
   { "user.login": 1 }
)

db.userdata.find(
   { "user.login": "tester" },
   { "user.login": 1, _id: 0 }
)