mysql-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 --- /dev/null
   9 +++ b/patch_info/optimizer_fix.info
  10 @@ -0,0 +1,8 @@
  11 +File=optimizer_fix.patch
  12 +Name=Unofficial optimizer fixes
  13 +Version=1.0
  14 +Author=Percona <info@percona.com>
  15 +License=GPL
  16 +Comment=
  17 +2010-01
  18 +Ported to 5.1.42
  19 --- a/sql/mysql_priv.h
  20 +++ b/sql/mysql_priv.h
  21 @@ -2109,6 +2109,7 @@
  22  extern ulong slave_exec_mode_options;
  23  extern my_bool opt_readonly, lower_case_file_system;
  24  extern my_bool opt_userstat_running, opt_thread_statistics;
  25 +extern my_bool opt_optimizer_fix;
  26  extern my_bool opt_enable_named_pipe, opt_sync_frm, opt_allow_suspicious_udfs;
  27  extern my_bool opt_secure_auth;
  28  extern char* opt_secure_file_priv;
  29 --- a/sql/mysqld.cc
  30 +++ b/sql/mysqld.cc
  31 @@ -542,6 +542,7 @@
  32  #endif /* defined(ENABLED_DEBUG_SYNC) */
  33  my_bool opt_old_style_user_limits= 0, trust_function_creators= 0;
  34  my_bool opt_userstat_running= 0, opt_thread_statistics= 0;
  35 +my_bool opt_optimizer_fix= 0;
  36  /*
  37    True if there is at least one per-hour limit for some user, so we should
  38    check them before each query (and possibly reset counters when hour is
  39 @@ -5865,6 +5866,7 @@
  40    OPT_SLOW_QUERY_LOG_FILE,
  41    OPT_USERSTAT_RUNNING,
  42    OPT_THREAD_STATISTICS,
  43 +  OPT_OPTIMIZER_FIX,
  44    OPT_USE_GLOBAL_LONG_QUERY_TIME,
  45    OPT_USE_GLOBAL_LOG_SLOW_CONTROL,
  46    OPT_SLOW_QUERY_LOG_MICROSECONDS_TIMESTAMP,
  47 @@ -7381,6 +7383,10 @@
  48     "Control TABLE_STATISTICS running, when userstat_running is enabled",
  49     (uchar**) &opt_thread_statistics, (uchar**) &opt_thread_statistics,
  50     0, GET_BOOL, NO_ARG, 0, 0, 1, 0, 1, 0},
  51 +  {"optimizer_fix", OPT_OPTIMIZER_FIX,
  52 +   "Enable unofficial optimizer fixes.",
  53 +   (uchar**) &opt_optimizer_fix, (uchar**) &opt_optimizer_fix,
  54 +   0, GET_BOOL, NO_ARG, 1, 0, 1, 0, 1, 0},
  55    {"binlog-direct-non-transactional-updates", OPT_BINLOG_DIRECT_NON_TRANS_UPDATE,
  56     "Causes updates to non-transactional engines using statement format to be "
  57     "written directly to binary log. Before using this option, make sure that "
  58 --- a/sql/opt_range.cc
  59 +++ b/sql/opt_range.cc
  60 @@ -695,7 +695,7 @@
  61  static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
  62                                         bool index_read_must_be_used,
  63                                         bool update_tbl_stats,
  64 -                                       double read_time);
  65 +                                       double read_time, ha_rows *estimated_records);
  66  static
  67  TRP_ROR_INTERSECT *get_best_ror_intersect(const PARAM *param, SEL_TREE *tree,
  68                                            double read_time,
  69 @@ -2200,6 +2200,7 @@
  70                                   ha_rows limit, bool force_quick_range)
  71  {
  72    uint idx;
  73 +  ha_rows estimated_records=0;
  74    double scan_time;
  75    DBUG_ENTER("SQL_SELECT::test_quick_select");
  76    DBUG_PRINT("enter",("keys_to_use: %lu  prev_tables: %lu  const_tables: %lu",
  77 @@ -2370,12 +2371,17 @@
  78
  79          /* Get best 'range' plan and prepare data for making other plans */
  80          if ((range_trp= get_key_scans_params(&param, tree, FALSE, TRUE,
  81 -                                             best_read_time)))
  82 +                                             best_read_time, &estimated_records)))
  83          {
  84            best_trp= range_trp;
  85            best_read_time= best_trp->read_cost;
  86          }
  87
  88 +        if (opt_optimizer_fix && estimated_records)
  89 +        {
  90 +          records = estimated_records;
  91 +        }
  92 +
  93          /*
  94            Simultaneous key scans and row deletes on several handler
  95            objects are not allowed so don't use ROR-intersection for
  96 @@ -3726,7 +3732,7 @@
  97    {
  98      DBUG_EXECUTE("info", print_sel_tree(param, *ptree, &(*ptree)->keys_map,
  99                                          "tree in SEL_IMERGE"););
 100 -    if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, FALSE, read_time)))
 101 +    if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, FALSE, read_time, NULL)))
 102      {
 103        /*
 104          One of index scans in this index_merge is more expensive than entire
 105 @@ -4829,11 +4835,12 @@
 106  static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
 107                                         bool index_read_must_be_used,
 108                                         bool update_tbl_stats,
 109 -                                       double read_time)
 110 +                                       double read_time, ha_rows *estimated_records)
 111  {
 112    int idx;
 113    SEL_ARG **key,**end, **key_to_read= NULL;
 114    ha_rows UNINIT_VAR(best_records);              /* protected by key_to_read */
 115 +  ha_rows min_records= HA_POS_ERROR;
 116    TRP_RANGE* read_plan= NULL;
 117    bool pk_is_clustered= param->table->file->primary_key_is_clustered();
 118    DBUG_ENTER("get_key_scans_params");
 119 @@ -4904,6 +4911,11 @@
 120          key_to_read=  key;
 121        }
 122
 123 +      if (estimated_records && found_records
 124 +          && min_records > found_records)
 125 +      {
 126 +        min_records = found_records;
 127 +      }
 128      }
 129    }
 130
 131 @@ -4926,6 +4938,12 @@
 132    else
 133      DBUG_PRINT("info", ("No 'range' table read plan found"));
 134
 135 +  /* minimum number of records (not 0) as estimated number of records */
 136 +  if (estimated_records && min_records != HA_POS_ERROR)
 137 +  {
 138 +    *estimated_records = min_records;
 139 +  }
 140 +
 141    DBUG_RETURN(read_plan);
 142  }
 143
 144 --- a/sql/set_var.cc
 145 +++ b/sql/set_var.cc
 146 @@ -573,6 +573,8 @@
 147                                                      &opt_userstat_running);
 148  static sys_var_bool_ptr                sys_thread_statistics(&vars, "thread_statistics",
 149                                                       &opt_thread_statistics);
 150 +static sys_var_bool_ptr                sys_optimizer_fix(&vars, "optimizer_fix",
 151 +                                                 &opt_optimizer_fix);
 152  static sys_var_thd_ulong       sys_read_rnd_buff_size(&vars, "read_rnd_buffer_size",
 153                                                &SV::read_rnd_buff_size);
 154  static sys_var_thd_ulong       sys_div_precincrement(&vars, "div_precision_increment",
 155 --- a/sql/sql_select.cc
 156 +++ b/sql/sql_select.cc
 157 @@ -2605,6 +2605,11 @@
 158        table->reginfo.impossible_range=1;
 159        DBUG_RETURN(0);
 160      }
 161 +    if (opt_optimizer_fix && error == 0)
 162 +    {
 163 +      /* quick select is not effective. but the estimated value is used. */
 164 +      DBUG_RETURN(select->records);
 165 +    }
 166      DBUG_PRINT("warning",("Couldn't use record count on const keypart"));
 167    }
 168    DBUG_RETURN(HA_POS_ERROR);                   /* This shouldn't happend */