]>
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! | |
db82db79 AM |
8 | --- /dev/null |
9 | +++ b/patch_info/optimizer_fix.info | |
b4e1fa2c AM |
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 | |
db82db79 AM |
19 | --- a/sql/mysqld.cc |
20 | +++ b/sql/mysqld.cc | |
29ffd636 | 21 | @@ -434,6 +434,7 @@ |
db82db79 | 22 | MYSQL_PLUGIN_IMPORT uint opt_debug_sync_timeout= 0; |
b4e1fa2c AM |
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 | |
db82db79 AM |
29 | --- a/sql/mysqld.h |
30 | +++ b/sql/mysqld.h | |
b4e1fa2c AM |
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; | |
db82db79 AM |
39 | --- a/sql/opt_range.cc |
40 | +++ b/sql/opt_range.cc | |
b4e1fa2c AM |
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(¶m, 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 | ||
db82db79 AM |
125 | --- a/sql/sql_select.cc |
126 | +++ b/sql/sql_select.cc | |
adf0fb13 | 127 | @@ -2619,6 +2619,11 @@ |
b4e1fa2c AM |
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 */ | |
db82db79 AM |
139 | --- a/sql/sys_vars.cc |
140 | +++ b/sql/sys_vars.cc | |
29ffd636 | 141 | @@ -2259,6 +2259,12 @@ |
b4e1fa2c AM |
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) |