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!
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
12 +File=optimizer_fix.patch
13 +Name=Unofficial optimizer fixes
15 +Author=Percona <info@percona.com>
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
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;
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
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
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,
50 + double read_time, ha_rows *estimated_records);
52 TRP_ROR_INTERSECT *get_best_ror_intersect(const PARAM *param, SEL_TREE *tree,
55 ha_rows limit, bool force_quick_range)
58 + ha_rows estimated_records=0;
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 @@
64 /* Get best 'range' plan and prepare data for making other plans */
65 if ((range_trp= get_key_scans_params(¶m, tree, FALSE, TRUE,
67 + best_read_time, &estimated_records)))
70 best_read_time= best_trp->read_cost;
73 + if (opt_optimizer_fix && estimated_records)
75 + records = estimated_records;
79 Simultaneous key scans and row deletes on several handler
80 objects are not allowed so don't use ROR-intersection for
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)))
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,
95 + double read_time, ha_rows *estimated_records)
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 @@
108 + if (estimated_records && found_records
109 + && min_records > found_records)
111 + min_records = found_records;
116 @@ -5020,6 +5032,12 @@
118 DBUG_PRINT("info", ("No 'range' table read plan found"));
120 + /* minimum number of records (not 0) as estimated number of records */
121 + if (estimated_records && min_records != HA_POS_ERROR)
123 + *estimated_records = min_records;
126 DBUG_RETURN(read_plan);
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 @@ -2611,6 +2611,11 @@
133 table->reginfo.impossible_range=1;
136 + if (opt_optimizer_fix && error == 0)
138 + /* quick select is not effective. but the estimated value is used. */
139 + DBUG_RETURN(select->records);
141 DBUG_PRINT("warning",("Couldn't use record count on const keypart"));
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 @@ -2134,6 +2134,12 @@
148 VALID_RANGE(1, IF_WIN(INT_MAX32/1000, LONG_TIMEOUT)),
149 DEFAULT(NET_WAIT_TIMEOUT), BLOCK_SIZE(1));
151 +static Sys_var_mybool Sys_optimizer_fix(
153 + "Enable unofficial optimizer fixes.",
154 + GLOBAL_VAR(opt_optimizer_fix),
155 + NO_CMD_LINE, DEFAULT(TRUE));
157 /** propagates changes to the relevant flag of @@optimizer_switch */
158 static bool fix_engine_condition_pushdown(sys_var *self, THD *thd,