👐 Build a compound index following the ESR rule

In this exercise, you will build a compound index following the ESR rule, compare the query explain plans before and after creating the index, and analyze them.

Explore the code

🚀 NodeJS/Express

1. Open the file server/src/indexing/borrowed-books-index.ts file in your GitHub Codespace.

2. Examine the code to build a compound index on the issueDetails collection.

   /**
   * Create the index to support the following query:
   * issueDetails.find({
   *    'user._id': userID,
   *   borrowDate: { $gte: date },
   * }, {
   *  sort: { returnedDate: -1 }
   * })
   */
   await collections?.issueDetails?.createIndex({
       // Equality
       'user._id': 1,
       // Sort
       returnedDate: 1,
       // Range
       borrowDate: 1,
   });

   info

   The index is created on the issueDetails collection to support the query that finds the issue details for a user with a specific userID, where the borrowDate is greater than or equal to a specific date, and sorts the results by returnedDate in descending order.

   This is compound index and it follows the ESR rule: Equality, Sort, and Range. This ensures optimal performance for the query.

3. Execute the script to create the compound index.

   npx tsx src/indexing/borrowed-books-index.ts

   After a few seconds, you should see the following output:

   Connecting to MongoDB Atlas...
   Connected!
   
   BEFORE creating the index
   
   Winning plan stage: COLLSCAN
   No index used
   Total documents examined: 1284
   Number of documents returned: 3
   
   -----------------------------
   
   AFTER creating the index
   
   Winning plan stage: IXSCAN
   Index used: user._id_1_returnedDate_1_borrowDate_1
   Total documents examined: 3
   Number of documents returned: 3

   info

   The script uses explain plans to compare the query plans before and after creating the index.

   Notice the difference in the winning plan stage and the number of documents examined before and after creating the index.

☕️ Java Spring Boot

1. Let’s start with our IssueDetail record. Right now, it looks like this:

   src/main/java/com/mongodb/devrel/library/domain/model/IssueDetail.java

   @Document(collection = "issueDetails")
   public record IssueDetail(
       // fields ..
   ) {}

2. Now let’s optimize queries that need to filter or sort by multiple fields. For example, we often query by user._id, returnedDate, and borrowDate together. To speed this up, we can add a compound index using the @CompoundIndex annotation.

   src/main/java/com/mongodb/devrel/library/domain/model/IssueDetail.java

    @Document(collection = "issueDetails")
    @CompoundIndex(
            name = "user_returned_borrow_idx", 
            def = "{'user._id': 1, 'returnedDate': 1, 'borrowDate': 1}")
    public record IssueDetail(
        // fields
    ){}

   info

   The index is created on the issueDetails collection to support the query that finds the issue details for a user with a specific userID, where the borrowDate is greater than or equal to a specific date, and sorts the results by returnedDate in descending order.

   This is compound index and it follows the ESR rule: Equality, Sort, and Range. This ensures optimal performance for the query.

3. Open the application.yml and include the property auto-index-creation: true. At the end, your configuration will look like this:

   src/main/resources/application.yml

   spring:
     data:
       mongodb:
         uri: ${MONGODB_URI}
         database: library
         auto-index-creation: true

This ensures that any indexes defined in your domain models (for example, with @Indexed) will be created automatically by Spring Data MongoDB at startup.

1. Stop the running app.

   1. Locate the bottom panel and click on the TERMINAL tab.
   2. Press Ctrl+C/Cmd+c to interrupt the running app

2. Restart the app typing in the Terminal:

   mvn spring-boot:run

As soon as the application starts, you will see log entries showing the creation of indexes, similar to the image below.

Verify that the index is created

1. Open the VS Code extension (leaf icon) from the sidebar on the left.
2. Click Add Connection.
3. In the welcome screen, click Connect under the Connect with Connection String section.
4. Enter the MongoDB connection string from your Atlas cluster, making sure to replace the password placeholder.
5. Press Enter.
6. In the sidebar, you should now see your database. Expand the library database, then the issueDetails collection, and finally select Indexes.

🦸‍♀️ Try different indexes

Modify the compound index by adding and removing fields, and observe the changes in the query explain plans.