I am introducing the concept of OR in my mongodb based product. I would like to know which operation is efficient in terms of perfromance
Depertment = product OR engineering OR finance
AND
Employeetype = Regular OR Parttime
Depertment = product OR engineering OR finance
OR
Employeetype = Regular OR Parttime
I believe using OR between the 2 attributes instead of AND would be redundant. I would like to know if there are any performance implications in introducing OR as the bridging operator. My personal opinion is that the second usage is redundant and costly for the backend and I would be better off creating a different conditon to satisfy the latter part of the second equation. (i.e. Employeetype = Regular OR Parttime).
I understand the difference in the 2 queries, my question is more on the performance on the server if I introduce OR in between the 2 attributes as shown in the second case. Isn’t the second query more compute-intensive? What are performance considerations when I introduce OR in between attributes, vs AND between attributes For eg department and employeetype?
When you $or fields you must have indexes on all the fields. You will then have multiple inputStages each doing and IXSCAN which is good. If you don’t you will end up with a COLSCAN which is bad.