I want deep understaning regadring compund index oredering.
Let’s assume I have a collection with 1b records. each record has company, user id, and date, and user’s personal information.
Let’s assume there are 50k companies with equal distribution.
I want to find all the personal information of a specific user on a specific date of a specific company.
what should be the index?
1 - company, user, date
2 - user, date
In other words, if company should be part of the index, and if it is ok if it will be in the first place
from one side, I read about EDR stragetry, because the index is by b tree index, so in case all of the indexes are eq index, the order it doesn’t matter (I am not talking about sub match index, so the order is importnatn, let’s assume the query uses always in all of the index’s fields)
From the other side, I read about greater cardinality should be first. MongoDB: The Definitive Guide - Kristina Chodorow - Google Books
Pls help me to understand how should I configure my indexes.
Thanks in advance