-
Type: New Feature
-
Resolution: Duplicate
-
Priority: Major - P3
-
None
-
Affects Version/s: None
-
Component/s: Querying
-
None
The ability to target multiple potential indexes for consideration or target a list of indexes to exclude from consideration in fulfilling a query request would be useful for sort operations using compound indexes. For performance reasons this is important in cases where some queries search for a single value and some queries search for multiple values using $in.
Use case:
An application creates three indexes on a collection as follows:
{"a":1, "b":-1} ("b"-1", "a":1} {"a":1, "c":1"}
The application requires both of the first two indexes because it queries for either:
{"a":{$in:[x]}, "c":{$in:[e,f,g]}}
or
{"a":{$in:[x,y,z]}, "c":{$in:[e,f,g]}}
But in both cases the application sorts on
{"b":-1}
Unfortunately Mongo frequently picks the index on
{"a":1, "c":1}
and then executes a comparatively expensive scanAndOrder.
A new feature that allows for the specification of a list of "anti-hints" and/or allows for the specification of a list of non-priority ordered index "possibilities" would remove the need for client logic to interpret the best index to use in these types of cases. Specifically, having to inspect the query and sort parameters in order to choose between the first two indexes listed above.
Assumption: Each unique inclusion/exclusion permutation would be considered a distinct query plan from a query that does not specify these values.
- duplicates
-
SERVER-9306 Ability to temporarily forbid query optimizer from using index ("invisible index")
- Closed