AND vs OR operator perfromance considerations

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).

Any thoughts on this are appreciated.

Your queries

and

are not logically equivalent. So using

is not redundant at all.

For example, your OR-query will match documents like:

{Depertment:sales, Employeetype:Regular}
{Depertment:products, Employeetype:SickLeave}

The documents above will not be match by your AND-query.

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?

I

Yes. But for

there is 2 things that are important.

  1. Working set fits in RAM
  2. Indexes

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.

@Rohit_Nayak, any followup on this? If not, mark one of the post as the solution.