Skip to content
  • Roy Lyseng's avatar
    87724fec
    Bug#12316645: Wrong cost calculation with optimizer_join_cache_level settings · 87724fec
    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.
    87724fec
    Bug#12316645: Wrong cost calculation with optimizer_join_cache_level settings
    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.
Loading