Hi Olav,
the patch is approved.
Thank you,
Roy
On 30.11.10 19.16, Olav Sandstaa wrote:
> #At file:///export/home/tmp/mysql2/mrr-enable-opt-back/ based on
> revid:olav.sandstaa@stripped
>
> 3293 Olav Sandstaa 2010-11-30
> WL#5692 "Enable Disk-Sweep Multi-Range Read optimizer feature".
>
> This patch contains the second part of this worklog. With this change
> the MySQL server will be built with support for the Multi-Range Read
> (MRR) feature. The default will be that cost-based MRR will be
> enabled.
>
> The following changes to MySQL are implemented:
>
> 1. Enable disk-sweep Multi-Range Read for MyISAM and InnoDB
>
> With this feature enabled the optimizer will consider to use the
> disk-sweep multi-range read strategy when reading a range of
> records using a secondary index. The decision whether to use
> MRR or not is (by default) cost-based. The disk-sweep MRR strategy
> is implemented for MyISAM and InnoDB.
>
> The main change by enabling MRR should be improved performance for
> range queries that needs to access records from the base table using a
> secondary index when the base table requires disk-access. The user
> should be able to see if MRR has been used by looking in the "Extra"
> column of the EXPLAIN output. If this field contains the string "Using
> MRR" then MRR will be used.
>
> 2. Enable the user to control the use of Multi-Range Read
>
> When MRR has been enabled the user will be able to control the use
> of MRR by using two optimizer_switch configuration variables:
>
> * mrr: if this is on then MRR is available for use by the optimizer
> The default value is "on".
>
> * mrr_cost_based: This determines whether the use of MRR should be
> based on cost estimates done by the optimizer (value: on)
> or if MRR should always be used (value: off). The default value
> is "on".
> @ mysql-test/r/index_merge_myisam.result
> Change in result file caused by changing the order for optimizer switch
> flags due to enabling Multi-range read (MRR).
> @ mysql-test/r/mysqld--help-notwin.result
> Change in result file caused by changing the order for optimizer switch
> flags due to enabling Multi-range read (MRR).
> @ mysql-test/r/mysqld--help-win.result
> Change in result file caused by changing the order for optimizer switch
> flags due to enabling Multi-range read (MRR).
> @ mysql-test/r/optimizer_switch.result
> Change in result file caused by changing the order for optimizer switch
> flags due to enabling Multi-range read (MRR).
> @ mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
> Change in result file caused by changing the order for optimizer switch
> flags due to enabling Multi-range read (MRR).
> @ sql/sql_priv.h
> Enable cost-based Multi-Range Read by default. The
> OPTIMIZER_SWITCH_MRR and OPTIMIZER_SWITCH_MRR_COST_BASED
> flags will be included in the OPTIMIZER_SWITCH_DEFAULT
> macro also for the case where OPTIMIZER_SWITCH_ALL is
> undefined.
> @ sql/sys_vars.cc
> Enable Multi-Range Read by changing optimizer_switch_names variable
> so that "mrr" and "mrr_cost_based" are included also when
> OPTIMIZER_SWITCH_ALL is not defined.
>
> modified:
> mysql-test/r/index_merge_myisam.result
> mysql-test/r/mysqld--help-notwin.result
> mysql-test/r/mysqld--help-win.result
> mysql-test/r/optimizer_switch.result
> mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
> sql/sql_priv.h
> sql/sys_vars.cc
> === modified file 'mysql-test/r/index_merge_myisam.result'
>
> === modified file 'mysql-test/r/index_merge_myisam.result'
> --- a/mysql-test/r/index_merge_myisam.result 2010-11-30 13:55:22 +0000
> +++ b/mysql-test/r/index_merge_myisam.result 2010-11-30 18:16:43 +0000
> @@ -1492,19 +1492,19 @@
> #
> select @@optimizer_switch;
> @@optimizer_switch
>
> -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
> set optimizer_switch='index_merge=off,index_merge_union=off';
> select @@optimizer_switch;
> @@optimizer_switch
>
> -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
> set optimizer_switch='index_merge_union=on';
> select @@optimizer_switch;
> @@optimizer_switch
>
> -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
> set optimizer_switch='default,index_merge_sort_union=off';
> select @@optimizer_switch;
> @@optimizer_switch
>
> -index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
> set optimizer_switch=4;
> set optimizer_switch=NULL;
> ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'NULL'
> @@ -1530,21 +1530,21 @@
> set optimizer_switch='index_merge=off,index_merge_union=off,default';
> select @@optimizer_switch;
> @@optimizer_switch
>
> -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
> set optimizer_switch=default;
> select @@global.optimizer_switch;
> @@global.optimizer_switch
>
> -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
> set @@global.optimizer_switch=default;
> select @@global.optimizer_switch;
> @@global.optimizer_switch
>
> -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
> #
> # Check index_merge's @@optimizer_switch flags
> #
> select @@optimizer_switch;
> @@optimizer_switch
>
> -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
> create table t0 (a int);
> insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
> create table t1 (a int, b int, c int, filler char(100),
> @@ -1654,5 +1654,5 @@
> set optimizer_switch=default;
> show variables like 'optimizer_switch';
> Variable_name Value
>
> -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
> drop table t0, t1;
>
> === modified file 'mysql-test/r/mysqld--help-notwin.result'
> --- a/mysql-test/r/mysqld--help-notwin.result 2010-11-30 13:55:22 +0000
> +++ b/mysql-test/r/mysqld--help-notwin.result 2010-11-30 18:16:43 +0000
> @@ -407,9 +407,9 @@
> optimizer_switch=option=val[,option=val...], where option
> is one of {index_merge, index_merge_union,
> index_merge_sort_union, index_merge_intersection,
> - engine_condition_pushdown, index_condition_pushdown,
> - materialization, semijoin, loosescan, firstmatch, mrr,
> - mrr_cost_based} and val is one of {on, off, default}
> + engine_condition_pushdown, index_condition_pushdown, mrr,
> + mrr_cost_based, materialization, semijoin, loosescan,
> + firstmatch} and val is one of {on, off, default}
> --performance-schema
> Enable the performance schema.
> --performance-schema-events-waits-history-long-size=#
> @@ -854,7 +854,7 @@
> optimizer-join-cache-level 4
> optimizer-prune-level 1
> optimizer-search-depth 62
> -optimizer-switch
> index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
> +optimizer-switch
> index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
> performance-schema FALSE
> performance-schema-events-waits-history-long-size 10000
> performance-schema-events-waits-history-size 10
>
> === modified file 'mysql-test/r/mysqld--help-win.result'
> --- a/mysql-test/r/mysqld--help-win.result 2010-11-30 13:55:22 +0000
> +++ b/mysql-test/r/mysqld--help-win.result 2010-11-30 18:16:43 +0000
> @@ -407,9 +407,9 @@
> optimizer_switch=option=val[,option=val...], where option
> is one of {index_merge, index_merge_union,
> index_merge_sort_union, index_merge_intersection,
> - engine_condition_pushdown, index_condition_pushdown,
> - materialization, semijoin, loosescan, firstmatch, mrr,
> - mrr_cost_based} and val is one of {on, off, default}
> + engine_condition_pushdown, index_condition_pushdown, mrr,
> + mrr_cost_based, materialization, semijoin, loosescan,
> + firstmatch} and val is one of {on, off, default}
> --performance-schema
> Enable the performance schema.
> --performance-schema-events-waits-history-long-size=#
> @@ -858,7 +858,7 @@
> optimizer-join-cache-level 4
> optimizer-prune-level 1
> optimizer-search-depth 62
> -optimizer-switch
> index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
> +optimizer-switch
> index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
> performance-schema FALSE
> performance-schema-events-waits-history-long-size 10000
> performance-schema-events-waits-history-size 10
>
> === modified file 'mysql-test/r/optimizer_switch.result'
> --- a/mysql-test/r/optimizer_switch.result 2010-11-30 13:55:22 +0000
> +++ b/mysql-test/r/optimizer_switch.result 2010-11-30 18:16:43 +0000
> @@ -3,47 +3,47 @@
>
> select @@optimizer_switch;
> @@optimizer_switch
>
> -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
> set optimizer_switch='default';
> set optimizer_switch='materialization=off';
> select @@optimizer_switch;
> @@optimizer_switch
>
> -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=off,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=off,semijoin=on,loosescan=on,firstmatch=on
> set optimizer_switch='default';
> set optimizer_switch='semijoin=off';
> select @@optimizer_switch;
> @@optimizer_switch
>
> -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=off,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=off,loosescan=on,firstmatch=on
> set optimizer_switch='default';
> set optimizer_switch='loosescan=off';
> select @@optimizer_switch;
> @@optimizer_switch
>
> -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=off,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=off,firstmatch=on
> set optimizer_switch='default';
> set optimizer_switch='semijoin=off,materialization=off';
> select @@optimizer_switch;
> @@optimizer_switch
>
> -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=off,semijoin=off,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=off,semijoin=off,loosescan=on,firstmatch=on
> set optimizer_switch='default';
> set optimizer_switch='materialization=off,semijoin=off';
> select @@optimizer_switch;
> @@optimizer_switch
>
> -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=off,semijoin=off,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=off,semijoin=off,loosescan=on,firstmatch=on
> set optimizer_switch='default';
> set optimizer_switch='semijoin=off,materialization=off,loosescan=off';
> select @@optimizer_switch;
> @@optimizer_switch
>
> -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=off,semijoin=off,loosescan=off,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=off,semijoin=off,loosescan=off,firstmatch=on
> set optimizer_switch='default';
> set optimizer_switch='semijoin=off,loosescan=off';
> select @@optimizer_switch;
> @@optimizer_switch
>
> -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=off,loosescan=off,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=off,loosescan=off,firstmatch=on
> set optimizer_switch='default';
> set optimizer_switch='materialization=off,loosescan=off';
> select @@optimizer_switch;
> @@optimizer_switch
>
> -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=off,semijoin=on,loosescan=off,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=off,semijoin=on,loosescan=off,firstmatch=on
> set optimizer_switch='default';
> create table t1 (a1 char(8), a2 char(8));
> create table t2 (b1 char(8), b2 char(8));
>
> === modified file 'mysql-test/suite/sys_vars/r/optimizer_switch_basic.result'
> --- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result 2010-11-30 13:55:22
> +0000
> +++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result 2010-11-30 18:16:43
> +0000
> @@ -1,57 +1,57 @@
> SET @start_global_value = @@global.optimizer_switch;
> SELECT @start_global_value;
> @start_global_value
>
> -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
> select @@global.optimizer_switch;
> @@global.optimizer_switch
>
> -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
> select @@session.optimizer_switch;
> @@session.optimizer_switch
>
> -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
> show global variables like 'optimizer_switch';
> Variable_name Value
>
> -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
> show session variables like 'optimizer_switch';
> Variable_name Value
>
> -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
> select * from information_schema.global_variables where
> variable_name='optimizer_switch';
> VARIABLE_NAME VARIABLE_VALUE
>
> -OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
> select * from information_schema.session_variables where
> variable_name='optimizer_switch';
> VARIABLE_NAME VARIABLE_VALUE
>
> -OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
> set global optimizer_switch=10;
> set session optimizer_switch=5;
> select @@global.optimizer_switch;
> @@global.optimizer_switch
>
> -index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off
>
> +index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off
> select @@session.optimizer_switch;
> @@session.optimizer_switch
>
> -index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off
>
> +index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off
> set global optimizer_switch="index_merge_sort_union=on";
> set session optimizer_switch="index_merge=off";
> select @@global.optimizer_switch;
> @@global.optimizer_switch
>
> -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off
>
> +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off
> select @@session.optimizer_switch;
> @@session.optimizer_switch
>
> -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off
>
> +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off
> show global variables like 'optimizer_switch';
> Variable_name Value
>
> -optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off
>
> +optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off
> show session variables like 'optimizer_switch';
> Variable_name Value
>
> -optimizer_switch index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off
>
> +optimizer_switch index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off
> select * from information_schema.global_variables where
> variable_name='optimizer_switch';
> VARIABLE_NAME VARIABLE_VALUE
>
> -OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off
>
> +OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off
> select * from information_schema.session_variables where
> variable_name='optimizer_switch';
> VARIABLE_NAME VARIABLE_VALUE
>
> -OPTIMIZER_SWITCH index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off
>
> +OPTIMIZER_SWITCH index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off
> set session optimizer_switch="default";
> select @@session.optimizer_switch;
> @@session.optimizer_switch
>
> -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off
>
> +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off
> set global optimizer_switch=1.1;
> ERROR 42000: Incorrect argument type to variable 'optimizer_switch'
> set global optimizer_switch=1e1;
> @@ -63,4 +63,4 @@
> SET @@global.optimizer_switch = @start_global_value;
> SELECT @@global.optimizer_switch;
> @@global.optimizer_switch
>
> -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=on
>
> +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on
>
> === modified file 'sql/sql_priv.h'
> --- a/sql/sql_priv.h 2010-11-30 13:55:22 +0000
> +++ b/sql/sql_priv.h 2010-11-30 18:16:43 +0000
> @@ -157,24 +157,24 @@
> #define OPTIMIZER_SWITCH_INDEX_MERGE_INTERSECT (1ULL<< 3)
> #define OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN (1ULL<< 4)
> #define OPTIMIZER_SWITCH_INDEX_CONDITION_PUSHDOWN (1ULL<< 5)
> -#define OPTIMIZER_SWITCH_MATERIALIZATION (1ULL<< 6)
> -#define OPTIMIZER_SWITCH_SEMIJOIN (1ULL<< 7)
> -#define OPTIMIZER_SWITCH_LOOSE_SCAN (1ULL<< 8)
> -#define OPTIMIZER_SWITCH_FIRSTMATCH (1ULL<< 9)
> /** If this is off, MRR is never used. */
> -#define OPTIMIZER_SWITCH_MRR (1ULL<< 10)
> +#define OPTIMIZER_SWITCH_MRR (1ULL<< 6)
> /**
> If OPTIMIZER_SWITCH_MRR is on and this is on, MRR is used depending on a
> cost-based choice ("automatic"). If OPTIMIZER_SWITCH_MRR is on and this is
> off, MRR is "forced" (i.e. used as long as the storage engine is capable of
> doing it).
> */
> -#define OPTIMIZER_SWITCH_MRR_COST_BASED (1ULL<< 11)
> +#define OPTIMIZER_SWITCH_MRR_COST_BASED (1ULL<< 7)
> +#define OPTIMIZER_SWITCH_MATERIALIZATION (1ULL<< 8)
> +#define OPTIMIZER_SWITCH_SEMIJOIN (1ULL<< 9)
> +#define OPTIMIZER_SWITCH_LOOSE_SCAN (1ULL<< 10)
> +#define OPTIMIZER_SWITCH_FIRSTMATCH (1ULL<< 11)
> #define OPTIMIZER_SWITCH_LAST (1ULL<< 12)
>
> /**
> If OPTIMIZER_SWITCH_ALL is defined, optimizer_switch flags for newer
> - optimizer features (semijoin, MRR) will be available.
> + optimizer features (semijoin) will be available.
> */
> #define OPTIMIZER_SWITCH_ALL 1
>
> @@ -189,19 +189,21 @@
> OPTIMIZER_SWITCH_INDEX_MERGE_INTERSECT | \
> OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN |\
> OPTIMIZER_SWITCH_INDEX_CONDITION_PUSHDOWN | \
> + OPTIMIZER_SWITCH_MRR | \
> + OPTIMIZER_SWITCH_MRR_COST_BASED | \
> OPTIMIZER_SWITCH_MATERIALIZATION | \
> OPTIMIZER_SWITCH_SEMIJOIN | \
> OPTIMIZER_SWITCH_LOOSE_SCAN | \
> - OPTIMIZER_SWITCH_FIRSTMATCH | \
> + OPTIMIZER_SWITCH_FIRSTMATCH)
> +#else
> +#define OPTIMIZER_SWITCH_DEFAULT (OPTIMIZER_SWITCH_INDEX_MERGE | \
> + OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \
> + OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION | \
> + OPTIMIZER_SWITCH_INDEX_MERGE_INTERSECT | \
> + OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN |\
> + OPTIMIZER_SWITCH_INDEX_CONDITION_PUSHDOWN | \
> OPTIMIZER_SWITCH_MRR | \
> OPTIMIZER_SWITCH_MRR_COST_BASED)
> -#else
> -#define OPTIMIZER_SWITCH_DEFAULT (OPTIMIZER_SWITCH_INDEX_MERGE | \
> - OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \
> - OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION | \
> - OPTIMIZER_SWITCH_INDEX_MERGE_INTERSECT | \
> - OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN |\
> - OPTIMIZER_SWITCH_INDEX_CONDITION_PUSHDOWN)
> #endif
> /*
> Replication uses 8 bytes to store SQL_MODE in the binary log. The day you
>
> === modified file 'sql/sys_vars.cc'
> --- a/sql/sys_vars.cc 2010-11-29 13:30:18 +0000
> +++ b/sql/sys_vars.cc 2010-11-30 18:16:43 +0000
> @@ -1412,10 +1412,9 @@
> {
> "index_merge", "index_merge_union", "index_merge_sort_union",
> "index_merge_intersection", "engine_condition_pushdown",
> - "index_condition_pushdown",
> + "index_condition_pushdown" , "mrr", "mrr_cost_based",
> #ifdef OPTIMIZER_SWITCH_ALL
> "materialization", "semijoin", "loosescan", "firstmatch",
> - "mrr", "mrr_cost_based",
> #endif
> "default", NullS
> };
> @@ -1433,10 +1432,10 @@
> "optimizer_switch=option=val[,option=val...], where option is one of "
> "{index_merge, index_merge_union, index_merge_sort_union, "
> "index_merge_intersection, engine_condition_pushdown, "
> - "index_condition_pushdown"
> + "index_condition_pushdown, mrr, mrr_cost_based"
> #ifdef OPTIMIZER_SWITCH_ALL
> ", materialization, "
> - "semijoin, loosescan, firstmatch, mrr, mrr_cost_based"
> + "semijoin, loosescan, firstmatch"
> #endif
> "} and val is one of {on, off, default}",
> SESSION_VAR(optimizer_switch), CMD_LINE(REQUIRED_ARG),
>
>
>
>
