Bug #22636 eq_ref on part of composite index, if unique
Submitted: 24 Sep 2006 11:46 Modified: 25 Sep 2006 10:24
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:4 / 5 OS:Any
Assigned to: CPU Architecture:Any
Tags: composite index, eq-ref, Optimizer, qc, ref

[24 Sep 2006 11:46] Martin Friebe
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)
[25 Sep 2006 10:24] Valeriy Kravchuk
Thank you for a reasonable feature request.