(Filed on behalf of, originally discovered by Shay Asher.)
Revised description (without explain)
When we query a collection with a compound index:
and provide a limit on the result set, the number of index keys scanned in the compound index seems to vary widely depending on the range of values requested for the first key.
The inconsistency is in the way the limit count affects the first query but not the second where we are requesting 2 values for the first key instead of 1.
Collection contains large number of documents with fields a and b (in addition to _id). There is an index on
A query with $in expression on a and sort of b with limit should use the index on a and b. It does use the index (and optimization from
SERVER-5063) when explain() is appended, however without explain even with hint it scans and sorts the whole collection.
Here is output from the logs for query:with and without explain.
The only way to keep it from sorting the entire dataset without explain() is by using negative limit.