1 # name : optimizer_fix.patch
2 # introduced : 11 or before
3 # maintainer : Yasufumi
6 # Any small change to this file in the main branch
7 # should be done or reviewed by the maintainer!
9 +++ b/patch_info/optimizer_fix.info
11 +File=optimizer_fix.patch
12 +Name=Unofficial optimizer fixes
14 +Author=Percona <info@percona.com>
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;
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
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
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,
46 + double read_time, ha_rows *estimated_records);
48 TRP_ROR_INTERSECT *get_best_ror_intersect(const PARAM *param, SEL_TREE *tree,
51 ha_rows limit, bool force_quick_range)
54 + ha_rows estimated_records=0;
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 @@
60 /* Get best 'range' plan and prepare data for making other plans */
61 if ((range_trp= get_key_scans_params(¶m, tree, FALSE, TRUE,
63 + best_read_time, &estimated_records)))
66 best_read_time= best_trp->read_cost;
69 + if (opt_optimizer_fix && estimated_records)
71 + records = estimated_records;
75 Simultaneous key scans and row deletes on several handler
76 objects are not allowed so don't use ROR-intersection for
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)))
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,
91 + double read_time, ha_rows *estimated_records)
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 @@
104 + if (estimated_records && found_records
105 + && min_records > found_records)
107 + min_records = found_records;
112 @@ -5020,6 +5032,12 @@
114 DBUG_PRINT("info", ("No 'range' table read plan found"));
116 + /* minimum number of records (not 0) as estimated number of records */
117 + if (estimated_records && min_records != HA_POS_ERROR)
119 + *estimated_records = min_records;
122 DBUG_RETURN(read_plan);
125 --- a/sql/sql_select.cc
126 +++ b/sql/sql_select.cc
127 @@ -2619,6 +2619,11 @@
128 table->reginfo.impossible_range=1;
131 + if (opt_optimizer_fix && error == 0)
133 + /* quick select is not effective. but the estimated value is used. */
134 + DBUG_RETURN(select->records);
136 DBUG_PRINT("warning",("Couldn't use record count on const keypart"));
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));
145 +static Sys_var_mybool Sys_optimizer_fix(
147 + "Enable unofficial optimizer fixes.",
148 + GLOBAL_VAR(opt_optimizer_fix),
149 + NO_CMD_LINE, DEFAULT(TRUE));
151 /** propagates changes to the relevant flag of @@optimizer_switch */
152 static bool fix_engine_condition_pushdown(sys_var *self, THD *thd,