#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
