From 38b3e52c3c40b35339b1d18c09053be416a93420 Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Wed, 31 Oct 2018 23:30:34 +0530 Subject: MDEV-16695: Estimate for rows of derived tables is very high when we are using index_merge union For index merge union[or sort union], the estimates are not taken into account while calculating the selectivity of a condition. So instead of showing the estimates of the index merge union[or sort union], it shows estimates equal to all the records of the table. The fix for the issue is to include the selectivity of index merge union[or sort union] while calculating the selectivity of a condition. --- mysql-test/r/index_merge_myisam.result | 50 ++++++++++++++++++++++++++++++++++ mysql-test/t/index_merge_myisam.test | 35 ++++++++++++++++++++++++ sql/opt_range.cc | 6 ++++ 3 files changed, 91 insertions(+) diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index b3beff5a967..51b764213ca 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -1712,3 +1712,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 ALL NULL NULL NULL NULL 2 Using where +2 DERIVED t0 index_merge i1,i2 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 ALL NULL NULL NULL NULL 2 Using where +2 DERIVED t0 index_merge i1,i2 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; diff --git a/mysql-test/t/index_merge_myisam.test b/mysql-test/t/index_merge_myisam.test index d265007431e..75beb9bd883 100644 --- a/mysql-test/t/index_merge_myisam.test +++ b/mysql-test/t/index_merge_myisam.test @@ -243,3 +243,38 @@ DROP TABLE t1; set optimizer_switch= @optimizer_switch_save; +--echo # +--echo # MDEV-16695: Estimate for rows of derived tables is very high when we are using index_merge union +--echo # + +create table t0 +( + key1 int not null, + INDEX i1(key1) +); + +insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8); +let $1=7; +set @d=8; +while ($1) +{ + eval insert into t0 select key1+ @d from t0; + eval set @d=@d*2; + dec $1; +} +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; + +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; +select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +set optimizer_use_condition_selectivity=2; +explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +set @@optimizer_switch= @optimizer_switch_save; +drop table t0; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 0fd2cd267fc..3bcaa72e32f 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3725,6 +3725,12 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond) } + if (quick && (quick->get_type() == QUICK_SELECT_I::QS_TYPE_ROR_UNION || + quick->get_type() == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE)) + { + table->cond_selectivity*= (quick->records/table_records); + } + bitmap_union(used_fields, &handled_columns); /* Check if we can improve selectivity estimates by using sampling */ -- cgit v1.2.1