revision-id: f7c182924bfa680db2cbc1eeafca6da5debea948 (mariadb-10.0.30-395-gf7c182924bf) parent(s): a2c0376e08d80d7b7dad8713d1df334b2b81eff9 author: Varun Gupta committer: Varun Gupta timestamp: 2018-07-09 12:59:21 +0530 message: 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 | 8 +++++- sql/opt_range.h | 2 +- sql/sql_select.cc | 2 +- 5 files changed, 94 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index b3beff5a967..6021011f27a 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 <derived2> 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 <derived2> 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..e345723a1fd 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 f1d84e5c623..63874dd26ea 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3491,7 +3491,7 @@ double records_in_column_ranges(PARAM *param, uint idx, TRUE otherwise */ -bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond) +bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond, QUICK_SELECT_I *quick) { uint keynr; uint max_quick_key_parts= 0; @@ -3704,6 +3704,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 */ diff --git a/sql/opt_range.h b/sql/opt_range.h index 54b15826d1b..bca446d5b9a 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -1051,7 +1051,7 @@ SQL_SELECT *make_select(TABLE *head, table_map const_tables, table_map read_tables, COND *conds, bool allow_null_cond, int *error); -bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond); +bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond, QUICK_SELECT_I *quick); #ifdef WITH_PARTITION_STORAGE_ENGINE bool prune_partitions(THD *thd, TABLE *table, Item *pprune_cond); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1ec6cca7c5b..7ef5cd6c77b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -3994,7 +3994,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, if (join->thd->variables.optimizer_use_condition_selectivity > 1) calculate_cond_selectivity_for_table(join->thd, s->table, *s->on_expr_ref ? - *s->on_expr_ref : conds); + *s->on_expr_ref : conds, s->quick); if (s->table->reginfo.impossible_range) { impossible_range= TRUE;