👐 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. Open the file java-server/java-server/src/main/java/com/mongodb/devrel/library/model/IssueDetail.java file in your GitHub Codespace.

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

   // add these imports
   import org.springframework.data.mongodb.core.index.Indexed;
   import org.springframework.data.mongodb.core.index.CompoundIndex;
   
   // ...
   // Add the CompoundIndex annotation
   @CompoundIndex(name = "user_returned_borrow_idx", def = "{'user._id': 1, 'returnedDate': 1, 'borrowDate': 1}")
   public class IssueDetail {
       // ...
   
       // Constructors, Getters, Setters
   }

   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. Add to application.properties this line to enable automatic creation of indexes

   spring.data.mongodb.auto-index-creation=true

4. Stop the running app.

   1. Locate the bottom panel and click on the TERMINAL tab.
   2. Press Ctrl+C to interrup the running app

5. Restart the app typing in the Terminal:

   mvn spring-boot:start

🦸‍♀️ Try different indexes

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