Habeeb_Raja
May 2024

Hi,
In my collection, I have 3 compound index as below
colA_colB
colB_colC
colC_colD
In my query, Im passing all 4 coulmns (colA, colB, colC, colD). index colA_colB have high cardinality and most of the time winningPlan uses that index and the results returned in less than a second.
In some cases, if the filter not matching with any of the combination in colA_colB index then the winningPlan chooses colB_colC index which has low cardinality. However, there is no records to be returned.
I just want to understand

  1. How to force to use only one index. Other than using hint() method
  2. Why the winningPlan chooses the wrong index eventhough no records to return.

Thanks,
Habeeb


Want to read more? Browse other topics in Working with Data or view latest topics.