I have a collection with compound index on four fields in order: (A,B,C,D)
When I do query like
find({A: val1, B: val2, C: val3}).sort({D: 1}).limit(N)
with strict equals in fields A,B,C it runs very fast, as it should be. And explain() tells me that just N documents was scanned.
If I change one of equals to $in operator (with about 100 elements in array) it scans much more number of documents and runs more slowly:
find({A: {$in: [val0, val1, ...]}, B: val2, C: val3}).sort({D: 1}).limit(N)
Other operators like $or have the same effect.
Logically one $in with 100 elements must be very similar to 100 individual queries with strict equals. Second variant runs much more faster in the database but requires getting all the elements (without limit) with post-sorting and limiting on the clientside.
Does it make sense to split this one query with $in into several queries with equals to make cursor scan less number of documents? What will be more efficient in case of millions of documents in the collection?