0

I have an Index created based on the frequent queries. Here is the order of Index 1- (A,B,C) . Index 2 (A,B,D) , Index 3 (A,B,E). There is already Index on (A,B) and (C), (D), (E) as well. Is there any way better way to do it ? The only performance i see is to delete Index (A,B) since it's covered under Index 1, 2, 3.

Appreciate your response

Musthafa
  • 13
  • 3
  • Yes, you should remove the (A, B) index as your other multi-column indexes already cover it. That's all you should do. Do you have any performance issues or you're trying to optimize the index structure as much as possible? – zhulien Jul 26 '21 at 15:01
  • The problem is the Mongodb is automatically picking the (A,B) instead of Index 1,2,3. I was in the impression it would use Index C and then A,B for a query of (A,B,C). I am trying to optimize the Index as much as possible and delete unnecessary – Musthafa Jul 26 '21 at 15:05
  • Well, this is not a problem at all. It uses the double column one because it exists and you query by exactly those 2 columns. It will use the 3-column one when you delete the (A, B). The query performance will be the same as they basically store the same tree (up to column `C`) under the hood. – zhulien Jul 26 '21 at 15:23
  • How does the 3 Index are built, does it uses 3 BTree index or Just 1 BTree for (A,B) and they diverse out for C, D, E ?. Can we delete the Index of Just (A) and (B) as well ? – Musthafa Jul 26 '21 at 15:31
  • This will help you: https://docs.mongodb.com/manual/core/index-compound/. You didn't mention you have single index on `A` and `B`. You can remove the single `A` index as long as you have a compound one that starts with `A`. You can't remove `B`, though, for the reason specified. – zhulien Jul 26 '21 at 15:46

0 Answers0