diff options
Diffstat (limited to 'mysql-test/r/innodb_ext_key.result')
| -rw-r--r-- | mysql-test/r/innodb_ext_key.result | 94 |
1 files changed, 94 insertions, 0 deletions
diff --git a/mysql-test/r/innodb_ext_key.result b/mysql-test/r/innodb_ext_key.result index 4e441245a39..0da4feaf26f 100644 --- a/mysql-test/r/innodb_ext_key.result +++ b/mysql-test/r/innodb_ext_key.result @@ -731,6 +731,7 @@ CREATE TABLE t2 (b int) ENGINE=MyISAM; INSERT INTO t1 (a) VALUES (4), (6); INSERT INTO t2 (b) VALUES (0), (8); set @save_optimizer_switch=@@optimizer_switch; +set @save_join_cache_level=@@join_cache_level; SET join_cache_level=3; SET optimizer_switch='join_cache_hashed=on'; SET optimizer_switch='join_cache_bka=on'; @@ -742,6 +743,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.a 2 Using where; Using join buffer (flat, BNLH join) SELECT * FROM t1, t2 WHERE b=a; a b +set join_cache_level=@save_join_cache_level; set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; # @@ -772,5 +774,97 @@ INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar') ON DUPLICATE KEY UPDATE c4 = VALUES(c4); set optimizer_switch=@save_optimizer_switch; DROP TABLE t1; +# +# Bug mdev-4220: using ref instead of eq_ref +# with extended_keys=on +# (performance regression introduced in the patch for mdev-3851) +# +set @save_optimizer_switch=@@optimizer_switch; +create table t1 (a int not null) engine=innodb; +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 ( +pk int primary key, a int not null, b int, unique(a) +)engine=innodb; +insert into t2 +select +A.a + 10 * B.a, A.a + 10 * B.a, A.a + 10 * B.a +from t1 A, t1 B; +set optimizer_switch='extended_keys=off'; +explain +select * from t1, t2 where t2.a=t1.a and t2.b < 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 +1 SIMPLE t2 eq_ref a a 4 test.t1.a 1 Using where +flush status; +select * from t1, t2 where t2.a=t1.a and t2.b < 2; +a pk a b +0 0 0 0 +1 1 1 1 +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 10 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 11 +set optimizer_switch='extended_keys=on'; +explain +select * from t1, t2 where t2.a=t1.a and t2.b < 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 +1 SIMPLE t2 eq_ref a a 4 test.t1.a 1 Using where +flush status; +select * from t1, t2 where t2.a=t1.a and t2.b < 2; +a pk a b +0 0 0 0 +1 1 1 1 +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 10 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 11 +drop table t1,t2; +create table t1(a int) engine=myisam; +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2(a int) engine=myisam; +insert into t2 select A.a + 10*B.a + 100*C.a from t1 A, t1 B, t1 C; +create table t3 ( +pk1 int not null, pk2 int not null, col1 int not null, col2 int not null) +engine=innodb; +insert into t3 select a,a,a,a from t2; +alter table t3 add primary key (pk1, pk2); +alter table t3 add key (col1, col2); +set optimizer_switch='extended_keys=off'; +explain +select * from t1, t3 where t3.col1=t1.a and t3.col2=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where +1 SIMPLE t3 ref col1 col1 8 test.t1.a,test.t1.a # Using index +explain +select * from t1, t3 where t3.col1=t1.a and t3.col2=t1.a and t3.pk1=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where +1 SIMPLE t3 ref PRIMARY,col1 PRIMARY 4 test.t1.a # Using where +set optimizer_switch='extended_keys=on'; +explain +select * from t1, t3 where t3.col1=t1.a and t3.col2=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where +1 SIMPLE t3 ref col1 col1 8 test.t1.a,test.t1.a # Using index +explain +select * from t1, t3 where t3.col1=t1.a and t3.col2=t1.a and t3.pk1=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where +1 SIMPLE t3 ref PRIMARY,col1 col1 12 test.t1.a,test.t1.a,test.t1.a # Using index +drop table t1,t2,t3; +set optimizer_switch=@save_optimizer_switch; set optimizer_switch=@save_ext_key_optimizer_switch; SET SESSION STORAGE_ENGINE=DEFAULT; |