-
Roy Lyseng authored
best_access_path() did not take properly into account the setting of optimizer_join_cache_level when calculating the access path that gives the smallest cost to access a given table. There are three changes applied in this bug fix: - best_access_path() properly accounts for join cache level 0, ie do not use join caching. - best_access_path() does account for join cache level greater than 2 when a table is outer-joined to the current join prefix. - best_access_path() does account for join cache level greater than 2 when a table is semi-joined to the current join prefix. Static properties for setting join buffering have been centralized to a new function set_join_buffer_properties(). Further on, setting of join cache level is changed. check_join_cache_usage() is renamed to setup_join_buffering(). Cost calculation for joins with join buffering is better than it used to be, but it is still not fair. Sometimes, cost is calculated using join buffering, but the decision has to be reverted later. Cost calculation for BKA join strategy is also not considered properly (but this patch does not change the cost calculations for BKA). mysql-test/t/join_cache_jcl0.test Added test cases for joins where join buffering is turned off. mysql-test/r/join_cache_jcl0.result Added test case results for joins where join buffering is turned off. mysql-test/r/count_distinct.result mysql-test/r/distinct.result mysql-test/r/group_by.result mysql-test/r/join_cache_jcl1.result mysql-test/r/join_cache_jcl2.result mysql-test/r/join_cache_jcl3.result mysql-test/r/join_cache_jcl4.result mysql-test/r/join_cache_jcl5.result mysql-test/r/join_cache_jcl6.result mysql-test/r/join_cache_jcl7.result mysql-test/r/join_cache_jcl8.result mysql-test/r/join_nested.result mysql-test/r/join_nested_jcl6.result mysql-test/r/join_outer.result mysql-test/r/join_outer_jcl6.result mysql-test/r/null_key_all.result mysql-test/r/null_key_icp.result mysql-test/r/null_key_none.result mysql-test/r/order_by_all.result mysql-test/r/order_by_icp_mrr.result mysql-test/r/order_by_none.result mysql-test/r/select_all.result mysql-test/r/select_all_jcl6.result mysql-test/r/select_icp_mrr.result mysql-test/r/select_icp_mrr_jcl6.result mysql-test/r/select_none.result mysql-test/r/select_none_jcl6.result mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_jcl6.result mysql-test/r/subquery_sj_all_jcl7.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_jcl6.result mysql-test/r/subquery_sj_firstmatch_jcl7.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_jcl6.result mysql-test/r/subquery_sj_mat_jcl7.result mysql-test/r/subselect_innodb.result Lots of changed plans for bug#12316645. Generally the new costing will benefit BNL join buffering, sometimes over ref access. count_distinct.result: Changed result because of non-deterministic query. sql/sql_select.cc Modified the argument disable_jbuf locally in best_access_path() according to join cache level setting, and whether the table to be added is outer-joined or semi-joined. Added function set_join_buffer_properties() that calculates whether join buffering can be used for the tables of a query. check_join_cache_usage() is renamed to setup_join_buffering(). Checks for join buffering have been refactored somewhat: - Attempted to group the tests better (general checks before outer join tests before semi-join tests). - Outer join test is simplified: The criterion is that if one table in an outer join operation cannot use join buffering, then all tables in that outer join nest and any inner outer join nest cannot use join buffering. This is handled in two tests: 1. If the first inner table of the current outer join nest does not use join buffering, this table cannot use it either. 2. If an immediate outer join nest does not use join buffering, this table cannot use join buffering. The initial join buffering strategy is now selected in greedy_search() and possibly modified in setup_join_buffering(). If join buffering is to be used, setup_join_buffering() will set up the join cache for the table. best_extension_by_limited_search(): Variable 's' made const throughout it's use. sql/sql_select.h Added more documentation to the field use_join_cache in JOIN_TAB. The value is set to the set of possible join buffering strategies for this table when starting the join optimization.
Roy Lyseng authoredbest_access_path() did not take properly into account the setting of optimizer_join_cache_level when calculating the access path that gives the smallest cost to access a given table. There are three changes applied in this bug fix: - best_access_path() properly accounts for join cache level 0, ie do not use join caching. - best_access_path() does account for join cache level greater than 2 when a table is outer-joined to the current join prefix. - best_access_path() does account for join cache level greater than 2 when a table is semi-joined to the current join prefix. Static properties for setting join buffering have been centralized to a new function set_join_buffer_properties(). Further on, setting of join cache level is changed. check_join_cache_usage() is renamed to setup_join_buffering(). Cost calculation for joins with join buffering is better than it used to be, but it is still not fair. Sometimes, cost is calculated using join buffering, but the decision has to be reverted later. Cost calculation for BKA join strategy is also not considered properly (but this patch does not change the cost calculations for BKA). mysql-test/t/join_cache_jcl0.test Added test cases for joins where join buffering is turned off. mysql-test/r/join_cache_jcl0.result Added test case results for joins where join buffering is turned off. mysql-test/r/count_distinct.result mysql-test/r/distinct.result mysql-test/r/group_by.result mysql-test/r/join_cache_jcl1.result mysql-test/r/join_cache_jcl2.result mysql-test/r/join_cache_jcl3.result mysql-test/r/join_cache_jcl4.result mysql-test/r/join_cache_jcl5.result mysql-test/r/join_cache_jcl6.result mysql-test/r/join_cache_jcl7.result mysql-test/r/join_cache_jcl8.result mysql-test/r/join_nested.result mysql-test/r/join_nested_jcl6.result mysql-test/r/join_outer.result mysql-test/r/join_outer_jcl6.result mysql-test/r/null_key_all.result mysql-test/r/null_key_icp.result mysql-test/r/null_key_none.result mysql-test/r/order_by_all.result mysql-test/r/order_by_icp_mrr.result mysql-test/r/order_by_none.result mysql-test/r/select_all.result mysql-test/r/select_all_jcl6.result mysql-test/r/select_icp_mrr.result mysql-test/r/select_icp_mrr_jcl6.result mysql-test/r/select_none.result mysql-test/r/select_none_jcl6.result mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_jcl6.result mysql-test/r/subquery_sj_all_jcl7.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_jcl6.result mysql-test/r/subquery_sj_firstmatch_jcl7.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_jcl6.result mysql-test/r/subquery_sj_mat_jcl7.result mysql-test/r/subselect_innodb.result Lots of changed plans for bug#12316645. Generally the new costing will benefit BNL join buffering, sometimes over ref access. count_distinct.result: Changed result because of non-deterministic query. sql/sql_select.cc Modified the argument disable_jbuf locally in best_access_path() according to join cache level setting, and whether the table to be added is outer-joined or semi-joined. Added function set_join_buffer_properties() that calculates whether join buffering can be used for the tables of a query. check_join_cache_usage() is renamed to setup_join_buffering(). Checks for join buffering have been refactored somewhat: - Attempted to group the tests better (general checks before outer join tests before semi-join tests). - Outer join test is simplified: The criterion is that if one table in an outer join operation cannot use join buffering, then all tables in that outer join nest and any inner outer join nest cannot use join buffering. This is handled in two tests: 1. If the first inner table of the current outer join nest does not use join buffering, this table cannot use it either. 2. If an immediate outer join nest does not use join buffering, this table cannot use join buffering. The initial join buffering strategy is now selected in greedy_search() and possibly modified in setup_join_buffering(). If join buffering is to be used, setup_join_buffering() will set up the join cache for the table. best_extension_by_limited_search(): Variable 's' made const throughout it's use. sql/sql_select.h Added more documentation to the field use_join_cache in JOIN_TAB. The value is set to the set of possible join buffering strategies for this table when starting the join optimization.
Loading