List:Commits« Previous MessageNext Message »
From:Øystein Grøvlen Date:November 11 2010 12:35pm
Subject:Re: bzr commit into mysql-next-mr-bugfixing branch (olav.sandstaa:3280)
WL#5639
View as plain text  
Hi, Olav

Thanks for the patch.  Looks very good. Approved.

--
Øystein

On 11/ 8/10 03:51 PM, Olav Sandstaa wrote:
> #At file:///export/home/tmp/mysql/wl5639-opt-backporting/ based on
> revid:olav.sandstaa@stripped
>
>   3280 Olav Sandstaa	2010-11-08
>        Patch for WL#5639 "Enable Index Condition Pushdown optimizer feature"
>
>        With this change the MySQL server will be built with support for the
>        Index Condition Pushdown (ICP) feature. The default will be that ICP
>        will be enabled.
>
>        The following changes to MySQL are implemented:
>
>        1. Enable index condition pushdown
>
>        With this feature enabled the optimizer will consider to push down to the
>        handler/storage engine parts (or all) of the where condition that can be
>        evaluated by the handler/storage engine using the content of the index that
> is
>        used for execution of the query.
>
>        The main change by enabling index condition pushdown should be improved
>        performance for certain queries. The user should be able to see if index
>        condition pushdown has been used by looking in the "Extra" column of the
> EXPLAIN
>        output. If this field contains the string "Using index condition" parts or
> all
>        of the where statement has been pushed down and will be evaluated by the
>        handler/storage engine.
>
>        2. Enable the user to control the use of index condition pushdown
>
>        When this has been enabled the user will be able to control the use
>        of index condition pushdown by using the optimizer_switch configuration
> variable.
>        The new optimizer_switch flag that will become available is named
>        "index_condition_pushdown" and can take the values "on", "off" and "default".
>        The default value for this optimizer switch flag will be "on".
>       @ mysql-test/r/index_merge_myisam.result
>          Change in result file caused by changing the order for optimizer switch
>          flags due to enabling Index Condition Pushdown.
>       @ mysql-test/r/mysqld--help-notwin.result
>          Change in result file caused by changing the order for optimizer switch
>          flags due to enabling Index Condition Pushdown.
>       @ mysql-test/r/mysqld--help-win.result
>          Change in result file caused by changing the order for optimizer switch
>          flags due to enabling Index Condition Pushdown.
>       @ mysql-test/r/optimizer_switch.result
>          Change in result file caused by changing the order for optimizer switch
>          flags due to enabling Index Condition Pushdown.
>       @ 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 Index Condition Pushdown.
>       @ sql/sql_priv.h
>          Enable Index Condition Pushdown by default. The
>          OPTIMIZER_SWITCH_INDEX_CONDITION_PUSHDOWN flag will be included in the
>          OPTIMIZER_SWITCH_DEFAULT macro also for the case where
>          OPTIMIZER_SWITCH_ALL is undefined.
>       @ sql/sys_vars.cc
>          Enable Index Condition Pushdown by changing *optimizer_switch_names
> variable
>          so that "index_condition_pushdown" is 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-04 15:23:00 +0000
> +++ b/mysql-test/r/index_merge_myisam.result	2010-11-08 14:51:09 +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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   #
>   # 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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   drop table t0, t1;
>
> === modified file 'mysql-test/r/mysqld--help-notwin.result'
> --- a/mysql-test/r/mysqld--help-notwin.result	2010-11-04 15:23:00 +0000
> +++ b/mysql-test/r/mysqld--help-notwin.result	2010-11-08 14:51:09 +0000
> @@ -406,10 +406,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, materialization, semijoin,
> - loosescan, firstmatch, mrr, mrr_cost_based,
> - index_condition_pushdown} and val is one of {on, off,
> - default}
> + engine_condition_pushdown, index_condition_pushdown,
> + materialization, semijoin, loosescan, firstmatch, mrr,
> + mrr_cost_based} and val is one of {on, off, default}
>    --performance-schema
>    Enable the performance schema.
>    --performance-schema-events-waits-history-long-size=#
> @@ -855,7 +854,7 @@
>   optimizer-join-cache-level 1
>   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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=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-04 15:23:00 +0000
> +++ b/mysql-test/r/mysqld--help-win.result	2010-11-08 14:51:09 +0000
> @@ -406,10 +406,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, materialization, semijoin,
> - loosescan, firstmatch, mrr, mrr_cost_based,
> - index_condition_pushdown} and val is one of {on, off,
> - default}
> + engine_condition_pushdown, index_condition_pushdown,
> + materialization, semijoin, loosescan, firstmatch, mrr,
> + mrr_cost_based} and val is one of {on, off, default}
>    --performance-schema
>    Enable the performance schema.
>    --performance-schema-events-waits-history-long-size=#
> @@ -859,7 +858,7 @@
>   optimizer-join-cache-level 1
>   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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=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-10-15 10:32:50 +0000
> +++ b/mysql-test/r/optimizer_switch.result	2010-11-08 14:51:09 +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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=off,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=off,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=on,semijoin=off,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=off,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=on,semijoin=on,loosescan=off,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=off,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=off,semijoin=off,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=off,semijoin=off,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=off,semijoin=off,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=off,semijoin=off,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=off,semijoin=off,loosescan=off,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=off,semijoin=off,loosescan=off,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=on,semijoin=off,loosescan=off,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=off,loosescan=off,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=off,semijoin=on,loosescan=off,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=off,semijoin=on,loosescan=off,firstmatch=on,mrr=on,mrr_cost_based=off
>   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-10-22 14:23:31
> +0000
> +++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result	2010-11-08 14:51:09
> +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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>   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,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off,index_condition_pushdown=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,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=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,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off,index_condition_pushdown=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,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=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,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off,index_condition_pushdown=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,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=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,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off,index_condition_pushdown=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,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=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,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off,index_condition_pushdown=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,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=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,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off,index_condition_pushdown=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,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=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,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off,index_condition_pushdown=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,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=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,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off,index_condition_pushdown=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,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=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,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off,index_condition_pushdown=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,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off
>
> === modified file 'sql/sql_priv.h'
> --- a/sql/sql_priv.h	2010-11-04 15:23:00 +0000
> +++ b/sql/sql_priv.h	2010-11-08 14:51:09 +0000
> @@ -150,47 +150,51 @@
>   #define OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION    (1ULL<<  2)
>   #define OPTIMIZER_SWITCH_INDEX_MERGE_INTERSECT     (1ULL<<  3)
>   #define OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN (1ULL<<  4)
> -#define OPTIMIZER_SWITCH_MATERIALIZATION           (1ULL<<  5)
> -#define OPTIMIZER_SWITCH_SEMIJOIN                  (1ULL<<  6)
> -#define OPTIMIZER_SWITCH_LOOSE_SCAN                (1ULL<<  7)
> -#define OPTIMIZER_SWITCH_FIRSTMATCH                (1ULL<<  8)
> +#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<<  9)
> +#define OPTIMIZER_SWITCH_MRR                       (1ULL<<  10)
>   /**
>      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<<  10)
> -#define OPTIMIZER_SWITCH_INDEX_CONDITION_PUSHDOWN  (1ULL<<  11)
> +#define OPTIMIZER_SWITCH_MRR_COST_BASED            (1ULL<<  11)
>   #define OPTIMIZER_SWITCH_LAST                      (1ULL<<  12)
>
>   /**
>      If OPTIMIZER_SWITCH_ALL is defined, optimizer_switch flags for newer
> -   optimizer features (semijoin, MRR, ICP) will be available.
> +   optimizer features (semijoin, MRR) will be available.
>    */
>   #define OPTIMIZER_SWITCH_ALL 1
>
> -/* The following must be kept in sync with optimizer_switch_str in mysqld.cc */
> +/*
> +  The following must be kept in sync with optimizer_switch string in
> +  sys_vars.cc.
> +*/
>   #ifdef OPTIMIZER_SWITCH_ALL
>   #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_MATERIALIZATION | \
>                                     OPTIMIZER_SWITCH_SEMIJOIN | \
>                                     OPTIMIZER_SWITCH_LOOSE_SCAN | \
>                                     OPTIMIZER_SWITCH_FIRSTMATCH | \
> -                                  OPTIMIZER_SWITCH_MRR | \
> +                                  OPTIMIZER_SWITCH_MRR)
> +#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)
> -#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)
>   #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-02 11:53:54 +0000
> +++ b/sql/sys_vars.cc	2010-11-08 14:51:09 +0000
> @@ -1412,9 +1412,10 @@
>   {
>     "index_merge", "index_merge_union", "index_merge_sort_union",
>     "index_merge_intersection", "engine_condition_pushdown",
> +  "index_condition_pushdown",
>   #ifdef OPTIMIZER_SWITCH_ALL
>     "materialization", "semijoin", "loosescan", "firstmatch",
> -  "mrr", "mrr_cost_based", "index_condition_pushdown",
> +  "mrr", "mrr_cost_based",
>   #endif
>     "default", NullS
>   };
> @@ -1431,11 +1432,11 @@
>          "optimizer_switch",
>          "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_merge_intersection, engine_condition_pushdown, "
> +       "index_condition_pushdown"
>   #ifdef OPTIMIZER_SWITCH_ALL
>          ", materialization, "
> -       "semijoin, loosescan, firstmatch, mrr, mrr_cost_based, "
> -       "index_condition_pushdown"
> +       "semijoin, loosescan, firstmatch, mrr, mrr_cost_based"
>   #endif
>          "} and val is one of {on, off, default}",
>          SESSION_VAR(optimizer_switch), CMD_LINE(REQUIRED_ARG),
>
>
>
>
>


-- 
Øystein Grøvlen, Senior Staff Engineer
Sun Microsystems, Database Group
Trondheim, Norway
Thread
bzr commit into mysql-next-mr-bugfixing branch (olav.sandstaa:3280) WL#5639Olav Sandstaa8 Nov
  • Re: bzr commit into mysql-next-mr-bugfixing branch (olav.sandstaa:3280)WL#5639Øystein Grøvlen11 Nov