diff options
| -rw-r--r-- | mysql-test/r/selectivity.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/selectivity_innodb.result | 4 | ||||
| -rw-r--r-- | sql/sql_select.cc | 37 |
3 files changed, 38 insertions, 7 deletions
diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index 8371b0eb0b6..79ff506d603 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -77,9 +77,9 @@ order by s_suppkey; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY supplier ALL PRIMARY NULL NULL NULL 10 100.00 Using filesort 1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 10 100.00 Using where -3 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 268 0.20 Using where; Using temporary; Using filesort +3 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 268 100.00 Using where; Using temporary; Using filesort 2 SUBQUERY <derived4> ALL NULL NULL NULL NULL 268 100.00 -4 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 268 0.20 Using where; Using temporary; Using filesort +4 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 268 100.00 Using where; Using temporary; Using filesort Warnings: Note 1003 select `dbt3_s001`.`supplier`.`s_suppkey` AS `s_suppkey`,`dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`supplier`.`s_address` AS `s_address`,`dbt3_s001`.`supplier`.`s_phone` AS `s_phone`,`revenue0`.`total_revenue` AS `total_revenue` from `dbt3_s001`.`supplier` join `dbt3_s001`.`revenue0` where ((`revenue0`.`supplier_no` = `dbt3_s001`.`supplier`.`s_suppkey`) and (`revenue0`.`total_revenue` = (select max(`revenue0`.`total_revenue`) from `dbt3_s001`.`revenue0`))) order by `dbt3_s001`.`supplier`.`s_suppkey` select s_suppkey, s_name, s_address, s_phone, total_revenue diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index eba0ed2f32a..e32eea8827d 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -80,9 +80,9 @@ order by s_suppkey; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY supplier index PRIMARY PRIMARY 4 NULL 10 100.00 1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 10 100.00 Using where -3 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 228 0.14 Using where; Using temporary; Using filesort +3 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 228 100.00 Using where; Using temporary; Using filesort 2 SUBQUERY <derived4> ALL NULL NULL NULL NULL 228 100.00 -4 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 228 0.14 Using where; Using temporary; Using filesort +4 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 228 100.00 Using where; Using temporary; Using filesort Warnings: Note 1003 select `dbt3_s001`.`supplier`.`s_suppkey` AS `s_suppkey`,`dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`supplier`.`s_address` AS `s_address`,`dbt3_s001`.`supplier`.`s_phone` AS `s_phone`,`revenue0`.`total_revenue` AS `total_revenue` from `dbt3_s001`.`supplier` join `dbt3_s001`.`revenue0` where ((`revenue0`.`supplier_no` = `dbt3_s001`.`supplier`.`s_suppkey`) and (`revenue0`.`total_revenue` = (select max(`revenue0`.`total_revenue`) from `dbt3_s001`.`revenue0`))) order by `dbt3_s001`.`supplier`.`s_suppkey` select s_suppkey, s_name, s_address, s_phone, total_revenue diff --git a/sql/sql_select.cc b/sql/sql_select.cc index be8ff20eb48..cc4b139fb2f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -6905,6 +6905,18 @@ double JOIN::get_examined_rows() } +/** + @brief + Get the selectivity of equalities between columns when joining a table + + @param join The optimized join + @param idx The number of tables in the evaluated partual join + @param s The table to be joined for evaluation + @param rem_tables The bitmap of tables to be joined later + @param keyparts The number of key parts to used when joining s + @param ref_keyuse_steps Array of references to keyuses employed to join s +*/ + static double table_multi_eq_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, table_map rem_tables, uint keyparts, @@ -7015,6 +7027,19 @@ double table_multi_eq_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, return sel; } + +/** + @brief + Get the selectivity of conditions when joining a table + + @param join The optimized join + @param s The table to be joined for evaluation + @param rem_tables The bitmap of tables to be joined later + + @retval + selectivity of the conditions imposed on the rows of s +*/ + static double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, table_map rem_tables) @@ -7034,7 +7059,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, { if (pos->key == 0 && table_records > 0) { - sel*= table->quick_rows[s->quick->index]/table_records; + sel/= table->quick_rows[s->quick->index]/table_records; } } else if (pos->key != 0) @@ -7085,6 +7110,11 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, } while (keyuse->table == table && keyuse->key == key); } + /* + If the field f from the table is equal to a field from one the + earlier joined tables then the selectivity of the range conditions + over the field f must be discounted. + */ for (Field **f_ptr=table->field ; (field= *f_ptr) ; f_ptr++) { if (!bitmap_is_set(read_set, field->field_index) || @@ -12275,9 +12305,9 @@ static COND *build_equal_items_for_cond(THD *thd, COND *cond, @endcode Thus, applying equalities from the where condition we basically can get more freedom in performing join operations. - Althogh we don't use this property now, it probably makes sense to use + Although we don't use this property now, it probably makes sense to use it in the future. - @param thd Thread handler + @param thd Thread handler @param cond condition to build the multiple equalities for @param inherited path to all inherited multiple equality items @param join_list list of join tables to which the condition @@ -12286,6 +12316,7 @@ static COND *build_equal_items_for_cond(THD *thd, COND *cond, for on expressions @param[out] cond_equal_ref pointer to the structure to place built equalities in + @param link_equal_items equal fields are to be linked @return pointer to the transformed condition containing multiple equalities |