List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:December 1 2010 9:08pm
Subject:bzr push into mysql-trunk-bugfixing branch (olav.sandstaa:3292 to 3293)
WL#5692
View as plain text  
 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
 3292 Olav Sandstaa	2010-11-30
      WL#5692 Enable Disk-Sweep Multi-Range Read optimizer feature.
      
      This patch contains the first part of this worklog. It changes the default
      value of the optimizer switch "mrr_cost_based" from being off as default
      to be on by default.
      
      To ensure that both non-cost-based MRR and cost-based MRR get tested the
      following changes are done to our MRR tests:
      
      1. to ensure MRR code get tested: the optimizer_switch flag mrr_cost_based
         is set to off in test that specifically is named to test MRR (these
         have _mrr or _all in their name).
      
      2. to ensure cost-based MRR get tested: New versions of existing MRR tests
         are added. These have added _mrr_cost in their test name.
     @ mysql-test/r/ctype_binary.result
        Change in explain output after making mrr_cost_based be "on" by default.
     @ mysql-test/r/ctype_collate.result
        Change in explain output after making mrr_cost_based be "on" by default.
     @ mysql-test/r/ctype_cp1251.result
        Change in explain output after making mrr_cost_based be "on" by default.
     @ mysql-test/r/ctype_latin1.result
        Change in explain output after making mrr_cost_based be "on" by default.
     @ mysql-test/r/ctype_utf8.result
        Change in explain output after making mrr_cost_based be "on" by default.
     @ mysql-test/r/func_in_all.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/func_in_icp_mrr.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/func_in_mrr.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/func_in_mrr_cost.result
        Test result file for the new new test for cost-based MRR.
     @ mysql-test/r/index_merge_myisam.result
        Change in explain output after making mrr_cost_based be "on" by default.
     @ mysql-test/r/innodb_mrr.result
        Change in output after making the test run with mrr_cost_based off.
        
        Note that the change in explain output is caused by running part of the
        test case on a separate connection that uses default values for optimizer 
        switch (something that should be fixed in a separate patch).
     @ mysql-test/r/innodb_mrr_all.result
        Change in output after making the test run with mrr_cost_based off.
        
        Note that the change in explain output is caused by running part of the
        test case on a separate connection that uses default values for optimizer 
        switch (something that should be fixed in a separate patch).
     @ mysql-test/r/innodb_mrr_cost.result
        Test result file for the new new test for cost-based MRR.
     @ mysql-test/r/innodb_mrr_cost_all.result
        Test result file for the new new test for cost-based MRR.
     @ mysql-test/r/innodb_mrr_cost_icp.result
        Test result file for the new new test for cost-based MRR.
     @ mysql-test/r/innodb_mrr_icp.result
        Change in output after making the test run with mrr_cost_based off.
        
        Note that the change in explain output is caused by running part of the
        test case on a separate connection that uses default values for optimizer 
        switch (something that should be fixed in a separate patch).
     @ mysql-test/r/innodb_mrr_none.result
        Change in explain output after making mrr_cost_based be "on" by default.
     @ mysql-test/r/join_cache_jcl1.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/join_cache_jcl2.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/join_cache_jcl3.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/join_cache_jcl4.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/join_cache_jcl5.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/join_cache_jcl6.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/join_cache_jcl7.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/join_cache_jcl8.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/join_nested_jcl6.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/join_outer_jcl6.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/merge.result
        Change in explain output after making mrr_cost_based be "on" by default.
     @ mysql-test/r/mix2_myisam.result
        Change in explain output after making mrr_cost_based be "on" by default.
     @ mysql-test/r/myisam.result
        Change in explain output after making mrr_cost_based be "on" by default.
     @ mysql-test/r/myisam_mrr.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/myisam_mrr_all.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/myisam_mrr_cost.result
        Test result file for the new new test for cost-based MRR.
     @ mysql-test/r/myisam_mrr_cost_all.result
        Test result file for the new new test for cost-based MRR.
     @ mysql-test/r/myisam_mrr_cost_icp.result
        Test result file for the new new test for cost-based MRR.
     @ mysql-test/r/myisam_mrr_icp.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/mysqld--help-notwin.result
        Change in output after making mrr_cost_based be "on" by default.
     @ mysql-test/r/mysqld--help-win.result
        Change in output after making mrr_cost_based be "on" by default.
     @ mysql-test/r/null.result
        Change in explain output after making mrr_cost_based be "on" by default.
     @ mysql-test/r/optimizer_switch.result
        Change in explain output after making mrr_cost_based be "on" by default.
     @ mysql-test/r/order_by_all.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/order_by_icp_mrr.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/ps_1general.result
        Change in explain output after making mrr_cost_based be "on" by default.
     @ mysql-test/r/range_all.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/range_icp_mrr.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/range_mrr.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/range_mrr_cost.result
        Test result file for the new new test for cost-based MRR.
     @ mysql-test/r/select_all.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/select_all_jcl6.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/select_icp_mrr.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/select_icp_mrr_jcl6.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/subquery_all.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/subquery_all_jcl6.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/subquery_nomat_nosj.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/subquery_nomat_nosj_jcl6.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/subquery_sj_all.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/subquery_sj_all_jcl6.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/subquery_sj_all_jcl7.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/subquery_sj_innodb_all.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/subquery_sj_innodb_all_jcl6.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/r/subquery_sj_innodb_all_jcl7.result
        Change in output after making the test run with mrr_cost_based off.
     @ mysql-test/suite/innodb/r/innodb.result
        Change in explain output after making mrr_cost_based be "on" by default.
     @ mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
        Change in explain output after making mrr_cost_based be "on" by default.
     @ mysql-test/t/func_in_all.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/func_in_icp_mrr.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/func_in_mrr.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/func_in_mrr_cost.test
        New version of func_in test that is run with cost-based MRR.
     @ mysql-test/t/innodb_mrr.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/innodb_mrr_all.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/innodb_mrr_cost.test
        New version of MRR test for InnoDB that is run with cost-based MRR.
     @ mysql-test/t/innodb_mrr_cost_all.test
        New version of MRR test for InnoDB that is run with all optimizer features on.
     @ mysql-test/t/innodb_mrr_cost_icp.test
        New version of MRR test for InnoDB that is run with cost-based MRR and ICP.
     @ mysql-test/t/innodb_mrr_icp.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/join_cache_jcl1.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/join_cache_jcl2.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/join_cache_jcl3.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/join_cache_jcl4.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/join_cache_jcl5.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/join_cache_jcl6.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/join_cache_jcl7.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/join_cache_jcl8.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/join_nested_jcl6.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/join_outer_jcl6.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/myisam_mrr.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/myisam_mrr_all.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/myisam_mrr_cost.test
        New version of MRR test for MyISAM that is run with cost-based MRR.
     @ mysql-test/t/myisam_mrr_cost_all.test
        New version of MRR test for MyISAM that is run with all optimizer features.
     @ mysql-test/t/myisam_mrr_cost_icp.test
        New version of MRR test for MyISAM that is run with cost-based MRR and ICP.
     @ mysql-test/t/myisam_mrr_icp.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/order_by_all.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/order_by_icp_mrr.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/range_all.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/range_icp_mrr.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/range_mrr.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/range_mrr_cost.test
        New variant of the range test that is run with cost-based MRR.
     @ mysql-test/t/select_all.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/select_icp_mrr.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/subquery_all.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/subquery_nomat_nosj.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/subquery_sj_all.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ mysql-test/t/subquery_sj_innodb_all.test
        Set mrr_cost_based to off to ensure that MRR is used by the test. This is 
        done as a consequence of changing the default value for mrr_cost_based
        to be "on".
     @ sql/sql_priv.h
        WL#5692 Enable Disk-Sweep Multi-Range Read optimizer feature.
        
        Change default value for the optimizer switch mrr_cost_based from
        being off to being on.

    added:
      mysql-test/r/func_in_mrr_cost.result
      mysql-test/r/innodb_mrr_cost.result
      mysql-test/r/innodb_mrr_cost_all.result
      mysql-test/r/innodb_mrr_cost_icp.result
      mysql-test/r/myisam_mrr_cost.result
      mysql-test/r/myisam_mrr_cost_all.result
      mysql-test/r/myisam_mrr_cost_icp.result
      mysql-test/r/range_mrr_cost.result
      mysql-test/t/func_in_mrr_cost.test
      mysql-test/t/innodb_mrr_cost-master.opt
      mysql-test/t/innodb_mrr_cost.test
      mysql-test/t/innodb_mrr_cost_all-master.opt
      mysql-test/t/innodb_mrr_cost_all.test
      mysql-test/t/innodb_mrr_cost_icp-master.opt
      mysql-test/t/innodb_mrr_cost_icp.test
      mysql-test/t/myisam_mrr_cost.test
      mysql-test/t/myisam_mrr_cost_all.test
      mysql-test/t/myisam_mrr_cost_icp.test
      mysql-test/t/range_mrr_cost.test
    modified:
      mysql-test/r/ctype_binary.result
      mysql-test/r/ctype_collate.result
      mysql-test/r/ctype_cp1251.result
      mysql-test/r/ctype_latin1.result
      mysql-test/r/ctype_utf8.result
      mysql-test/r/func_in_all.result
      mysql-test/r/func_in_icp_mrr.result
      mysql-test/r/func_in_mrr.result
      mysql-test/r/index_merge_myisam.result
      mysql-test/r/innodb_mrr.result
      mysql-test/r/innodb_mrr_all.result
      mysql-test/r/innodb_mrr_icp.result
      mysql-test/r/innodb_mrr_none.result
      mysql-test/r/join_cache_jcl1.result
      mysql-test/r/join_cache_jcl2.result
      mysql-test/r/join_cache_jcl3.result
      mysql-test/r/join_cache_jcl4.result
      mysql-test/r/join_cache_jcl5.result
      mysql-test/r/join_cache_jcl6.result
      mysql-test/r/join_cache_jcl7.result
      mysql-test/r/join_cache_jcl8.result
      mysql-test/r/join_nested_jcl6.result
      mysql-test/r/join_outer_jcl6.result
      mysql-test/r/merge.result
      mysql-test/r/mix2_myisam.result
      mysql-test/r/myisam.result
      mysql-test/r/myisam_mrr.result
      mysql-test/r/myisam_mrr_all.result
      mysql-test/r/myisam_mrr_icp.result
      mysql-test/r/mysqld--help-notwin.result
      mysql-test/r/mysqld--help-win.result
      mysql-test/r/null.result
      mysql-test/r/optimizer_switch.result
      mysql-test/r/order_by_all.result
      mysql-test/r/order_by_icp_mrr.result
      mysql-test/r/ps_1general.result
      mysql-test/r/range_all.result
      mysql-test/r/range_icp_mrr.result
      mysql-test/r/range_mrr.result
      mysql-test/r/select_all.result
      mysql-test/r/select_all_jcl6.result
      mysql-test/r/select_icp_mrr.result
      mysql-test/r/select_icp_mrr_jcl6.result
      mysql-test/r/subquery_all.result
      mysql-test/r/subquery_all_jcl6.result
      mysql-test/r/subquery_nomat_nosj.result
      mysql-test/r/subquery_nomat_nosj_jcl6.result
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_innodb_all.result
      mysql-test/r/subquery_sj_innodb_all_jcl6.result
      mysql-test/r/subquery_sj_innodb_all_jcl7.result
      mysql-test/suite/innodb/r/innodb.result
      mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
      mysql-test/t/func_in_all.test
      mysql-test/t/func_in_icp_mrr.test
      mysql-test/t/func_in_mrr.test
      mysql-test/t/innodb_mrr.test
      mysql-test/t/innodb_mrr_all.test
      mysql-test/t/innodb_mrr_icp.test
      mysql-test/t/join_cache_jcl1.test
      mysql-test/t/join_cache_jcl2.test
      mysql-test/t/join_cache_jcl3.test
      mysql-test/t/join_cache_jcl4.test
      mysql-test/t/join_cache_jcl5.test
      mysql-test/t/join_cache_jcl6.test
      mysql-test/t/join_cache_jcl7.test
      mysql-test/t/join_cache_jcl8.test
      mysql-test/t/join_nested_jcl6.test
      mysql-test/t/join_outer_jcl6.test
      mysql-test/t/myisam_mrr.test
      mysql-test/t/myisam_mrr_all.test
      mysql-test/t/myisam_mrr_icp.test
      mysql-test/t/order_by_all.test
      mysql-test/t/order_by_icp_mrr.test
      mysql-test/t/range_all.test
      mysql-test/t/range_icp_mrr.test
      mysql-test/t/range_mrr.test
      mysql-test/t/select_all.test
      mysql-test/t/select_icp_mrr.test
      mysql-test/t/subquery_all.test
      mysql-test/t/subquery_nomat_nosj.test
      mysql-test/t/subquery_sj_all.test
      mysql-test/t/subquery_sj_innodb_all.test
      sql/sql_priv.h
=== 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),

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk-bugfixing branch (olav.sandstaa:3292 to 3293)WL#5692Olav Sandstaa1 Dec