Description:
given a table like this:
create table t (
a int not null,
b int not null,
unique i1 (a),
unique i2 (a, b)
)
and a select like
select tx.x, t.a, t.b from t2 left join t on (t.a = t2.xa) where ...
mysql has 2 choises:
using an index read for table "t", but using only a "ref" on index "i2", because only the first part of the index is used, and this is not regocnized as unique
using an eq-ref for table "t" on index "i1", but having to access the table, instead of "using index", because i1 does not have the data for "b"
How to repeat:
-
Suggested fix:
mysql should realize that the left part of "i2" and "i1" are identical.
if mysql encounters a situation as the above, it should use the index "i2", (to allow b to be read from the index), but mysql should also realize that the column "a" in "i2" is unique (because of index "i1"), and therefore use an eq-ref instead of an ref.
Even better would be, if you could directly specify for a composite index which part to use for the uniquness check. This would be something like:
unique i2 (a) include(b),
(but that would be an extension to the sql standart, instead of just an optimization check. also the optimization is usefull in any case, as there could be cases where you want both indexes)