3249 Tor Didriksen 2010-09-22
Revert parts of previous auto-merge
modified:
mysql-test/r/create.result
mysql-test/r/ctype_collate.result
mysql-test/r/explain.result
mysql-test/r/group_by.result
mysql-test/r/group_min_max.result
mysql-test/r/index_merge_myisam.result
mysql-test/r/innodb_mrr_none.result
mysql-test/r/join_cache_jcl1.result
mysql-test/r/join_nested.result
mysql-test/r/join_optimizer.result
mysql-test/r/merge.result
mysql-test/r/mix2_myisam.result
mysql-test/r/myisam.result
mysql-test/r/mysqld--help-notwin.result
mysql-test/r/null.result
mysql-test/r/optimizer_switch_eng_cond_pushdown1.result
mysql-test/r/optimizer_switch_eng_cond_pushdown2.result
mysql-test/r/ps_1general.result
mysql-test/r/subselect_innodb.result
mysql-test/suite/innodb/r/innodb.result
mysql-test/suite/innodb/r/innodb_mysql.result
mysql-test/suite/sys_vars/r/engine_condition_pushdown_basic.result
mysql-test/suite/sys_vars/r/optimizer_join_cache_level_basic.result
mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
sql/sql_priv.h
3248 Roy Lyseng 2010-09-22
Refactoring: Make members positions and best_positions of class JOIN
dynamically allocated instead of fixed-size arrays.
This saves approx. 20 kbyte memory during optimization and execution
for normal-sized join operations.
sql/sql_select.cc
Allocate positions and best_positions dynamically.
sql/sql_select.h
positions and best_positions (members of class JOIN) defined as
pointers instead of arrays. Made a derived class of Sql_alloc, so
operator new can be used instead of alloc().
modified:
sql/sql_select.cc
sql/sql_select.h
=== modified file 'mysql-test/r/create.result'
--- a/mysql-test/r/create.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/r/create.result 2010-09-22 14:48:50 +0000
@@ -113,7 +113,7 @@ insert into t1 (b) values ("hello"),("my
create table t2 (key (b)) select * from t1;
explain select * from t2 where b="world";
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ref B B 21 const 1 Using where
+1 SIMPLE t2 ref B B 21 const 1 Using index condition
select * from t2 where b="world";
a B
3 world
=== modified file 'mysql-test/r/ctype_collate.result'
--- a/mysql-test/r/ctype_collate.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/r/ctype_collate.result 2010-09-22 14:48:50 +0000
@@ -596,31 +596,31 @@ INSERT INTO t1 VALUES ('i','i');
INSERT INTO t1 VALUES ('j','j');
EXPLAIN SELECT * FROM t1 WHERE s1='a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref s1 s1 11 const 1 Using where
+1 SIMPLE t1 ref s1 s1 11 const 1 Using index condition
EXPLAIN SELECT * FROM t1 WHERE s2='a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref s2 s2 11 const 1 Using where
+1 SIMPLE t1 ref s2 s2 11 const 1 Using index condition
EXPLAIN SELECT * FROM t1 WHERE s1='a' COLLATE latin1_german1_ci;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref s1 s1 11 const 1 Using where
+1 SIMPLE t1 ref s1 s1 11 const 1 Using index condition
EXPLAIN SELECT * FROM t1 WHERE s2='a' COLLATE latin1_german1_ci;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where
EXPLAIN SELECT * FROM t1 WHERE s1 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range s1 s1 11 NULL 2 Using where
+1 SIMPLE t1 range s1 s1 11 NULL 2 Using index condition; Using MRR
EXPLAIN SELECT * FROM t1 WHERE s2 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where
EXPLAIN SELECT * FROM t1 WHERE s1 IN ('a','b' COLLATE latin1_german1_ci);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range s1 s1 11 NULL 2 Using where
+1 SIMPLE t1 range s1 s1 11 NULL 2 Using index condition; Using MRR
EXPLAIN SELECT * FROM t1 WHERE s2 IN ('a','b' COLLATE latin1_german1_ci);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where
EXPLAIN SELECT * FROM t1 WHERE s1 LIKE 'a' COLLATE latin1_german1_ci;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range s1 s1 11 NULL 1 Using where
+1 SIMPLE t1 range s1 s1 11 NULL 1 Using index condition; Using MRR
EXPLAIN SELECT * FROM t1 WHERE s2 LIKE 'a' COLLATE latin1_german1_ci;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where
=== modified file 'mysql-test/r/explain.result'
--- a/mysql-test/r/explain.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/r/explain.result 2010-09-22 14:48:50 +0000
@@ -13,7 +13,7 @@ id str
3 foo
explain select * from t1 where str is null;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref str str 11 const 1 Using where
+1 SIMPLE t1 ref str str 11 const 1 Using index condition
explain select * from t1 where str="foo";
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const str str 11 const 1
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/r/group_by.result 2010-09-22 14:48:50 +0000
@@ -1543,8 +1543,8 @@ id select_type table type possible_keys
EXPLAIN SELECT 1 FROM t1 WHERE a IN
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL PRIMARY 4 NULL 144 Using where; Using index
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 144 Using where
+1 PRIMARY t1 index PRIMARY,i2 PRIMARY 4 NULL 144 Using index
+1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t1)
CREATE TABLE t2 (a INT, b INT, KEY(a));
INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;
@@ -1556,8 +1556,8 @@ id select_type table type possible_keys
EXPLAIN SELECT 1 FROM t2 WHERE a IN
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 144 Using where
+1 PRIMARY t2 index a a 5 NULL 4 Using index
+1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t2)
SHOW VARIABLES LIKE 'old';
Variable_name Value
old OFF
=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/r/group_min_max.result 2010-09-22 14:48:50 +0000
@@ -2256,7 +2256,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHE
a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1_outer index NULL a 10 NULL 15 Using where; Using index
-2 DEPENDENT SUBQUERY t1 index NULL a 10 NULL 1 Using index
+2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by
EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING
a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
id select_type table type possible_keys key key_len ref rows Extra
=== modified file 'mysql-test/r/index_merge_myisam.result'
--- a/mysql-test/r/index_merge_myisam.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/r/index_merge_myisam.result 2010-09-22 14:48:50 +0000
@@ -19,7 +19,7 @@ Table Op Msg_type Msg_text
test.t0 analyze status OK
explain select * from t0 where key1 < 3 or key1 > 1020;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 range i1 i1 4 NULL 78 Using where
+1 SIMPLE t0 range i1 i1 4 NULL 78 Using index condition; Using MRR
explain
select * from t0 where key1 < 3 or key2 > 1020;
id select_type table type possible_keys key key_len ref rows Extra
@@ -72,7 +72,7 @@ id select_type table type possible_keys
1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 17 Using sort_union(i1,i2); Using where
explain select * from t0 where key2 = 45 or key1 <=> null;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 range i1,i2 i2 4 NULL 1 Using where
+1 SIMPLE t0 range i1,i2 i2 4 NULL 1 Using where; Using MRR
explain select * from t0 where key2 = 45 or key1 is not null;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t0 ALL i1,i2 NULL NULL NULL 1024 Using where
@@ -115,7 +115,7 @@ id select_type table type possible_keys
explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 100);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 range i1,i2,i3 i3 4 NULL 95 Using where
+1 SIMPLE t0 range i1,i2,i3 i3 4 NULL 95 Using index condition; Using where; Using MRR
explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 1000);
id select_type table type possible_keys key key_len ref rows Extra
@@ -275,7 +275,7 @@ id select_type table type possible_keys
explain select * from t0,t1 where t0.key1 < 3 and
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 range i1 i1 4 NULL 3 Using where
+1 SIMPLE t0 range i1 i1 4 NULL 3 Using index condition; Using MRR
1 SIMPLE t1 ALL i1,i8 NULL NULL NULL 1024 Range checked for each record (index map: 0x81)
explain select * from t1 where key1=3 or key2=4
union select * from t1 where key1<4 or key3=5;
@@ -1324,7 +1324,7 @@ primary key (pk1, pk2)
);
explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 7 Using where
+1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 7 Using index condition; Using where; Using MRR
select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2
1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2
@@ -1419,19 +1419,19 @@ drop table t1;
#
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_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
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_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
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_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
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_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
set optimizer_switch=4;
set optimizer_switch=NULL;
ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'NULL'
@@ -1457,21 +1457,21 @@ set optimizer_switch=default;
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_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
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_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
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_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
#
# 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_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
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),
@@ -1581,5 +1581,5 @@ id select_type table type possible_keys
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
+optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
drop table t0, t1;
=== modified file 'mysql-test/r/innodb_mrr_none.result'
--- a/mysql-test/r/innodb_mrr_none.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/r/innodb_mrr_none.result 2010-09-22 14:48:50 +0000
@@ -527,7 +527,7 @@ REPEATABLE-READ
START TRANSACTION;
EXPLAIN SELECT * FROM t1 WHERE a > 2 FOR UPDATE;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 5 NULL 2 Using where
+1 SIMPLE t1 range a a 5 NULL 2 Using where; Using MRR
SELECT * FROM t1 WHERE a > 2 FOR UPDATE;
dummy a b
3 3 3
=== modified file 'mysql-test/r/join_cache_jcl1.result'
--- a/mysql-test/r/join_cache_jcl1.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/r/join_cache_jcl1.result 2010-09-22 14:48:50 +0000
@@ -449,7 +449,7 @@ SELECT City.Name, Country.Name FROM City
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using where
+1 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR
1 SIMPLE City ref Population,Country Country 3 world.Country.Code 18 Using where
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -480,7 +480,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using where
+1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition; Using where
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -633,8 +633,8 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY City ALL Population NULL NULL NULL 4079 Using where
-2 DEPENDENT SUBQUERY Country unique_subquery PRIMARY,Name PRIMARY 3 func 1 Using where
+1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR
+1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
@@ -756,7 +756,7 @@ SELECT City.Name, Country.Name FROM City
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using where
+1 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR
1 SIMPLE City ref Population,Country Country 3 world.Country.Code 18 Using where
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -787,7 +787,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using where
+1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition; Using where
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -940,8 +940,8 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY City ALL Population NULL NULL NULL 4079 Using where
-2 DEPENDENT SUBQUERY Country unique_subquery PRIMARY,Name PRIMARY 3 func 1 Using where
+1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR
+1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
@@ -1019,7 +1019,7 @@ EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND City.Population > 3000000;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Population,Country Population 4 NULL # Using where
+1 SIMPLE City range Population,Country Population 4 NULL # Using index condition; Using MRR
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.City.Country #
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND City.Population > 3000000;
@@ -1306,7 +1306,7 @@ t1.metaid = t2.metaid AND t1.affiliateid
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t6 system PRIMARY NULL NULL NULL 1
1 SIMPLE t1 ref t1_affiliateid,t1_metaid t1_affiliateid 4 const 1
-1 SIMPLE t4 ref PRIMARY,t4_formatclassid,t4_formats_idx t4_formats_idx 1 const 1 Using where
+1 SIMPLE t4 ref PRIMARY,t4_formatclassid,t4_formats_idx t4_formats_idx 1 const 1 Using index condition; Using where
1 SIMPLE t5 eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 Using where
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1
1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index
@@ -1743,7 +1743,7 @@ explain select t2.f1, t2.f2, t2.f3 from
where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
-1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using where
+1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition
drop table t1,t2;
#
# Bug #42955: join with GROUP BY/ORDER BY and when BKA is enabled
@@ -2184,7 +2184,7 @@ where table3 .`pk` and table3 .`col_int_
and table3 .`col_varchar_key` = table2 .`col_varchar_nokey`;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table2 ALL NULL NULL NULL NULL 2 Using where
-1 SIMPLE table3 ref col_varchar_key col_varchar_key 4 test.table2.col_varchar_nokey 1 Using where
+1 SIMPLE table3 ref col_varchar_key col_varchar_key 4 test.table2.col_varchar_nokey 1 Using index condition; Using where
SELECT table2 .`col_int_key` FROM t1 table2,
t1 table3 force index (`col_varchar_key`)
where table3 .`pk` and table3 .`col_int_key` >= table2 .`pk`
=== modified file 'mysql-test/r/join_nested.result'
--- a/mysql-test/r/join_nested.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/r/join_nested.result 2010-09-22 14:48:50 +0000
@@ -1447,12 +1447,12 @@ id select_type table type possible_keys
1 SIMPLE t2 ALL NULL NULL NULL NULL X
1 SIMPLE t3 ref a a 5 test.t2.b X
1 SIMPLE t5 ref a a 5 test.t3.b X
-1 SIMPLE t4 ref a a 5 test.t3.b X Using where
+1 SIMPLE t4 ref a a 5 test.t3.b X Using index condition
explain select * from (t4 join t6 on t6.a=t4.b) right join t3 on t4.a=t3.b
join t2 left join (t5 join t7 on t7.a=t5.b) on t5.a=t2.b where t3.a<=>t2.b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL X
-1 SIMPLE t3 ref a a 5 test.t2.b X Using where
+1 SIMPLE t3 ref a a 5 test.t2.b X Using index condition
1 SIMPLE t4 ref a a 5 test.t3.b X
1 SIMPLE t6 ref a a 5 test.t4.b X
1 SIMPLE t5 ref a a 5 test.t2.b X
=== modified file 'mysql-test/r/join_optimizer.result'
--- a/mysql-test/r/join_optimizer.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/r/join_optimizer.result 2010-09-22 14:48:50 +0000
@@ -33,7 +33,7 @@ SELECT STRAIGHT_JOIN g.id FROM t2 a, t3
WHERE g.domain = 'queue' AND g.type = a.type;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a ALL NULL NULL NULL NULL 2 Using where
-1 SIMPLE g ref groups_dt groups_dt 70 const,test.a.type 13 Using where
+1 SIMPLE g ref groups_dt groups_dt 70 const,test.a.type 13 Using index condition
drop table t0,t1,t2,t3;
#
# BUG#47217 Lost optimization caused slowdown & wrong result.
@@ -61,7 +61,7 @@ Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
-Handler_read_next 2
+Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 1
=== modified file 'mysql-test/r/merge.result'
--- a/mysql-test/r/merge.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/r/merge.result 2010-09-22 14:48:50 +0000
@@ -677,7 +677,7 @@ id select_type table type possible_keys
EXPLAIN SELECT * FROM t1 WHERE fileset_id = 2
AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY,files PRIMARY 35 NULL 5 Using where
+1 SIMPLE t1 range PRIMARY,files PRIMARY 35 NULL 5 Using index condition; Using MRR
EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2
AND file_code = '0000000115' LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
=== modified file 'mysql-test/r/mix2_myisam.result'
--- a/mysql-test/r/mix2_myisam.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/r/mix2_myisam.result 2010-09-22 14:48:50 +0000
@@ -1114,11 +1114,11 @@ count(*)
29267
explain select * from t1 where c between 1 and 2500;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range c c 5 NULL # Using where
+1 SIMPLE t1 range c c 5 NULL # Using index condition; Using MRR
update t1 set c=a;
explain select * from t1 where c between 1 and 2500;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range c c 5 NULL # Using where
+1 SIMPLE t1 range c c 5 NULL # Using index condition; Using MRR
drop table t1,t2;
create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=MyISAM;
insert into t1 (id) values (null),(null),(null),(null),(null);
@@ -1559,7 +1559,7 @@ qq
*a *a*a *
explain select * from t1 where v='a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref v,v_2 # 13 const # Using where
+1 SIMPLE t1 ref v,v_2 # 13 const # Using index condition
select v,count(*) from t1 group by v limit 10;
v count(*)
a 1
@@ -1735,7 +1735,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ref v v 303 const # Using where; Using index
explain select * from t1 where v='a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref v v 303 const # Using where
+1 SIMPLE t1 ref v v 303 const # Using index condition
select v,count(*) from t1 group by v limit 10;
v count(*)
a 1
=== modified file 'mysql-test/r/myisam.result'
--- a/mysql-test/r/myisam.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/r/myisam.result 2010-09-22 14:48:50 +0000
@@ -368,7 +368,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where
explain select * from t1 force index (a) where a=0 or a=2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 4 NULL 4 Using where
+1 SIMPLE t1 range a a 4 NULL 4 Using index condition; Using MRR
explain select * from t1 where c=1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref c,c_2 c 5 const 1
@@ -1232,7 +1232,7 @@ qq
*a *a*a *
explain select * from t1 where v='a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref v,v_2 # 13 const # Using where
+1 SIMPLE t1 ref v,v_2 # 13 const # Using index condition
select v,count(*) from t1 group by v limit 10;
v count(*)
a 1
@@ -1408,7 +1408,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ref v v 303 const # Using where; Using index
explain select * from t1 where v='a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref v v 303 const # Using where
+1 SIMPLE t1 ref v v 303 const # Using index condition
select v,count(*) from t1 group by v limit 10;
v count(*)
a 1
@@ -2432,7 +2432,7 @@ insert into t2 select A.a + 10 *B.a, 1,
explain select * from t1, t2 where t2.a=t1.a and t2.b + 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
-1 SIMPLE t2 ref a a 4 test.t1.a 1 Using where
+1 SIMPLE t2 ref a a 4 test.t1.a 1 Using index condition
select * from t1, t2 where t2.a=t1.a and t2.b + 1;
a a b filler
0 0 1 filler
=== modified file 'mysql-test/r/mysqld--help-notwin.result'
--- a/mysql-test/r/mysqld--help-notwin.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/r/mysqld--help-notwin.result 2010-09-22 14:48:50 +0000
@@ -405,7 +405,9 @@ The following options may be given as th
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} and val is one of {on, off,
+ engine_condition_pushdown, materialization, semijoin,
+ loosescan, firstmatch, mrr, mrr_cost_based,
+ index_condition_pushdown} and val is one of {on, off,
default}
--performance-schema
Enable the performance schema.
@@ -850,7 +852,7 @@ old-style-user-limits FALSE
optimizer-join-cache-level 1
optimizer-prune-level 1
optimizer-search-depth 62
-optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
+optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
performance-schema FALSE
performance-schema-events-waits-history-long-size 10000
performance-schema-events-waits-history-size 10
=== modified file 'mysql-test/r/null.result'
--- a/mysql-test/r/null.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/r/null.result 2010-09-22 14:48:50 +0000
@@ -148,10 +148,10 @@ insert into t1 values
(7,7), (8,8), (9,9), (10,10), (11,11), (12,12);
explain select * from t1 where a between 2 and 3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range idx idx 4 NULL 2 Using where
+1 SIMPLE t1 range idx idx 4 NULL 2 Using index condition; Using MRR
explain select * from t1 where a between 2 and 3 or b is null;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range idx idx 4 NULL 2 Using where
+1 SIMPLE t1 range idx idx 4 NULL 2 Using index condition; Using MRR
drop table t1;
select cast(NULL as signed);
cast(NULL as signed)
=== modified file 'mysql-test/r/optimizer_switch_eng_cond_pushdown1.result'
--- a/mysql-test/r/optimizer_switch_eng_cond_pushdown1.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/r/optimizer_switch_eng_cond_pushdown1.result 2010-09-22 14:48:50 +0000
@@ -2,4 +2,4 @@ select @@session.engine_condition_pushdo
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch
-1 1 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
+1 1 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
=== modified file 'mysql-test/r/optimizer_switch_eng_cond_pushdown2.result'
--- a/mysql-test/r/optimizer_switch_eng_cond_pushdown2.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/r/optimizer_switch_eng_cond_pushdown2.result 2010-09-22 14:48:50 +0000
@@ -2,4 +2,4 @@ select @@session.engine_condition_pushdo
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch
-0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off
+0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
=== modified file 'mysql-test/r/ps_1general.result'
--- a/mysql-test/r/ps_1general.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/r/ps_1general.result 2010-09-22 14:48:50 +0000
@@ -467,9 +467,9 @@ def key 253 64 7 Y 0 31 8
def key_len 253 4096 1 Y 0 31 8
def ref 253 1024 0 Y 0 31 8
def rows 8 10 1 Y 32928 0 63
-def Extra 253 255 27 N 1 31 8
+def Extra 253 255 48 N 1 31 8
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where; Using filesort
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using index condition; Using MRR; Using filesort
drop table if exists t2;
create table t2 (id smallint, name varchar(20)) ;
prepare stmt1 from ' insert into t2 values(?, ?) ' ;
=== modified file 'mysql-test/r/subselect_innodb.result'
--- a/mysql-test/r/subselect_innodb.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/r/subselect_innodb.result 2010-09-22 14:48:50 +0000
@@ -279,13 +279,13 @@ id select_type table type possible_keys
1 PRIMARY t2 ALL DDOCTYPEID_IDX NULL NULL NULL 9 100.00 Using where
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 34 test.t2.DOCID 1 100.00
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 34 test.t2.DOCTYPEID 1 100.00
-2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 100.00 Using where
-3 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 100.00 Using where
-4 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 100.00 Using where
-5 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 100.00 Using where
-6 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 func 1 100.00 Using where
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 func 1 100.00 Using where
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 100.00 Using where
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 100.00 Using where
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 100.00 Using where
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 100.00 Using where
Warnings:
-Note 1003 select `test`.`t2`.`DOCID` AS `DOCID`,`test`.`t2`.`DOCNAME` AS `DOCNAME`,`test`.`t2`.`DOCTYPEID` AS `DOCTYPEID`,`test`.`t2`.`FOLDERID` AS `FOLDERID`,`test`.`t2`.`AUTHOR` AS `AUTHOR`,`test`.`t2`.`CREATED` AS `CREATED`,`test`.`t2`.`TITLE` AS `TITLE`,`test`.`t2`.`SUBTITLE` AS `SUBTITLE`,`test`.`t2`.`DOCABSTRACT` AS `DOCABSTRACT`,`test`.`t2`.`PUBLISHDATE` AS `PUBLISHDATE`,`test`.`t2`.`EXPIRATIONDATE` AS `EXPIRATIONDATE`,`test`.`t2`.`LOCKEDBY` AS `LOCKEDBY`,`test`.`t2`.`STATUS` AS `STATUS`,`test`.`t2`.`PARENTDOCID` AS `PARENTDOCID`,`test`.`t2`.`REPID` AS `REPID`,`test`.`t2`.`MODIFIED` AS `MODIFIED`,`test`.`t2`.`MODIFIER` AS `MODIFIER`,`test`.`t2`.`PUBLISHSTATUS` AS `PUBLISHSTATUS`,`test`.`t2`.`ORIGINATOR` AS `ORIGINATOR`,`test`.`t4`.`DOCTYPENAME` AS `DOCTYPENAME`,`test`.`t1`.`CONTENTSIZE` AS `CONTENTSIZE`,`test`.`t1`.`MIMETYPE` AS `MIMETYPE` from `test`.`t2` join `test`.`t4` left join `test`.`t1` on((`test`.`t1`.`DOCID` = `test`.`t2`.`DOCID`)) where ((`test`.`t4`.`DOCTY
PEID` = `test`.`t2`.`DOCTYPEID`) and (`test`.`t2`.`DOCNAME` = 'Last Discussion') and <in_optimizer>(`test`.`t2`.`FOLDERID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`FOLDERID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Movie Reviews') and <in_optimizer>(`test`.`t3`.`PARENTID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'CopiedFolder') and <in_optimizer>(`test`.`t3`.`PARENTID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Level3') and <in_optimizer>(`test`.`t3`.`PARENTID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Level2') and <in_optimizer>(`test`.`t3`.`PARENTID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Level1') and (`test`.`t3`.`PARENTID` = '2f6161e879db43c1a5b82c21
ddc49089') and (<cache>(`test`.`t3`.`PARENTID`) = `test`.`t3`.`FOLDERID`))))) and (<cache>(`test`.`t3`.`PARENTID`) = `test`.`t3`.`FOLDERID`))))) and (<cache>(`test`.`t3`.`PARENTID`) = `test`.`t3`.`FOLDERID`))))) and (<cache>(`test`.`t3`.`PARENTID`) = `test`.`t3`.`FOLDERID`))))) and (<cache>(`test`.`t2`.`FOLDERID`) = `test`.`t3`.`FOLDERID`))))))
+Note 1003 select `test`.`t2`.`DOCID` AS `DOCID`,`test`.`t2`.`DOCNAME` AS `DOCNAME`,`test`.`t2`.`DOCTYPEID` AS `DOCTYPEID`,`test`.`t2`.`FOLDERID` AS `FOLDERID`,`test`.`t2`.`AUTHOR` AS `AUTHOR`,`test`.`t2`.`CREATED` AS `CREATED`,`test`.`t2`.`TITLE` AS `TITLE`,`test`.`t2`.`SUBTITLE` AS `SUBTITLE`,`test`.`t2`.`DOCABSTRACT` AS `DOCABSTRACT`,`test`.`t2`.`PUBLISHDATE` AS `PUBLISHDATE`,`test`.`t2`.`EXPIRATIONDATE` AS `EXPIRATIONDATE`,`test`.`t2`.`LOCKEDBY` AS `LOCKEDBY`,`test`.`t2`.`STATUS` AS `STATUS`,`test`.`t2`.`PARENTDOCID` AS `PARENTDOCID`,`test`.`t2`.`REPID` AS `REPID`,`test`.`t2`.`MODIFIED` AS `MODIFIED`,`test`.`t2`.`MODIFIER` AS `MODIFIER`,`test`.`t2`.`PUBLISHSTATUS` AS `PUBLISHSTATUS`,`test`.`t2`.`ORIGINATOR` AS `ORIGINATOR`,`test`.`t4`.`DOCTYPENAME` AS `DOCTYPENAME`,`test`.`t1`.`CONTENTSIZE` AS `CONTENTSIZE`,`test`.`t1`.`MIMETYPE` AS `MIMETYPE` from `test`.`t2` join `test`.`t4` left join `test`.`t1` on((`test`.`t1`.`DOCID` = `test`.`t2`.`DOCID`)) where ((`test`.`t4`.`DOCTY
PEID` = `test`.`t2`.`DOCTYPEID`) and (`test`.`t2`.`DOCNAME` = 'Last Discussion') and <in_optimizer>(`test`.`t2`.`FOLDERID`,<exists>(select 1 from `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` where ((`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERNAME` = 'Level1') and (`test`.`t3`.`PARENTID` = '2f6161e879db43c1a5b82c21ddc49089') and (`test`.`t3`.`FOLDERNAME` = 'Level2') and (`test`.`t3`.`FOLDERNAME` = 'Level3') and (`test`.`t3`.`FOLDERNAME` = 'CopiedFolder') and (`test`.`t3`.`FOLDERNAME` = 'Movie Reviews') and (<cache>(`test`.`t2`.`FOLDERID`) = `test`.`t3`.`FOLDERID`)))))
drop table t1, t2, t3, t4;
CREATE TABLE t1 (
school_name varchar(45) NOT NULL,
=== modified file 'mysql-test/suite/innodb/r/innodb.result'
--- a/mysql-test/suite/innodb/r/innodb.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/suite/innodb/r/innodb.result 2010-09-22 14:48:50 +0000
@@ -1222,7 +1222,7 @@ count(*)
623
explain select * from t1 where c between 1 and 2500;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range c c 5 NULL # Using where
+1 SIMPLE t1 range c c 5 NULL # Using index condition; Using MRR
update t1 set c=a;
explain select * from t1 where c between 1 and 2500;
id select_type table type possible_keys key key_len ref rows Extra
@@ -1910,7 +1910,7 @@ qq
*a *a*a *
explain select * from t1 where v='a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref v,v_2 # 13 const # Using where
+1 SIMPLE t1 ref v,v_2 # 13 const # Using index condition
select v,count(*) from t1 group by v limit 10;
v count(*)
a 1
@@ -2086,7 +2086,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ref v v 303 const # Using where; Using index
explain select * from t1 where v='a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref v v 303 const # Using where
+1 SIMPLE t1 ref v v 303 const # Using index condition
select v,count(*) from t1 group by v limit 10;
v count(*)
a 1
=== modified file 'mysql-test/suite/innodb/r/innodb_mysql.result'
--- a/mysql-test/suite/innodb/r/innodb_mysql.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result 2010-09-22 14:48:50 +0000
@@ -1375,7 +1375,7 @@ INSERT INTO t1 (a,b,c) VALUES (1,1,1), (
INSERT INTO t1 (a,b,c) SELECT a+4,b,c FROM t1;
EXPLAIN SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range t1_b t1_b 5 NULL 8 Using where
+1 SIMPLE t1 range t1_b t1_b 5 NULL 8 Using index condition
SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5;
a b c
8 1 1
@@ -1787,7 +1787,7 @@ insert into t2 select A.a + 10 *B.a, 1,
explain select * from t1, t2 where t2.a=t1.a and t2.b + 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
-1 SIMPLE t2 ref a a 4 test.t1.a 1 Using where
+1 SIMPLE t2 ref a a 4 test.t1.a 1 Using index condition
select * from t1, t2 where t2.a=t1.a and t2.b + 1;
a a b filler
0 0 1 filler
@@ -2724,9 +2724,9 @@ set optimizer_join_cache_level=1;
EXPLAIN
SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 1
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, regular buffers)
-2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 1 Using where; Using index
+1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY t2 index NULL PRIMARY 4 NULL 1 Using index; Using join buffer (BNL, regular buffers)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (BNL, regular buffers)
SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2);
b
1
=== modified file 'mysql-test/suite/sys_vars/r/engine_condition_pushdown_basic.result'
--- a/mysql-test/suite/sys_vars/r/engine_condition_pushdown_basic.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/suite/sys_vars/r/engine_condition_pushdown_basic.result 2010-09-22 14:48:50 +0000
@@ -9,7 +9,7 @@ SELECT @global_start_value;
select @old_session_opt_switch:=@@session.optimizer_switch,
@old_global_opt_switch:=@@global.optimizer_switch;
@old_session_opt_switch:=@@session.optimizer_switch @old_global_opt_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_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
'#--------------------FN_DYNVARS_028_01------------------------#'
SET @@session.engine_condition_pushdown = 0;
Warnings:
@@ -212,7 +212,7 @@ select @@session.engine_condition_pushdo
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch
-0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off
+0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
set @@session.engine_condition_pushdown = TRUE;
Warnings:
Warning 1287 The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
@@ -220,7 +220,7 @@ select @@session.engine_condition_pushdo
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch
-1 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off
+1 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
set @@session.engine_condition_pushdown = FALSE;
Warnings:
Warning 1287 The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
@@ -228,7 +228,7 @@ select @@session.engine_condition_pushdo
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch
-0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off
+0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
set @@global.engine_condition_pushdown = TRUE;
Warnings:
Warning 1287 The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
@@ -236,7 +236,7 @@ select @@session.engine_condition_pushdo
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch
-0 1 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
+0 1 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
set @@global.engine_condition_pushdown = FALSE;
Warnings:
Warning 1287 The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
@@ -244,31 +244,31 @@ select @@session.engine_condition_pushdo
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch
-0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off
+0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
set @@session.optimizer_switch = "engine_condition_pushdown=on";
select @@session.engine_condition_pushdown,
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch
-1 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off
+1 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
set @@session.optimizer_switch = "engine_condition_pushdown=off";
select @@session.engine_condition_pushdown,
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch
-0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off
+0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
set @@global.optimizer_switch = "engine_condition_pushdown=on";
select @@session.engine_condition_pushdown,
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch
-0 1 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
+0 1 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
set @@global.optimizer_switch = "engine_condition_pushdown=off";
select @@session.engine_condition_pushdown,
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch
-0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off
+0 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
SET @@session.engine_condition_pushdown = @session_start_value;
Warnings:
Warning 1287 The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
@@ -287,4 +287,4 @@ select @@session.engine_condition_pushdo
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch
-1 1 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
+1 1 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
=== modified file 'mysql-test/suite/sys_vars/r/optimizer_join_cache_level_basic.result'
--- a/mysql-test/suite/sys_vars/r/optimizer_join_cache_level_basic.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/suite/sys_vars/r/optimizer_join_cache_level_basic.result 2010-09-22 14:48:50 +0000
@@ -18,23 +18,19 @@ select * from information_schema.session
VARIABLE_NAME VARIABLE_VALUE
OPTIMIZER_JOIN_CACHE_LEVEL 1
set global optimizer_join_cache_level=2;
-Warnings:
-Warning 1292 Truncated incorrect optimizer_join_cache_level value: '2'
select @@global.optimizer_join_cache_level;
@@global.optimizer_join_cache_level
-1
+2
set session optimizer_join_cache_level=4;
-Warnings:
-Warning 1292 Truncated incorrect optimizer_join_cache_level value: '4'
select @@session.optimizer_join_cache_level;
@@session.optimizer_join_cache_level
-1
+4
set session optimizer_join_cache_level=9;
Warnings:
Warning 1292 Truncated incorrect optimizer_join_cache_level value: '9'
select @@session.optimizer_join_cache_level;
@@session.optimizer_join_cache_level
-1
+8
set global optimizer_join_cache_level=1.1;
ERROR 42000: Incorrect argument type to variable 'optimizer_join_cache_level'
set global optimizer_join_cache_level=1e1;
=== modified file 'mysql-test/suite/sys_vars/r/optimizer_switch_basic.result'
--- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result 2010-09-20 15:05:13 +0000
+++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result 2010-09-22 14:48:50 +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_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
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_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
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_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
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
+optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
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
+optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
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
+OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
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
+OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
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_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off,index_condition_pushdown=off
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_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off,index_condition_pushdown=off
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_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off,index_condition_pushdown=off
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_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off,index_condition_pushdown=off
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
+optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off,index_condition_pushdown=off
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
+optimizer_switch index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off,index_condition_pushdown=off
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
+OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off,index_condition_pushdown=off
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
+OPTIMIZER_SWITCH index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off,index_condition_pushdown=off
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_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=off,mrr_cost_based=off,index_condition_pushdown=off
set global optimizer_switch=1.1;
ERROR 42000: Incorrect argument type to variable 'optimizer_switch'
set global optimizer_switch=1e1;
@@ -63,4 +63,4 @@ ERROR 42000: Variable 'optimizer_switch'
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_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
=== modified file 'sql/sql_priv.h'
--- a/sql/sql_priv.h 2010-09-20 15:05:13 +0000
+++ b/sql/sql_priv.h 2010-09-22 14:48:50 +0000
@@ -174,7 +174,7 @@
If OPTIMIZER_SWITCH_ALL is defined, optimizer_switch flags for newer
optimizer features (semijoin, MRR, ICP) will be available.
*/
-#undef OPTIMIZER_SWITCH_ALL
+#define OPTIMIZER_SWITCH_ALL 1
/* The following must be kept in sync with optimizer_switch_str in mysqld.cc */
#ifdef OPTIMIZER_SWITCH_ALL
Attachment: [text/bzr-bundle] bzr/tor.didriksen@oracle.com-20100922144850-bgpht7v0ib1qukqf.bundle
| Thread |
|---|
| • bzr push into mysql-next-mr-bugfixing branch (tor.didriksen:3248 to 3249) | Tor Didriksen | 23 Sep |
