MongoDB $ in operator and compound index

Question

I have a collection with a composite index across four fields: (A, B, C, D)

When I make a request like

find({A: val1, B: val2, C: val3}).sort({D: 1}).limit(N)

with strict equalities in fields A, B, C it works very quickly, as it should be. And he explain()tells me that only N documents are checked.

If I change one of the peers to an operator $in(with about 100 elements in the array), it scans a lot more documents and works slower:

find({A: {$in: [val0, val1, ...]}, B: val2, C: val3}).sort({D: 1}).limit(N)

Other operators, such as $or, have the same effect.

Logically, one $inwith 100 elements should be very similar to 100 individual queries with strict equalities. The second option works much faster in the database, but requires the receipt of all elements (without restrictions), followed by sorting and restriction on the client side.

Does it make sense to split this single query $ininto several queries with equal values ​​to make the scan cursor less than the number of documents? What will be more effective in the case of millions of documents in the collection?

Answer 1

Have you tested with the index {B: 1, C: 1, A: 1, D: 1}? Thus, the exact values ​​of B and C can be processed quickly, the range can be used in field A, and sorting by D can be done through the index.