Details
-
Improvement
-
Resolution: Unresolved
-
Major
-
None
-
4.6.3, 5.0.0
-
None
Description
Recommend the following changes with use index for consistent behavior and high availability. Use index behaves differently under different scenarios.
- If you specify an index which does not correctly qualify for the query it ignores the index and uses alternate indexes
- If you specify an index which does not exist the query errors out
- If you specify multiple alternate indexes in the use index _replica1, _replica2 use index works as long as both indexes exist even though only one might be used in the plan. If one of the indexes doesn't exist or is not available the query fails
To ensure high availability we need to change the use index to behave as outlined below:
- Use index should always be considered truly as a suggestion/hint, if the index doesn't exist or doesn't qualify use an alternate index
- If one of the the indexes in the use index does not exist the query should not error out unless there is no other qualifying index in the system (so if one replica / suggested index fails or doesn't exist other should still be used for the query, today it does not work that way)