Video Transcript (English)

So far, we’ve focused on the first two phases of tuning queries: identifying slow queries and then analyzing them.

So now let’s focus on the third phase, optimization.

Optimizing queries keeps your application fast, efficient, and scalable. This can save resources, cut costs, and result in higher user satisfaction.

In this video, we’ll use the MongoDB Atlas Performance Advisor to optimize queries by adding indexes. We’ll also cover indexing strategies for projections and expiring data, which will further enhance the performance of our queries. Let’s get started.

The MongoDB Atlas Performance Advisor is a tool available in M10 and higher clusters. It monitors and analyzes the queries executed in our database and provides actionable recommendations to enhance response times and efficiency.

Some of its key features include: Index Suggestions to enhance data retrieval speed, Slow Query Identification, Identifying unused indexes to find those that are consuming resources, Schema recommendations for data modeling changes, And finally, Query modifier suggestions to advise on how to refactor queries for better performance.

Ok, let’s access the Performance Advisor. First we need to log into our MongoDB Atlas account.

We select our cluster and then click here to go to the overview page.

From here we click the Performance Advisor tab.

This takes us to the Optimize performance page of the Advisor.

This is where the Performance Advisor provides suggestions for optimizing our queries. These recommendations are based on real-time analysis of query patterns.

In this case, we see that the performance advisor doesn’t have any Schema recommendations but does recommend adding indexes to our cluster. This number tells us how many index recommendations it suggests. By clicking here, we can look at the specifics.

On the create indexes page, the suggestions are listed in the order of impact. Each suggestion provides the namespace for the recommendation, the suggested index structure, and metadata about the anticipated impact.

Below the suggested indexes, we also see which indexes currently exist, and the queries that will be directly improved by adding that particular index.

By viewing the sample queries for each index, we can decide which of the indexes we want to create.

Looking now at a sample query, we can see the query matches on the property type and room type fields, and contains a range query on the number of nights and bedrooms.

The queries also include a sort operation on the price field.

From the suggested index metadata, we also know that the queries scanned a lot of documents to get the result set. Let’s go ahead and create this index and evaluate the improvements.

The Performance Advisor makes it super easy to add a recommended index to our collection.

It just takes the click of a button - the create index button here. This brings up a modal with the index information. By clicking review, we can then confirm that we want to build the index. The success modal lets us know the index build is in progress. Depending on the size of the dataset, this may take a few seconds or several minutes.

Now that we’ve created the index, we can test and evaluate the query by using explain.

Let’s run the query again in mongosh.

Wow! The query is now below the 100 millisecond threshold. What an improvement.

We can see that the query is now performing an index scan using the index we just created, and the execution time has dropped from multiple seconds to just a few milliseconds. Nice!

Now, what about queries that use a projection for the results? Remember that a query of a document in a collection in MongoDB returns all fields of each matching document. However, sometimes you only need specific fields from those documents. Using projection helps you achieve this. Projections can be optimized even further by using another indexing strategy to cover the queries completely.

A covered query is one where all the fields required by the query (including filters, projections, and sort criteria) are contained within an index. For example, this query filters equality matches to property type and room type, and a range filter on bedrooms. It also includes a projection for the listing name, and finally, sorts by price. All of these fields are contained in this index. This means MongoDB can retrieve the data directly from the index, bypassing the need to access the documents. This reduces both disk I/O and in-memory data processing, speeding up query execution.

Here’s an example. This query filters on a variety of fields using equality and range filters. The query uses a projection to define which fields it will return, and then sorts the results by price from highest to lowest.

Looking at the explain output from this query, we can see that it took a long time to run, examined over 1700 documents but returned just a few, and used a projection to limit the results to specific fields.

Let's take each of the fields used in the query and create an index that covers the query.

We'll start with the equality filters. Then, we will use price as the next item, since it’s used both as a range filter and for the sort. Then we will use the other range filters, and finish up with the projected fields. That gives us the create index statement seen here.

Now let’s create the index and run the query to see the improvements.

I'll enter the create index command into MongoSH.

Now that it is built, let's run the query with the explain command.

This time, the query used the index, the projection was covered as seen here, and the execution time was in the single millisecond range and 0 documents were examined because the index contained all of the data needed to satisfy the query.

Well done! In this video, you learned how to optimize slow queries by using the MongoDB Atlas Performance Advisor.

We covered how to access the Performance Advisor tab in Atlas and review its recommendations.

We also explored how this tool makes it simple to implement index recommendations.

Finally, we optimized a query that used a projection. Since we knew the exact fields required by the query, we built a covered index that retrieved the entire resultset from the index itself.

Great job putting all these strategies into action!