optimizer_fix.patch

   1 # name       : optimizer_fix.patch
   2 # introduced : 11 or before
   3 # maintainer : Yasufumi
   4 #
   5 #!!! notice !!!
   6 # Any small change to this file in the main branch
   7 # should be done or reviewed by the maintainer!
   8 diff -ruN /dev/null b/patch_info/optimizer_fix.info
   9 --- /dev/null   1970-01-01 09:00:00.000000000 +0900
  10 +++ b/patch_info/optimizer_fix.info     2010-12-02 20:47:55.781968475 +0900
  11 @@ -0,0 +1,8 @@
  12 +File=optimizer_fix.patch
  13 +Name=Unofficial optimizer fixes
  14 +Version=1.0
  15 +Author=Percona <info@percona.com>
  16 +License=GPL
  17 +Comment=
  18 +2010-01
  19 +Ported to 5.1.42
  20 diff -ruN a/sql/mysqld.cc b/sql/mysqld.cc
  21 --- a/sql/mysqld.cc     2010-12-02 19:22:40.027024953 +0900
  22 +++ b/sql/mysqld.cc     2010-12-02 20:51:50.811356434 +0900
  23 @@ -429,6 +429,7 @@
  24  uint    opt_debug_sync_timeout= 0;
  25  #endif /* defined(ENABLED_DEBUG_SYNC) */
  26  my_bool opt_old_style_user_limits= 0, trust_function_creators= 0;
  27 +my_bool opt_optimizer_fix= 0;
  28  /*
  29    True if there is at least one per-hour limit for some user, so we should
  30    check them before each query (and possibly reset counters when hour is
  31 diff -ruN a/sql/mysqld.h b/sql/mysqld.h
  32 --- a/sql/mysqld.h      2010-11-03 07:01:14.000000000 +0900
  33 +++ b/sql/mysqld.h      2010-12-02 20:51:10.392070356 +0900
  34 @@ -109,6 +109,7 @@
  35  extern ulonglong slave_type_conversions_options;
  36  extern my_bool read_only, opt_readonly;
  37  extern my_bool lower_case_file_system;
  38 +extern my_bool opt_optimizer_fix;
  39  extern my_bool opt_enable_named_pipe, opt_sync_frm, opt_allow_suspicious_udfs;
  40  extern my_bool opt_secure_auth;
  41  extern char* opt_secure_file_priv;
  42 diff -ruN a/sql/opt_range.cc b/sql/opt_range.cc
  43 --- a/sql/opt_range.cc  2010-11-03 07:01:14.000000000 +0900
  44 +++ b/sql/opt_range.cc  2010-12-02 20:47:55.795969853 +0900
  45 @@ -727,7 +727,7 @@
  46  static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
  47                                         bool index_read_must_be_used,
  48                                         bool update_tbl_stats,
  49 -                                       double read_time);
  50 +                                       double read_time, ha_rows *estimated_records);
  51  static
  52  TRP_ROR_INTERSECT *get_best_ror_intersect(const PARAM *param, SEL_TREE *tree,
  53                                            double read_time,
  54 @@ -2151,6 +2151,7 @@
  55                                   ha_rows limit, bool force_quick_range)
  56  {
  57    uint idx;
  58 +  ha_rows estimated_records=0;
  59    double scan_time;
  60    DBUG_ENTER("SQL_SELECT::test_quick_select");
  61    DBUG_PRINT("enter",("keys_to_use: %lu  prev_tables: %lu  const_tables: %lu",
  62 @@ -2319,12 +2320,17 @@
  63
  64          /* Get best 'range' plan and prepare data for making other plans */
  65          if ((range_trp= get_key_scans_params(&param, tree, FALSE, TRUE,
  66 -                                             best_read_time)))
  67 +                                             best_read_time, &estimated_records)))
  68          {
  69            best_trp= range_trp;
  70            best_read_time= best_trp->read_cost;
  71          }
  72
  73 +        if (opt_optimizer_fix && estimated_records)
  74 +        {
  75 +          records = estimated_records;
  76 +        }
  77 +
  78          /*
  79            Simultaneous key scans and row deletes on several handler
  80            objects are not allowed so don't use ROR-intersection for
  81 @@ -3820,7 +3826,7 @@
  82    {
  83      DBUG_EXECUTE("info", print_sel_tree(param, *ptree, &(*ptree)->keys_map,
  84                                          "tree in SEL_IMERGE"););
  85 -    if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, FALSE, read_time)))
  86 +    if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, FALSE, read_time, NULL)))
  87      {
  88        /*
  89          One of index scans in this index_merge is more expensive than entire
  90 @@ -4923,11 +4929,12 @@
  91  static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
  92                                         bool index_read_must_be_used,
  93                                         bool update_tbl_stats,
  94 -                                       double read_time)
  95 +                                       double read_time, ha_rows *estimated_records)
  96  {
  97    int idx;
  98    SEL_ARG **key,**end, **key_to_read= NULL;
  99    ha_rows UNINIT_VAR(best_records);              /* protected by key_to_read */
 100 +  ha_rows min_records= HA_POS_ERROR;
 101    TRP_RANGE* read_plan= NULL;
 102    bool pk_is_clustered= param->table->file->primary_key_is_clustered();
 103    DBUG_ENTER("get_key_scans_params");
 104 @@ -4998,6 +5005,11 @@
 105          key_to_read=  key;
 106        }
 107
 108 +      if (estimated_records && found_records
 109 +          && min_records > found_records)
 110 +      {
 111 +        min_records = found_records;
 112 +      }
 113      }
 114    }
 115
 116 @@ -5020,6 +5032,12 @@
 117    else
 118      DBUG_PRINT("info", ("No 'range' table read plan found"));
 119
 120 +  /* minimum number of records (not 0) as estimated number of records */
 121 +  if (estimated_records && min_records != HA_POS_ERROR)
 122 +  {
 123 +    *estimated_records = min_records;
 124 +  }
 125 +
 126    DBUG_RETURN(read_plan);
 127  }
 128
 129 diff -ruN a/sql/sql_select.cc b/sql/sql_select.cc
 130 --- a/sql/sql_select.cc 2010-11-03 07:01:14.000000000 +0900
 131 +++ b/sql/sql_select.cc 2010-12-02 20:47:55.813953789 +0900
 132 @@ -2615,6 +2615,11 @@
 133        table->reginfo.impossible_range=1;
 134        DBUG_RETURN(0);
 135      }
 136 +    if (opt_optimizer_fix && error == 0)
 137 +    {
 138 +      /* quick select is not effective. but the estimated value is used. */
 139 +      DBUG_RETURN(select->records);
 140 +    }
 141      DBUG_PRINT("warning",("Couldn't use record count on const keypart"));
 142    }
 143    DBUG_RETURN(HA_POS_ERROR);                   /* This shouldn't happend */
 144 diff -ruN a/sql/sys_vars.cc b/sql/sys_vars.cc
 145 --- a/sql/sys_vars.cc   2010-12-02 20:31:56.208023606 +0900
 146 +++ b/sql/sys_vars.cc   2010-12-02 21:17:44.618120277 +0900
 147 @@ -2144,6 +2144,12 @@
 148         VALID_RANGE(1, IF_WIN(INT_MAX32/1000, LONG_TIMEOUT)),
 149         DEFAULT(NET_WAIT_TIMEOUT), BLOCK_SIZE(1));
 150
 151 +static Sys_var_mybool Sys_optimizer_fix(
 152 +       "optimizer_fix",
 153 +       "Enable unofficial optimizer fixes.",
 154 +       GLOBAL_VAR(opt_optimizer_fix),
 155 +       NO_CMD_LINE, DEFAULT(TRUE));
 156 +
 157  /** propagates changes to the relevant flag of @@optimizer_switch */
 158  static bool fix_engine_condition_pushdown(sys_var *self, THD *thd,
 159                                            enum_var_type type)