CODEWITHSUNDEEP
mongodb
Meni
Meni

Reputation: 488

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

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?

Upvotes: 17

Views: 4108

Answers (1)

Ofir Malka
Ofir Malka

Reputation: 384

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

Upvotes: 20

Related Questions