Details
Major
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The fix for the bug was pushed into the 5.5 tree on 2013-03-03. It will appear in 5.5.30.
This is a regression bug introduced by the patch for mdev-3851 that appeared in mariadb 5.5.28.a.