diff options
| -rw-r--r-- | mysql-test/r/subselect_innodb.result | 24 | ||||
| -rw-r--r-- | mysql-test/t/subselect_innodb.test | 23 | ||||
| -rw-r--r-- | sql/sql_select.cc | 40 |
3 files changed, 73 insertions, 14 deletions
diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index 159b1d4be81..07d00e96549 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -526,4 +526,26 @@ t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 2 DEPENDENT SUBQUERY t2 ref key1 key1 5 test.t1.a 1 Using where -drop table t1, t2; +# +# MDEV-6081: ORDER BY+ref(const): selectivity is very incorrect (MySQL Bug#14338686) +# +alter table t2 add key2 int; +update t2 set key2=key1; +alter table t2 add key(key2); +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +flush tables; +# Table tsubq must use 'ref' + Using filesort (not 'index' w/o filesort) +explain select +(SELECT +concat(id, '-', key1, '-', col1) +FROM t2 +WHERE t2.key1 = t1.a +ORDER BY t2.key2 ASC LIMIT 1) +from +t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # +2 DEPENDENT SUBQUERY t2 ref key1 key1 5 test.t1.a # Using where; Using filesort +drop table t1,t2; diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index e6883d51332..af6ec90ba74 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -513,5 +513,26 @@ explain select ORDER BY t2.id ASC LIMIT 1) from t1; -drop table t1, t2; + +--echo # +--echo # MDEV-6081: ORDER BY+ref(const): selectivity is very incorrect (MySQL Bug#14338686) +--echo # + +alter table t2 add key2 int; +update t2 set key2=key1; +alter table t2 add key(key2); +analyze table t2; +flush tables; +--echo # Table tsubq must use 'ref' + Using filesort (not 'index' w/o filesort) +--replace_column 9 # +explain select + (SELECT + concat(id, '-', key1, '-', col1) + FROM t2 + WHERE t2.key1 = t1.a + ORDER BY t2.key2 ASC LIMIT 1) +from + t1; + +drop table t1,t2; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 7a47dd309c1..235de14c466 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -24527,7 +24527,7 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, double fanout= 1; ha_rows table_records= table->stat_records(); bool group= join && join->group && order == join->group_list; - ha_rows ref_key_quick_rows= HA_POS_ERROR; + ha_rows refkey_rows_estimate= table->quick_condition_rows; const bool has_limit= (select_limit_arg != HA_POS_ERROR); /* @@ -24553,10 +24553,6 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, else keys= usable_keys; - if (ref_key >= 0 && ref_key != MAX_KEY && - table->covering_keys.is_set(ref_key)) - ref_key_quick_rows= table->quick_rows[ref_key]; - if (join) { uint tablenr= tab - join->join_tab; @@ -24567,6 +24563,22 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, else read_time= table->file->scan_time(); + /* + Calculate the selectivity of the ref_key for REF_ACCESS. For + RANGE_ACCESS we use table->quick_condition_rows. + */ + if (ref_key >= 0 && tab->type == JT_REF) + { + if (table->quick_keys.is_set(ref_key)) + refkey_rows_estimate= table->quick_rows[ref_key]; + else + { + const KEY *ref_keyinfo= table->key_info + ref_key; + refkey_rows_estimate= ref_keyinfo->rec_per_key[tab->ref.key_parts - 1]; + } + set_if_bigger(refkey_rows_estimate, 1); + } + for (nr=0; nr < table->s->keys ; nr++) { int direction; @@ -24683,17 +24695,17 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, with ref_key. Thus, to select first N records we have to scan N/selectivity(ref_key) index entries. selectivity(ref_key) = #scanned_records/#table_records = - table->quick_condition_rows/table_records. + refkey_rows_estimate/table_records. In any case we can't select more than #table_records. - N/(table->quick_condition_rows/table_records) > table_records - <=> N > table->quick_condition_rows. - */ - if (select_limit > table->quick_condition_rows) + N/(refkey_rows_estimate/table_records) > table_records + <=> N > refkey_rows_estimate. + */ + if (select_limit > refkey_rows_estimate) select_limit= table_records; else select_limit= (ha_rows) (select_limit * (double) table_records / - table->quick_condition_rows); + refkey_rows_estimate); rec_per_key= keyinfo->actual_rec_per_key(keyinfo->user_defined_key_parts-1); set_if_bigger(rec_per_key, 1); /* @@ -24713,8 +24725,12 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, index_scan_time < read_time) { ha_rows quick_records= table_records; + ha_rows refkey_select_limit= (ref_key >= 0 && + table->covering_keys.is_set(ref_key)) ? + refkey_rows_estimate : + HA_POS_ERROR; if ((is_best_covering && !is_covering) || - (is_covering && ref_key_quick_rows < select_limit)) + (is_covering && refkey_select_limit < select_limit)) continue; if (table->quick_keys.is_set(nr)) quick_records= table->quick_rows[nr]; |