Today, we silently ignore index hints on UPDATE/DELETE:
CREATE TABLE x (
a INT PRIMARY KEY,
b INT,
c INT,
INDEX b_idx (b),
INDEX c_idx (c)
)
----
opt
DELETE FROM
x@primary
WHERE
a = 1 AND b = 2 AND c = 3
----
delete x
โโโ columns: <none>
โโโ fetch columns: a:4(int) b:5(int) c:6(int)
โโโ cardinality: [0 - 0]
โโโ side-effects, mutations
โโโ select
โโโ columns: a:4(int!null) b:5(int!null) c:6(int!null)
โโโ cardinality: [0 - 1]
โโโ key: ()
โโโ fd: ()-->(4-6)
โโโ index-join x
โ โโโ columns: a:4(int!null) b:5(int) c:6(int)
โ โโโ cardinality: [0 - 1]
โ โโโ key: ()
โ โโโ fd: ()-->(4-6)
โ โโโ scan x@b_idx
โ โโโ columns: a:4(int!null) b:5(int!null)
โ โโโ constraint: /5/4: [/2/1 - /2/1]
โ โโโ cardinality: [0 - 1]
โ โโโ key: ()
โ โโโ fd: ()-->(4,5)
โโโ filters
โโโ c = 3 [type=bool, outer=(6), constraints=(/6: [/3 - /3]; tight), fd=()-->(6)]
We should use the index requested by the hint or explicitly error out when one is provided (preferably the former)
We should extend index hints to work here.
Most helpful comment
We should extend index hints to work here.