Understanding Compound Index in MongoDB

Working with Data
,
3

Hey @Abhishek_Chaudhary1,

MongoDB chooses query plans based on the query shape and available indexes to find the optimal balance between performance and resource utilization.

In this case when inserting a document {a:1, b:1, c:1}, and creating two indexes - a_1_b_1 and a_1_b_1_c_1.

The MongoDB will choose the smaller a_1_b_1 index as the winning plan for this query since the collection has a single document. The database can retrieve the matching document using just a and b, and get the c:1 value for free without needing to use the larger index.

However, when inserting a second document {a:1, b:1, c:1} with the same value for c, MongoDB will still use the a_1_b_1 index. This index contains only a and b, but that’s sufficient to locate the two matching documents as the value of c is also the same across the collection.

Finally, upon inserting a third document {a:1, b:1, c:2} with a different value for c, MongoDB will now choose a_1_b_1_c_1 as the winning plan. This index contains all three fields a, b, and c, which allows MongoDB to efficiently locate the two documents that match the query’s criteria on all three fields.

   winningPlan: {
      stage: 'FETCH',
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: {
          a: 1,
          b: 1,
          c: 1
        },
        indexName: 'a_1_b_1_c_1',
    ...
    rejectedPlans: [
      {
        stage: 'FETCH',
        filter: {
          c: {
            '$eq': 1
          }
        },
        inputStage: {
          stage: 'IXSCAN',
          keyPattern: {
            a: 1,
            b: 1
          },
          indexName: 'a_1_b_1',

In summary, MongoDB adapts its query plan based on several factors one being data distribution in this specific case. As more documents match on a specific field like c, MongoDB will start utilizing indexes that contain that field, switching from smaller indexes to larger ones as needed to improve query performance.

I hope it clarifies!

Thanks,
Kushagra

5 Likes