BACKGROUND ========== Certain optimizer features are controlled by @@optimizer_switch, whereas some others are controlled by dedicated session variables: @@optimizer_use_mrr, @@engine_condition_pushdown. GOAL ==== The task aims at removing the dedicated variables and adding corresponding switches to @@optimizer_switch. RATIONALE ========= - We want to standardize on using @@optimizer_switch. It has more visibility than other optimizer controlling variables, for example it has its own manual section in http://dev.mysql.com/doc/refman/5.4/en/query-speed.html: http://dev.mysql.com/doc/refman/5.4/en/switchable-optimizations.html - @@engine_condition_pushdown controls two features which is not practical from a user's point of view, and testability point of view.
MORE DETAILED BACKGROUND ======================== In 6.0 and 6.0 only we introduced two Optimizer features: - Multi-range-read MRR (WL#2474); user can enable or disable it with "set optimizer_use_mrr=auto|force|disable" - Index Condition Pushdown ICP (http://dev.mysql.com/doc/refman/5.4/en/index-condition-pushdown-optimization.html) : user can enable or disable it with "set engine_condition_pushdown=on|off"; this variable exists since 5.0 but in 5.x it controls only "engine condition pushdown" (http://dev.mysql.com/doc/refman/5.4/en/condition-pushdown-optimization.html), which is a different feature from "index condition pushdown", so in 6.0 we have one variable controlling both condition pushdowns. For several other 6.0 features and 5.1 features, enabling or disabling is done with "set optimizer_switch=semijoin=off,index_merge=off" etc (on|off|default are the only 3 possible values). In a discussion with some members of the Optimizer team it has been found that it would be more uniform from the point of view of the user if the following changes would be implemented. PRE-REQUISITES ============== Those changes all require a tree which has WL#4738 because that WL has re-designed server veriables. TO BE DONE IN 6.0-codebase ========================== 1) remove @@optimizer_use_mrr (possible as introduced in 6.0 which isn't RC yet); MRR will be controlled by "set optimizer_switch=X=Y" where X=Y is defined like this: "mrr=off" replaces "set optimizer_use_mrr=disable" (described as "do not use MRR") "mrr=on,mrr_cost_based=on" replaces "set optimizer_use_mrr=auto" (described as "attempt to make a cost-based choice between using and not using MRR") "mrr=on,mrr_cost_based=off" replaces "set optimizer_use_mrr=force" (described as "use MRR when possible; this is the default") "mrr=off,mrr_cost_based=on" is equal to "mrr=off" (precedence of "mrr"). (see http://dev.mysql.com/doc/refman/5.4/en/server-system-variables.html#sysvar_optimizer_use_mrr). 2) "set engine_condition_pushdown" will not control Index Condition Pushdown but only Engine Condition Pushdown (possible as ICP was introduced in 6.0); Index Condition Pushdown will be controlled by a new "set optimizer_switch=index_condition_pushdown=on|off"; once done certain DBUG_EXECUTE_IF can be removed. 3) @@join_cache_level will be renamed to @@optimizer_join_cache_level (possible as variable was introduced only in 6.0); the interest is to have most optimizer variables starting with "optimizer", so that you can see most of them with a single "SHOW VARIABLES LIKE 'optimizer%'". TO BE DONE IN next-mr (Celosia?) ================================ Move @@engine_condition_pushdown into @@optimizer_switch; for administrative purposes (simplifying the work of Docs etc), this has been made into a subtask, WL#5197. WL#5116 will serve to track only changes to 6.0.
For 2) - make sure that @@optimizer_switch=engine_condition_pushdown=on|off and @@engine_condition_pushdown=on|off do not influence Index condition pushdown - add an index_condition_pushdown=on|off switch to @@optimizer_switch For 1): - add mrr* switches to @@optimizer_switch - remove optimizer_use_mrr For 3): rename the variable. And add tests for all this.