Video Transcript (English)

Hi! In this video, we will define and create a single field index by using the createIndex() command. We’ll also discuss how to enforce uniqueness in the field values by using the unique constraint in an index.

Then, we’ll go over how to identify the indexes that exist for a collection by using getIndexes(). Finally, we will discuss how to determine if a query is using an index. If it is, we’ll use explain() to determine the index it’s using.

As the name implies, single field indexes are indexes on a single field. They support queries on a single field, and can support sorts on a single field.

We can create a single field index for a collection by using the createIndex command. We pass in the field name that we’ll include in the index, with the order 1 for ascending.

Ascending indexes can be used for both ascending or descending sorts. But it’s important to specify the order when using compound indexes to perform sorts on multiple fields.

Now we’ll demonstrate how to create a single field index.

We have a collection containing customer details that we query or sort based on the customer’s birthdate.

We search for customers with specific birthdate ranges and sort them by those dates.

For this collection, we should create an index with the birthdate field.

We’ll create an index for the customers collection by using the createIndex command.

We specify the birthdate field and the sort order as ascending.

Perfect! The index is created, and MongoDB returns the new index name, which is birthdate_1. Now this index can be used on birthdate queries for this collection.

This was just an example, but usually we query collections by other fields as well. For example, if we want to search for a specific customer email, we should create a new index on the email field.

In this case, the username is a unique identifier for the customer, and it should be a unique value in this collection. So, when we create the new index with the email field, we can specify that the values are unique by using the unique constraint and setting it equal to true.

This creates a new index called email_1 in the collection with the email field.

The unique constraint helps with the queries and prevents duplication of the email values.

If we attempt to insert a new document with an email that already exists in the collection, MongoDB returns a duplicate key error.

Now that we’ve created some indexes in the customers collection, let’s use the getIndexes() command to show the indexes that are being used.

The output shows three indexes on the collection:

_id, the default index

birthdate, the first index we created

And email, the index we just created with the unique constraint

We can also use the Atlas UI to review the indexes in a collection.

In your Atlas cluster, select Browse collections to find the customers collection, and then navigate to indexes.

Here, three indexes are listed.

To determine if any of these indexes are being used in a query, we use the explain() command on the collection, followed by the query.

Let’s use the query to find all the customers with a birthdate after August 1, 1995.

The explain() command returns the query plan. The winningPlan subsection lists the stages that are executed when the query is run.

In this case, there are o stages:

An IXSCAN, or index scan that uses the birthdate index, and

A FETCH stage, which reads only the documents that the index has identified.

If we return only the birthdate field, this FETCH stage won’t be necessary, and the index covers the query.

Now let’s use the explain() method with two more queries.

We’ll first use a query that searches for a set of customers based on their birthdate and returns them sorted by email.

The winningPlan performs an index scan by using the birthdate_1 index, fetches the documents that are identified by the index, and then sorts the results in memory.

We should create an index on both birthdate and email to support the query more efficiently. This is also known as a compound index, but we won't cover compound indexes in this video.

Note that the query optimizer considered using the email_1 index, which would support the sort but not the query filter. But it was rejected.

The final query example searches for customers with a specific account.

The winningPlan is a collection scan, because there are no indexes that can be used for the accounts field. We should create an index on the accounts field to support this query.

Congratulations on learning the essential indexing methods!

In this video, we created single field indexes with createIndex().

We also reviewed the collection indexes by using getIndexes() and the execution plan by using the explain() method.