]>
Commit | Line | Data |
---|---|---|
b4e1fa2c AM |
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 | |
11822e22 | 23 | @@ -429,6 +429,7 @@ |
b4e1fa2c AM |
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(¶m, 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 | |
11822e22 | 132 | @@ -2615,6 +2615,11 @@ |
b4e1fa2c AM |
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 | |
11822e22 | 147 | @@ -2144,6 +2144,12 @@ |
b4e1fa2c AM |
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) |