diff options
author | Igor Babaev <igor@askmonty.org> | 2018-11-10 14:52:57 -0800 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2018-11-10 14:52:57 -0800 |
commit | 5f46670bd09babbee75a24ac82eb4ade0706da66 (patch) | |
tree | 85e2759b75650b8165c3e01638e9458eb4d1274c /mysql-test/main/index_merge_myisam.result | |
parent | 8d5a11122c32f4d9eb87536886c6e893377bdd07 (diff) | |
parent | 3ea7de9a78a1410a9b79362774247e9e44b201b3 (diff) | |
download | mariadb-git-5f46670bd09babbee75a24ac82eb4ade0706da66.tar.gz |
Merge branch '10.4' into 10.4-mdev16188
Diffstat (limited to 'mysql-test/main/index_merge_myisam.result')
-rw-r--r-- | mysql-test/main/index_merge_myisam.result | 50 |
1 files changed, 50 insertions, 0 deletions
diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index 992e1d831b5..725a888c914 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -1715,3 +1715,53 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY,c1,i,c2 NULL NULL NULL 69 Using where DROP TABLE t1; set optimizer_switch= @optimizer_switch_save; +# +# MDEV-16695: Estimate for rows of derived tables is very high when we are using index_merge union +# +create table t0 +( +key1 int not null, +INDEX i1(key1) +); +insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8); +set @d=8; +insert into t0 select key1+ @d from t0; +set @d=@d*2; +insert into t0 select key1+ @d from t0; +set @d=@d*2; +insert into t0 select key1+ @d from t0; +set @d=@d*2; +insert into t0 select key1+ @d from t0; +set @d=@d*2; +insert into t0 select key1+ @d from t0; +set @d=@d*2; +insert into t0 select key1+ @d from t0; +set @d=@d*2; +insert into t0 select key1+ @d from t0; +set @d=@d*2; +alter table t0 add key2 int not null, add index i2(key2); +alter table t0 add key3 int not null, add index i3(key3); +alter table t0 add key8 int not null, add index i8(key8); +update t0 set key2=key1,key3=key1,key8=1024-key1; +analyze table t0; +Table Op Msg_type Msg_text +test.t0 analyze status OK +set @optimizer_switch_save=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; +explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +2 DERIVED t0 index_merge i1,i2,i8 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where +select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +key1 key2 key3 key8 +3 3 3 1021 +set optimizer_use_condition_selectivity=2; +explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +2 DERIVED t0 index_merge i1,i2,i8 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where +select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +key1 key2 key3 key8 +3 3 3 1021 +set @@optimizer_switch= @optimizer_switch_save; +drop table t0; |