14

Implementing an application that looks up a table for mail id presence from a list of around 10 email ids. Tried using $or and $in.

$in seems to give better performance but not significant. Is one more optimized than other?

Amareswar
  • 2,048
  • 1
  • 20
  • 36
  • 3
    Well for starters an $in only take one index, that is significant due to the reason why an $or takes more (one per clause). An $or is like getting the result for each clause and then merging duplicates and returning a result. So that is one major point to consider here, whereas $in is a range identifier. By laws of computer science I would say that if you can use $in it would be more optimised than $or – Sammaye Feb 06 '13 at 21:31

4 Answers4

32

MongoDB docs have the answer:

"When using $or with <expressions> that are equality checks for the value of the same field, choose the $in operator over the $or operator."

artch
  • 4,487
  • 2
  • 28
  • 35
Asya Kamsky
  • 41,784
  • 5
  • 109
  • 133
  • 1
    Good answer, But why does `docs` prefer `$in` for equality checks? – Gibbs Jul 16 '20 at 10:38
  • some day it may not make a difference, currently the query optimizer chooses a *slightly* different plan between the two and while when that's the only predicate it doesn't make a difference performance-wise, when there are other predicates involved, it *may* make a difference. Plus writing it as $in make it significantly easier to read - it's immediately obvious that the same field is being tested against multiple values. – Asya Kamsky Jul 17 '20 at 14:24
8

$or operator is logical operator where you can define your own login but $in operator is Comparison operator where you can compare you can not put your on logic.

Syntax of $in:

{ field: { $in: [<value1>, <value2>, ... <valueN> ] } }

Example:

db.account.find( { qty: { $in: [ 5, 15 ] } } )

Syntax of $or:

{ $or: [ { <expression1> }, { <expression2> }, ... , { <expressionN> } ] }

Example:

db.account.find( { $or: [ { quantity: { $lt: 20 } }, { price: 10 } ] } )

Note: Account is your collection name

deadfish
  • 11,996
  • 12
  • 87
  • 136
Aamod Tiwari
  • 1,310
  • 1
  • 9
  • 7
5

"While "$or"will always work, use "$in"whenever possible as the query optimizer handles it more efficiently."

Moreover "$in" has more readability.

Ref: MongoDB: The Definitive Guide

iltermutlu
  • 71
  • 1
  • 3
0

Well that will insure no indecis to be ensured if you use $in, however i prefer to format it to $or as it will ensure index (readability won't concern me at is being handled in application logic in which i prefer to consume the memory of app rather than mongodb server)

Abdelrahman Salem
  • 9
  • 2
  • 1
    $in absolutely takes advantage of indexes. You can test this easily by using .explain() https://docs.mongodb.com/manual/reference/method/cursor.explain/ – Quest Mar 08 '21 at 21:37