CODEWITHSUNDEEP
mongodb
newUserNameHere
newUserNameHere

Reputation: 18001

Mongo $in with compound index

How to efficiently do an $in lookup on a collection with a compound index?

Index is on fields a and b per example below. EG: db.foo.createIndex({a: 1, b: 1})

Example in SQL:

SELECT * 
FROM foo 
WHERE (a,b) 
  IN (
    ("aVal1", "bVal1"),
    ("aVal2", "bVal2")
  );

I know you can do something like:

db.foo.find( {
    $or: [
        { a: "aVal1", b: "bVal1" },
        { a: "aVal2", b: "bVal2" },
    ]
} )

Is there a more performant way to do this using the $in operator?

Upvotes: 5

Views: 810

Answers (1)

Mạnh Quyết Nguyễn
Mạnh Quyết Nguyễn

Reputation: 18235

Since you already create a compound index for (a, b), all of your clauses expression are supported by indexes -> mongo will use index scan instead of collection scan. It probably fast enough.

Reference: $or Clauses and Indexes

When evaluating the clauses in the $or expression, MongoDB either performs a collection scan or, if all the clauses are supported by indexes, MongoDB performs index scans. That is, for MongoDB to use indexes to evaluate an $or expression, all the clauses in the $or expression must be supported by indexes. Otherwise, MongoDB will perform a collection scan.

Now about your question

Is there a more performant way to do this using the $in operator?

$in match entire field. If you want to match (a,b) then obviously (a,b) must become an embedded object to search with $in.

Not sure if making embedded object fits your current schema / requirement. But if it is the case, $in has known for better performance comparing to $or:

When using $or with that are equality checks for the value of the same field, use the $in operator instead of the $or operator.

In this case, if you have embedded object like: {e: {a: 'x', b: 'y'}} then db.collections.createIndex({e: 1}) paired with $in will speed things up

Upvotes: 3

Related Questions