Mongodb performance of $in with single array element vs. $eq

Question

In our code, for convenience, we use queries like

db.collection.find({ "field": { $in: array } })

even if array contains only a single element. We could have rewritten it in this case to simply be

db.collection.find({ "field": "element" })

We thought that these queries would behave the same, however we noticed that with complex queries, that contain $or operators and multiple fields, while explain() shows the same query plan for both cases, actually running the queries returns quickly for the simple case, while using $in takes forever because maybe it's using different index scans.

Why wouldn't the mongodb query compiler turn $in with a single element into the same as $eq? And why would explain() still show that they're using the same index scans and fetches, while actually running the queries obviously uses different plans?

What specific version of MongoDB server are you using, and what queries are you comparing? In your current description you mention "complex $or operators and multiple fields", which seems a separate question from $in with a single array element versus the equivalent equality criteria. It would also be helpful if you can link to a gist/pastebin/... with the output of explain(true) for the two queries you are comparing. — Stennie
– Stennie, Commented Apr 3, 2017 at 11:35

Ofir Malka · Accepted Answer · 2018-02-28 07:20:41Z

21

It's the same

use

.explain()

to see the final query

db.collection.find({ "field": { $in: array } }).explain()
db.collection.find({ "field": "element" }).explain()

the $in translated to $eq if array contains only 1 element

answered Feb 28, 2018 at 7:20

Ofir Malka

3943 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mongodb performance of $in with single array element vs. $eq

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related