RFC: Support for Vindex Hints in Vitess

[systay]

Objective

To introduce syntax for specifying vindex hints directly in SQL queries in Vitess, enabling explicit control over shard routing decisions for optimized query performance.

Background

Vitess routes queries across shards based on vindexes without explicit hints from users. This proposal aims to allow users to provide specific vindexes for routing, similar to MySQL's index hints but tailored for Vitess's shard routing.

Proposed Syntax

The syntax for vindex hints is inspired by MySQL's index hint syntax, simplified for Vitess's shard routing focus:

tbl_name [USE VINDEX (vindex_list)] [IGNORE VINDEX (vindex_list)]

Where:

• tbl_name is the name of the table.
• vindex_list is a comma-separated list of vindex names that the query planner should consider or ignore for routing the query.

Examples

SELECT * FROM user_tbl USE VINDEX (hash_user_id);
SELECT * FROM order_tbl IGNORE VINDEX (range_order_id);

The first hint advises Vitess to use the hash_user_id vindex for routing the SELECT query, while the second advises ignoring the range_order_id vindex for routing queries against order_tbl.

Semantics

• Vindex hints are advisory. The query planner will attempt to use or ignore the suggested vindexes for shard routing, depending on the hint type.
• The hint only affects shard routing decisions and does not influence index choice within the shard's underlying MySQL instance.
• If multiple vindexes are specified in a USE VINDEX, Vitess will use the first applicable vindex based on the query's where clause and available vindexes.
• Specifying multiple USE VINDEX or IGNORE VINDEX for the same table in a single query is not valid. If encountered, Vitess should report an error. This includes having USE VINDEX or IGNORE VINDEX twice on the same table.
• Vindex hints do not support FOR {JOIN|ORDER BY|GROUP BY} clause as vindexes are solely used for shard routing.
• Specifying an unknown vindex should result in Vitess returning an error.

Implementation Considerations

• Extend the parser to recognize USE VINDEX and IGNORE VINDEX hint syntax.
• Update the planner to consider vindex hints when making routing decisions.
• Implement error handling for cases where specified vindexes do not exist, cannot be applied to a given query, or both USE and IGNORE hints are specified for the same table.

[systay]

Update with a bit about conflicts with USE & IGNORE VINDEX

[harshit-gangal]

I am unsure why we cannot add support FOR {JOIN|ORDER BY|GROUP BY}. For the Join still, there are 2 routes and the table indicates the hint and uses the hint for routing the query based on the valid vindex hint. Not sure what changes for an ORDER BY/GROUP BY

[harshit-gangal]

how about issuing a warning over failing the query for vindexes that do not exists? We can fail when the user uses both USE and IGNORE on the same table (possibly a parsing error)

[systay]

I am unsure why we cannot add support FOR {JOIN|ORDER BY|GROUP BY}. For the Join still, there are 2 routes and the table indicates the hint and uses the hint for routing the query based on the valid vindex hint. Not sure what changes for an ORDER BY/GROUP BY

Maybe we can add that, but I don't see a need to add all of these at the same time. Starting with [USE|IGNORE] VINDEX this would be valuable and not close the door for other hints in the future. Another future addition we could do is FORCE VINDEX, but again, my suggestion is that we start with just the two specified in this RFC to start with.

[systay]

how about issuing a warning over failing the query for vindexes that do not exists? We can fail when the user uses both USE and IGNORE on the same table (possibly a parsing error)

WWMD (What Would MySQL Do?) here?

mysql> select * from emp use index (does_not_exist) where empno = 12;
ERROR 1176 (42000): Key 'does_not_exist' doesn't exist in table 'emp'

I don't have very strong opinions, but it seems more helpful to fail here.