List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:November 30 2010 6:16pm
Subject:bzr commit into mysql-trunk-bugfixing branch (olav.sandstaa:3293) WL#5692
View as plain text  
#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),


Attachment: [text/bzr-bundle] bzr/olav.sandstaa@oracle.com-20101130181643-3ho1fio4kxjq41zj.bundle
Thread
bzr commit into mysql-trunk-bugfixing branch (olav.sandstaa:3293) WL#5692Olav Sandstaa30 Nov
  • Re: bzr commit into mysql-trunk-bugfixing branch (olav.sandstaa:3293)WL#5692Roy Lyseng1 Dec