Video Transcript (English)

Hello! In this video, you’ll learn about compound indexes. In particular, we'll cover what compound indexes are and how to create them.

You’ll also learn how to use a compound index to sort query results and cover queries.

A compound index is an index on multiple fields.

Compound indexes can also be multikey indexes if they include an array field, with the limitation of only one array field per index.

Compound indexes can cover and support queries that match on the prefix of the index fields.

Let’s look at an example to explain this point in more detail.

The customers collection contains details about customers such as their name, their birthdate, and whether they’re active. We use the createIndex method to create an index on the active and birthdate fields.

Any query that contains active or sorts by active can use the index, because active is the first field, or prefix, in the index.

Queries that do not include the index prefix can’t use the index, even if they have other fields included in the index.

This is because indexes are ordered structures. The order of the fields in a compound index matters.

In general, when we’re defining the list of fields in compound indexes, we want fields tested for equality first, followed by fields that are used for sort or range.

Additionally, if we require specific sorted results in our queries, the sort order of the field values in the index also matters to avoid in-memory sorts.

Let’s examine this in more detail.

Equality predicates test exact matches on a single value, like we do when searching for a specific active customer or birthdate value. Those fields should be placed first in a compound index. This reduces the processing time for a query, and retrieves fewer documents.

Sort predicates determine the order of results. For instance, we can sort by name in ascending order, or by birthdate in descending order. The sort fields should be included in the index after the equality fields. This is because once we select a specific value for the first fields, the other field values will be in order.

The sort order of these field values in the index is important if the query results are sorted by more than one field, and they mix sort orders (that is, some fields are in ascending order and other fields are in descending order).

Range filters scan fields, as shown in this example with birthdate values between 1977 and 1995.

Note: In general, we recommend that Range and Sort should be placed after Equality in the index to avoid in memory sort or filtering.

Let’s review this with an example. We’ll use a database called sample_analytics and the customers collection..

We want to run this query on the customers collection to:

Find all the documents where the birthdate is greater or equal to 1st January 1977, and the account id is active.

Then sort the results by birthdate in descending order, and the name in ascending order.

The results of this query are returned in order based on birthdate descending and name ascending. All results match the filter criteria.

Let’s use the same query as before, and execute the explain method on the query to understand what MongoDB is doing to retrieve the data.

First, MongoDB performs an index scan over one of the existing indexes. In this case, it’s the single field index on birthdate. Then it executes a fetch that uses a filter looking for the active customers. Finally, it performs an memory sort based on birthdate in descending order and name in ascending order.

We can improve the performance of the query by creating an index that better supports it. To do this, we use the createIndex command and order the fields according to the guideline of Equality first followed by Sort.

We include the active field first, because we query by using equality on this field. Birthdate and name are placed next, because we sort by these fields. The sort order of these fields matters because they mix descending and ascending orders in the query.

We’ll sort birthdate in descending order with -1 and name in ascending order with 1 in the index, following the same pattern as in the query. We could also sort them with birthdate in ascending order and name in descending order. Both indexes will work. For this example, we'll use the first version, with the birthdate set to -1 and the name set to 1.

We also have a range query on birthdate, but this field is already included in the list.

Perfect! The compound index is created.

To verify if the new index is being used, we’ll run the explain query again.

The winning plan shows the new index that we created is being used in the index scan.hen a FETCH stage is performed based on the index results, reading only the necessary documents from the collection.

In most cases, the FETCH stage can be avoided if we only project fields that are in the index. Let’s go through an example of a compound index that covers a query.

To the previous query, we’ll add a projection in the find command to return only name and birthdate, excluding the default _id field.

Now the documents returned by the query only have those fields.

Now, with this new query, let’s use the explain method again to review the execution plan.

Great! This output shows that the compound index is being used, and there is no FETCH stage because the index covers the projection. This execution is faster than the one with no projection because there is no extra step to read the documents.

If a query projects additional fields that we don’t use to filter or sort by, we can include those fields at the end of the field list when we create the index. This way, we use the index to cover the query and avoid fetching the documents.

Excellent work! In this video, you learned about compound indexes. and how to define the order of fields in the index based on query equality, sort, and range predicates.

You also learned how to use the createIndex() command to create a compound index, and the explain() method to confirm that the compound index that we created was being used.

Finally, you learned how to cover a query with the index to avoid the fetch stage.