MongoDB $in operator and compound index

Question

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?

Answer 1

Have you tested with the index {B:1,C:1,A:1,D:1} ? That way the exact B and C values can be processed quickly, a range can be used on the A field and sorting by D can still be done via the index.