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