Open systay opened 7 months ago
Update with a bit about conflicts with USE & IGNORE VINDEX
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
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)
I am unsure why we cannot add support
FOR {JOIN|ORDER BY|GROUP BY}
. For theJoin
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 anORDER 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.
how about issuing a warning over failing the query for vindexes that do not exists? We can fail when the user uses both
USE
andIGNORE
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.
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:
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
The first hint advises Vitess to use the
hash_user_id
vindex for routing the SELECT query, while the second advises ignoring therange_order_id
vindex for routing queries againstorder_tbl
.Semantics
USE VINDEX
, Vitess will use the first applicable vindex based on the query's where clause and available vindexes.USE VINDEX
orIGNORE VINDEX
for the same table in a single query is not valid. If encountered, Vitess should report an error. This includes havingUSE VINDEX
orIGNORE VINDEX
twice on the same table.FOR {JOIN|ORDER BY|GROUP BY}
clause as vindexes are solely used for shard routing.Implementation Considerations
USE VINDEX
andIGNORE VINDEX
hint syntax.USE
andIGNORE
hints are specified for the same table.