Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.29
-
None
-
None
Description
If one turns on extended keys, the optimizer starts use ref access where it used to use eq_ref access.
eq_ref is somewhat slower than 'equivalent' ref access, but that is only part of the problem. ORDER BY optimization checks if eq_ref is used, and if yes, enables certain strategies (see sql_select.cc, eq_ref_table()).
Testcase:
--source include/have_innodb.inc
|
create table t20 (a int not null) engine=innodb;
|
|
|
insert into t20 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
|
create table t21 (pk int primary key, a int not null, b int, unique(a))engine=innodb;
|
|
|
insert into t21
|
select
|
A.a + 10 * B.a, A.a + 10 * B.a, A.a + 10 * B.a
|
from t20 A, t20 B;
|
|
|
set optimizer_switch='extended_keys=on';
|
explain select * from t20, t21 where t21.a=t20.a;
|
set optimizer_switch='extended_keys=off';
|
explain select * from t20, t21 where t21.a=t20.a;
|
its output:
create table t20 (a int not null) engine=innodb;
|
insert into t20 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
create table t21 (pk int primary key, a int not null, b int, unique(a))engine=innodb;
|
insert into t21
|
select
|
A.a + 10 * B.a, A.a + 10 * B.a, A.a + 10 * B.a
|
from t20 A, t20 B;
|
set optimizer_switch='extended_keys=on';
|
explain select * from t20, t21 where t21.a=t20.a;
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 SIMPLE t20 ALL NULL NULL NULL NULL 10
|
1 SIMPLE t21 ref a a 4 test.t20.a 1
|
set optimizer_switch='extended_keys=off';
|
explain select * from t20, t21 where t21.a=t20.a;
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 SIMPLE t20 ALL NULL NULL NULL NULL 10
|
1 SIMPLE t21 eq_ref a a 4 test.t20.a 1
|