3405 Ole John Aske 2011-11-10 [merge]
Cherry picked fix for Bug#13330645 into mysql-trunk-cluster
(EXPLAIN OF 'USING MRR' IS INCORRECT IF HANDLER NATIVELY SUPPORTS 'HA_MRR_SORTED')
BEWARE: This fix is not yet reviewed, so we may have to update the MCP-tagged
code based on review comments. However, we need this fixed in order to
continue with integrating the new MRR interface with trunk-cluster.
Updated lots of resultfiles which now will explain 'Using MRR'.
modified:
mysql-test/suite/ndb/r/ndb_condition_pushdown.result
mysql-test/suite/ndb/r/ndb_join_pushdown.result
mysql-test/suite/ndb/r/ndb_read_multi_range.result
mysql-test/suite/ndb/r/ndb_row_count.result
mysql-test/suite/ndb/r/ndb_statistics0.result
mysql-test/suite/ndb/r/ndb_statistics1.result
sql/ha_ndbcluster.cc
sql/handler.cc
sql/handler.h
sql/opt_explain.cc
sql/opt_range.cc
=== modified file 'mysql-test/suite/ndb/r/ndb_condition_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2011-09-07 12:58:43 +0000
+++ b/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2011-11-10 13:08:24 +0000
@@ -1738,7 +1738,7 @@ pk1 attr1 attr2 attr3 pk1 attr1 attr2 at
explain
select * from t4 where attr1 < 5 and attr2 > 9223372036854775803 and attr3 != 3 order by t4.pk1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range attr1 attr1 4 NULL # Using where with pushed condition; Using filesort
+1 SIMPLE t4 range attr1 attr1 4 NULL # Using where with pushed condition; Using MRR; Using filesort
select * from t4 where attr1 < 5 and attr2 > 9223372036854775803 and attr3 != 3 order by t4.pk1;
pk1 attr1 attr2 attr3 attr4
2 2 9223372036854775804 2 c
@@ -1746,7 +1746,7 @@ pk1 attr1 attr2 attr3 attr4
explain
select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range attr1 attr1 4 NULL # Using where with pushed condition; Using temporary; Using filesort
+1 SIMPLE t4 range attr1 attr1 4 NULL # Using where with pushed condition; Using MRR; Using temporary; Using filesort
1 SIMPLE t3 ALL NULL NULL NULL NULL # Using where; Using join buffer (Block Nested Loop)
select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1;
pk1 attr1 attr2 attr3 attr4 pk1 attr1 attr2 attr3 attr4
@@ -1910,7 +1910,7 @@ insert into NodeAlias VALUES(null, 8 , '
12:22:26');
explain select * from NodeAlias where (aliasKey LIKE '491803%');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NodeAlias range NodeAlias_KeyIndex NodeAlias_KeyIndex 48 NULL 3 Using where with pushed condition
+1 SIMPLE NodeAlias range NodeAlias_KeyIndex NodeAlias_KeyIndex 48 NULL 3 Using where with pushed condition; Using MRR
select * from NodeAlias where (aliasKey LIKE '491803%') order by id;
id nodeId displayName aliasKey objectVersion changed
7 8 491803% 491803% 0 2008-03-10 12:22:26
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-10-27 12:19:57 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-11-10 13:08:24 +0000
@@ -930,7 +930,7 @@ from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a > 2 and t1.d = 3;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`d` = 3) and (`test`.`t1`.`a` > 2))
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`d` = 3) and (`test`.`t1`.`a` > 2)); Using MRR
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.c,const 1 100.00 Child of 't1' in pushed join@1
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`c`) and (`test`.`t1`.`d` = 3) and (`test`.`t2`.`b` = 3) and (`test`.`t1`.`a` > 2))
@@ -1582,7 +1582,7 @@ select * from t1 x, t1 y where
x.a <= 2 and
y.a=x.d and y.b=x.b;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE x range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`x`.`a` <= 2)
+1 SIMPLE x range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`x`.`a` <= 2); Using MRR
1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d,test.x.b 1 100.00 Child of 'x' in pushed join@1
Warnings:
Note 1003 /* select#1 */ select `test`.`x`.`a` AS `a`,`test`.`x`.`b` AS `b`,`test`.`x`.`c` AS `c`,`test`.`x`.`d` AS `d`,`test`.`y`.`a` AS `a`,`test`.`y`.`b` AS `b`,`test`.`y`.`c` AS `c`,`test`.`y`.`d` AS `d` from `test`.`t1` `x` join `test`.`t1` `y` where ((`test`.`y`.`b` = `test`.`x`.`b`) and (`test`.`y`.`a` = `test`.`x`.`d`) and (`test`.`x`.`a` <= 2))
@@ -1602,7 +1602,7 @@ select * from t1 x, t1 y where
(x.a <= 2 or x.a > 3) and
y.a=x.d and y.b=x.b;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE x range PRIMARY PRIMARY 4 NULL 6 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`x`.`a` <= 2) or (`test`.`x`.`a` > 3))
+1 SIMPLE x range PRIMARY PRIMARY 4 NULL 6 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`x`.`a` <= 2) or (`test`.`x`.`a` > 3)); Using MRR
1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d,test.x.b 1 100.00 Child of 'x' in pushed join@1
Warnings:
Note 1003 /* select#1 */ select `test`.`x`.`a` AS `a`,`test`.`x`.`b` AS `b`,`test`.`x`.`c` AS `c`,`test`.`x`.`d` AS `d`,`test`.`y`.`a` AS `a`,`test`.`y`.`b` AS `b`,`test`.`y`.`c` AS `c`,`test`.`y`.`d` AS `d` from `test`.`t1` `x` join `test`.`t1` `y` where ((`test`.`y`.`b` = `test`.`x`.`b`) and (`test`.`y`.`a` = `test`.`x`.`d`) and ((`test`.`x`.`a` <= 2) or (`test`.`x`.`a` > 3)))
@@ -1625,7 +1625,7 @@ select * from t1 x, t1 y where
(x.a >= 2 or x.a < 3) and
y.a=x.d and y.b=x.b;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE x range PRIMARY PRIMARY 0 NULL 16 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`x`.`a` >= 2) or (`test`.`x`.`a` < 3))
+1 SIMPLE x range PRIMARY PRIMARY 0 NULL 16 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`x`.`a` >= 2) or (`test`.`x`.`a` < 3)); Using MRR
1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d,test.x.b 1 100.00 Child of 'x' in pushed join@1
Warnings:
Note 1003 /* select#1 */ select `test`.`x`.`a` AS `a`,`test`.`x`.`b` AS `b`,`test`.`x`.`c` AS `c`,`test`.`x`.`d` AS `d`,`test`.`y`.`a` AS `a`,`test`.`y`.`b` AS `b`,`test`.`y`.`c` AS `c`,`test`.`y`.`d` AS `d` from `test`.`t1` `x` join `test`.`t1` `y` where ((`test`.`y`.`b` = `test`.`x`.`b`) and (`test`.`y`.`a` = `test`.`x`.`d`) and ((`test`.`x`.`a` >= 2) or (`test`.`x`.`a` < 3)))
@@ -1652,7 +1652,7 @@ select * from t1 x, t1 y where
(x.a <= 2 or x.a in (0,5,4)) and
y.a=x.d and y.b=x.b;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE x range PRIMARY PRIMARY 4 NULL 9 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`x`.`a` <= 2) or (`test`.`x`.`a` in (0,5,4)))
+1 SIMPLE x range PRIMARY PRIMARY 4 NULL 9 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`x`.`a` <= 2) or (`test`.`x`.`a` in (0,5,4))); Using MRR
1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d,test.x.b 1 100.00 Child of 'x' in pushed join@1
Warnings:
Note 1003 /* select#1 */ select `test`.`x`.`a` AS `a`,`test`.`x`.`b` AS `b`,`test`.`x`.`c` AS `c`,`test`.`x`.`d` AS `d`,`test`.`y`.`a` AS `a`,`test`.`y`.`b` AS `b`,`test`.`y`.`c` AS `c`,`test`.`y`.`d` AS `d` from `test`.`t1` `x` join `test`.`t1` `y` where ((`test`.`y`.`b` = `test`.`x`.`b`) and (`test`.`y`.`a` = `test`.`x`.`d`) and ((`test`.`x`.`a` <= 2) or (`test`.`x`.`a` in (0,5,4))))
@@ -2103,7 +2103,7 @@ from t1, t1 as t2
where t1.a in (1,3,5)
and t2.a = t1.b;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` in (1,3,5)) and (`test`.`t1`.`b` is not null))
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` in (1,3,5)) and (`test`.`t1`.`b` is not null)); Using MRR
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Child of 't1' in pushed join@1
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` in (1,3,5)))
@@ -2131,7 +2131,7 @@ from t1, t1 as t2
where t1.a in (1,3,5)
and t2.a = t1.b;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` in (1,3,5)) and (`test`.`t1`.`b` is not null))
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` in (1,3,5)) and (`test`.`t1`.`b` is not null)); Using MRR
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Child of 't1' in pushed join@1
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` in (1,3,5)))
@@ -2331,7 +2331,7 @@ a3 b3 c3 d3 a3 b3 c3 d3
explain extended
select * from t3 x, t3 y where x.d3>=31 and y.d3=x.b3;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE x range t3_d3 t3_d3 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`x`.`d3` >= 31)
+1 SIMPLE x range t3_d3 t3_d3 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`x`.`d3` >= 31); Using MRR
1 SIMPLE y eq_ref t3_d3 t3_d3 4 test.x.b3 1 100.00 Child of 'x' in pushed join@1
Warnings:
Note 1003 /* select#1 */ select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`d3` = `test`.`x`.`b3`) and (`test`.`x`.`d3` >= 31))
@@ -2718,7 +2718,7 @@ left outer join t3 as t2 on t2.d3 = t1.a
left outer join t3 as t3 on t3.a3 = t2.d3
where t1.d3 >= 47;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range d3 d3 5 NULL 3 100.00 Parent of 3 pushed join@1; Using where with pushed condition: (`test`.`t1`.`d3` >= 47)
+1 SIMPLE t1 range d3 d3 5 NULL 3 100.00 Parent of 3 pushed join@1; Using where with pushed condition: (`test`.`t1`.`d3` >= 47); Using MRR
1 SIMPLE t2 ref d3 d3 5 test.t1.a3 1 100.00 Child of 't1' in pushed join@1
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.d3 1 100.00 Child of 't2' in pushed join@1
Warnings:
@@ -2753,7 +2753,7 @@ left outer join t3 as t2 on t2.d3 = t1.a
left outer join t3 as t3 on t3.a3 = t2.d3
where t1.d3 is not null;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range d3 d3 5 NULL 3 100.00 Parent of 3 pushed join@1; Using where with pushed condition: (`test`.`t1`.`d3` is not null)
+1 SIMPLE t1 range d3 d3 5 NULL 3 100.00 Parent of 3 pushed join@1; Using where with pushed condition: (`test`.`t1`.`d3` is not null); Using MRR
1 SIMPLE t2 ref d3 d3 5 test.t1.a3 1 100.00 Child of 't1' in pushed join@1
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.d3 1 100.00 Child of 't2' in pushed join@1
Warnings:
@@ -4160,7 +4160,7 @@ pruned_scan_count
create index i2 on t1(a,b);
explain extended select count(*) from t1 t1, t1 t2 where t1.a = 11 and t1.b<13 and t2.a = t1.c and t2.b = t1.c;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY,i2 i2 8 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` = 11) and (`test`.`t1`.`b` < 13))
+1 SIMPLE t1 range PRIMARY,i2 i2 8 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` = 11) and (`test`.`t1`.`b` < 13)); Using MRR
1 SIMPLE t2 eq_ref PRIMARY,i2 PRIMARY 8 test.t1.c,test.t1.c 1 100.00 Child of 't1' in pushed join@1
Warnings:
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t1`.`a` = 11) and (`test`.`t1`.`b` < 13))
@@ -4171,7 +4171,7 @@ pruned_scan_count
1
explain extended select count(*) from t1 t1, t1 t2 where t1.a >= 12 and t1.a<=12 and t2.a = t1.c and t2.b = t1.c;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY,i2 i2 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` >= 12) and (`test`.`t1`.`a` <= 12))
+1 SIMPLE t1 range PRIMARY,i2 i2 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` >= 12) and (`test`.`t1`.`a` <= 12)); Using MRR
1 SIMPLE t2 eq_ref PRIMARY,i2 PRIMARY 8 test.t1.c,test.t1.c 1 100.00 Child of 't1' in pushed join@1
Warnings:
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t1`.`a` >= 12) and (`test`.`t1`.`a` <= 12))
@@ -4182,7 +4182,7 @@ pruned_scan_count
1
explain extended select count(*) from t1 t1, t1 t2 where t1.a >= 11 and t1.a<=12 and t2.a = t1.c and t2.b = t1.c;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY,i2 i2 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` >= 11) and (`test`.`t1`.`a` <= 12))
+1 SIMPLE t1 range PRIMARY,i2 i2 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` >= 11) and (`test`.`t1`.`a` <= 12)); Using MRR
1 SIMPLE t2 eq_ref PRIMARY,i2 PRIMARY 8 test.t1.c,test.t1.c 1 100.00 Child of 't1' in pushed join@1
Warnings:
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t1`.`a` >= 11) and (`test`.`t1`.`a` <= 12))
@@ -4193,7 +4193,7 @@ pruned_scan_count
0
explain extended select count(*) from t1 t1, t1 t2 where (t1.a = 10 or t1.a=12) and t1.b<13 and t2.a = t1.c and t2.b = t1.c;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY,i2 i2 8 NULL 6 50.00 Parent of 2 pushed join@1; Using where with pushed condition: (((`test`.`t1`.`a` = 10) or (`test`.`t1`.`a` = 12)) and (`test`.`t1`.`b` < 13))
+1 SIMPLE t1 range PRIMARY,i2 i2 8 NULL 6 50.00 Parent of 2 pushed join@1; Using where with pushed condition: (((`test`.`t1`.`a` = 10) or (`test`.`t1`.`a` = 12)) and (`test`.`t1`.`b` < 13)); Using MRR
1 SIMPLE t2 eq_ref PRIMARY,i2 PRIMARY 8 test.t1.c,test.t1.c 1 100.00 Child of 't1' in pushed join@1
Warnings:
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`c`) and ((`test`.`t1`.`a` = 10) or (`test`.`t1`.`a` = 12)) and (`test`.`t1`.`b` < 13))
@@ -4204,7 +4204,7 @@ pruned_scan_count
0
explain extended select count(*) from t1 t1, t1 t2 where t1.a = 10 and (t1.b<11 or t1.b>11) and t2.a = t1.c and t2.b = t1.c;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY,i2 i2 8 NULL 6 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` = 10) and ((`test`.`t1`.`b` < 11) or (`test`.`t1`.`b` > 11)))
+1 SIMPLE t1 range PRIMARY,i2 i2 8 NULL 6 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` = 10) and ((`test`.`t1`.`b` < 11) or (`test`.`t1`.`b` > 11))); Using MRR
1 SIMPLE t2 eq_ref PRIMARY,i2 PRIMARY 8 test.t1.c,test.t1.c 1 100.00 Child of 't1' in pushed join@1
Warnings:
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t1`.`a` = 10) and ((`test`.`t1`.`b` < 11) or (`test`.`t1`.`b` > 11)))
@@ -5157,7 +5157,7 @@ on table1.a = table4.pk
where table2.pk != 6;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE table1 ALL NULL NULL NULL NULL 6 100.00 Using where with pushed condition: (`test`.`table1`.`a` is not null); Using temporary
-1 SIMPLE table2 range PRIMARY PRIMARY 4 NULL 6 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`table2`.`pk` <> 6); Distinct; Using join buffer (Block Nested Loop)
+1 SIMPLE table2 range PRIMARY PRIMARY 4 NULL 6 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`table2`.`pk` <> 6); Using MRR; Distinct; Using join buffer (Block Nested Loop)
1 SIMPLE table3 eq_ref PRIMARY PRIMARY 4 test.table2.pk 1 100.00 Child of 'table2' in pushed join@1; Distinct
1 SIMPLE table4 eq_ref PRIMARY PRIMARY 4 test.table1.a 1 100.00 Using where; Distinct
Warnings:
@@ -5182,7 +5182,7 @@ insert into t values(0,0);
explain extended
select * from t as t1 join t as t2 on t2.a=t1.a where t1.a < 8 or t1.a >= 8;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 0 NULL 2 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` < 8) or (`test`.`t1`.`a` >= 8))
+1 SIMPLE t1 range PRIMARY PRIMARY 0 NULL 2 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` < 8) or (`test`.`t1`.`a` >= 8)); Using MRR
1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Child of 't1' in pushed join@1
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t` `t1` join `test`.`t` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and ((`test`.`t1`.`a` < 8) or (`test`.`t1`.`a` >= 8)))
@@ -5529,7 +5529,7 @@ counter_name spj_counts_at_end.val - spj
CONST_PRUNED_RANGE_SCANS_RECEIVED 8
LOCAL_TABLE_SCANS_SENT 250
PRUNED_RANGE_SCANS_RECEIVED 27
-RANGE_SCANS_RECEIVED 730
+RANGE_SCANS_RECEIVED 732
READS_RECEIVED 58
TABLE_SCANS_RECEIVED 250
drop table spj_counts_at_startup;
@@ -5539,11 +5539,11 @@ scan_count_derived
pruned_scan_count
8
sorted_scan_count
-35
+10
pushed_queries_defined
405
pushed_queries_dropped
-9
+8
pushed_queries_executed
-549
+550
set ndb_join_pushdown = @save_ndb_join_pushdown;
=== modified file 'mysql-test/suite/ndb/r/ndb_read_multi_range.result'
--- a/mysql-test/suite/ndb/r/ndb_read_multi_range.result 2011-10-18 10:43:35 +0000
+++ b/mysql-test/suite/ndb/r/ndb_read_multi_range.result 2011-11-10 13:08:24 +0000
@@ -608,7 +608,7 @@ SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM
t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3000 Using temporary
-1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 6 Using where; Distinct
+1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 6 Using where; Using MRR; Distinct
SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM
t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6;
set optimizer_switch='block_nested_loop=default';
=== modified file 'mysql-test/suite/ndb/r/ndb_row_count.result'
--- a/mysql-test/suite/ndb/r/ndb_row_count.result 2011-09-07 12:58:43 +0000
+++ b/mysql-test/suite/ndb/r/ndb_row_count.result 2011-11-10 13:08:24 +0000
@@ -16,7 +16,7 @@ insert into t1 values
# expected result 0 roundtrips - read stats from share
explain extended select * from t1 where a < 100;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100)
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100); Using MRR
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 100)
@ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count
@@ -26,7 +26,7 @@ insert into t1 select a+100 from t1;
# expected result 0 roundtrips - read stats from share
explain extended select * from t1 where a < 100;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 20 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100)
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 20 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100); Using MRR
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 100)
@ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count
@@ -36,7 +36,7 @@ delete from t1 where a >= 100;
# expected result 0 roundtrips - read stats from share
explain extended select * from t1 where a < 100;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100)
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100); Using MRR
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 100)
@ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count
@@ -48,7 +48,7 @@ commit;
# expected result 0 roundtrips - read stats from share
explain extended select * from t1 where a < 100;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 20 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100)
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 20 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100); Using MRR
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 100)
@ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count
@@ -60,7 +60,7 @@ commit;
# expected result 0 roundtrips - read stats from share
explain extended select * from t1 where a < 100;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100)
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100); Using MRR
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 100)
@ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count
@@ -72,7 +72,7 @@ rollback;
# expected result 0 roundtrips - read stats from share
explain extended select * from t1 where a < 100;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100)
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100); Using MRR
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 100)
@ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count
@@ -84,7 +84,7 @@ rollback;
# expected result 0 roundtrips - read stats from share
explain extended select * from t1 where a < 100;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100)
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100); Using MRR
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 100)
@ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count
@@ -95,7 +95,7 @@ ERROR 23000: Can't write; duplicate key
# expected result 0 roundtrips - read stats from share
explain extended select * from t1 where a < 100;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100)
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100); Using MRR
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 100)
@ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count
@@ -107,7 +107,7 @@ ERROR 23000: Can't write; duplicate key
# expected result 0 roundtrips - read stats from share
explain extended select * from t1 where a < 100;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100)
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100); Using MRR
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 100)
@ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count
=== modified file 'mysql-test/suite/ndb/r/ndb_statistics0.result'
--- a/mysql-test/suite/ndb/r/ndb_statistics0.result 2011-09-07 12:58:43 +0000
+++ b/mysql-test/suite/ndb/r/ndb_statistics0.result 2011-11-10 13:08:24 +0000
@@ -57,19 +57,19 @@ id select_type table type possible_keys
EXPLAIN
SELECT * FROM t10000 WHERE k >= 42 and k < 10000;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 Using where with pushed condition
+1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 Using where with pushed condition
+1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE k < 42;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition
+1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE k > 42;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition
+1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 AS X JOIN t10000 AS Y
ON Y.I=X.I AND Y.J = X.I;
@@ -79,27 +79,27 @@ id select_type table type possible_keys
EXPLAIN
SELECT * FROM t100 WHERE k < 42;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 10 Using where with pushed condition
+1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 10 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t100 WHERE k > 42;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 10 Using where with pushed condition
+1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 10 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE k < 42;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition
+1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE k > 42;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition
+1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t100 WHERE k BETWEEN 42 AND 10000;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 5 Using where with pushed condition
+1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 5 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 Using where with pushed condition
+1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE I = 0;
id select_type table type possible_keys key key_len ref rows Extra
@@ -119,15 +119,15 @@ id select_type table type possible_keys
EXPLAIN
SELECT * FROM t10000 WHERE I = 0 AND J > 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range J,I I 10 NULL 100 Using where with pushed condition
+1 SIMPLE t10000 range J,I I 10 NULL 100 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE I = 0 AND J < 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range J,I I 10 NULL 50 Using where with pushed condition
+1 SIMPLE t10000 range J,I I 10 NULL 50 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE I = 0 AND J BETWEEN 1 AND 10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range J,I I 10 NULL 50 Using where with pushed condition
+1 SIMPLE t10000 range J,I I 10 NULL 50 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE I = 0 AND J = 1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -139,15 +139,15 @@ id select_type table type possible_keys
EXPLAIN
SELECT * FROM t10000 WHERE J = 0 AND K > 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range PRIMARY,J J 9 NULL 83 Using where with pushed condition
+1 SIMPLE t10000 range PRIMARY,J J 9 NULL 83 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE J = 0 AND K < 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range PRIMARY,J J 9 NULL 83 Using where with pushed condition
+1 SIMPLE t10000 range PRIMARY,J J 9 NULL 83 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE J = 0 AND K BETWEEN 1 AND 10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range PRIMARY,J J 9 NULL 41 Using where with pushed condition
+1 SIMPLE t10000 range PRIMARY,J J 9 NULL 41 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE J = 0 AND K = 1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -155,7 +155,7 @@ id select_type table type possible_keys
EXPLAIN
SELECT * FROM t10000 WHERE I = 0 AND J <> 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range J,I I 10 NULL 150 Using where with pushed condition
+1 SIMPLE t10000 range J,I I 10 NULL 150 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE I <> 0 AND J = 1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -163,11 +163,11 @@ id select_type table type possible_keys
EXPLAIN
SELECT * FROM t10000 WHERE I <> 0 AND J <> 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range J,I J 5 NULL 1500 Using where with pushed condition
+1 SIMPLE t10000 range J,I J 5 NULL 1500 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE J <> 1 AND I = 0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range J,I I 10 NULL 150 Using where with pushed condition
+1 SIMPLE t10000 range J,I I 10 NULL 150 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE J = 1 AND I <> 0;
id select_type table type possible_keys key key_len ref rows Extra
@@ -175,7 +175,7 @@ id select_type table type possible_keys
EXPLAIN
SELECT * FROM t10000 WHERE J <> 1 AND I <> 0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range J,I J 5 NULL 1500 Using where with pushed condition
+1 SIMPLE t10000 range J,I J 5 NULL 1500 Using where with pushed condition; Using MRR
DROP TABLE t10,t100,t10000;
End of 5.1 tests
set @is_enable = @is_enable_default;
=== modified file 'mysql-test/suite/ndb/r/ndb_statistics1.result'
--- a/mysql-test/suite/ndb/r/ndb_statistics1.result 2011-09-07 12:58:43 +0000
+++ b/mysql-test/suite/ndb/r/ndb_statistics1.result 2011-11-10 13:08:24 +0000
@@ -59,19 +59,19 @@ id select_type table type possible_keys
EXPLAIN
SELECT * FROM t10000 WHERE k >= 42 and k < 10000;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9958 Using where with pushed condition
+1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9958 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9959 Using where with pushed condition
+1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9959 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE k < 42;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 41 Using where with pushed condition
+1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 41 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE k > 42;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9958 Using where with pushed condition
+1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9958 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 AS X JOIN t10000 AS Y
ON Y.I=X.I AND Y.J = X.I;
@@ -81,27 +81,27 @@ id select_type table type possible_keys
EXPLAIN
SELECT * FROM t100 WHERE k < 42;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 41 Using where with pushed condition
+1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 41 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t100 WHERE k > 42;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 58 Using where with pushed condition
+1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 58 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE k < 42;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 41 Using where with pushed condition
+1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 41 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE k > 42;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9958 Using where with pushed condition
+1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9958 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t100 WHERE k BETWEEN 42 AND 10000;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 59 Using where with pushed condition
+1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 59 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9959 Using where with pushed condition
+1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9959 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE I = 0;
id select_type table type possible_keys key key_len ref rows Extra
@@ -121,15 +121,15 @@ id select_type table type possible_keys
EXPLAIN
SELECT * FROM t10000 WHERE I = 0 AND J > 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range J,I I 10 NULL 99 Using where with pushed condition
+1 SIMPLE t10000 range J,I I 10 NULL 99 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE I = 0 AND J < 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range J,I J 5 NULL 2 Using where with pushed condition
+1 SIMPLE t10000 range J,I J 5 NULL 2 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE I = 0 AND J BETWEEN 1 AND 10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range J,I I 10 NULL 2 Using where with pushed condition
+1 SIMPLE t10000 range J,I I 10 NULL 2 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE I = 0 AND J = 1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -141,7 +141,7 @@ id select_type table type possible_keys
EXPLAIN
SELECT * FROM t10000 WHERE J = 0 AND K > 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range PRIMARY,J J 9 NULL 2 Using where with pushed condition
+1 SIMPLE t10000 range PRIMARY,J J 9 NULL 2 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE J = 0 AND K < 1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -149,7 +149,7 @@ id select_type table type possible_keys
EXPLAIN
SELECT * FROM t10000 WHERE J = 0 AND K BETWEEN 1 AND 10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range PRIMARY,J J 9 NULL 2 Using where with pushed condition
+1 SIMPLE t10000 range PRIMARY,J J 9 NULL 2 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE J = 0 AND K = 1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -157,7 +157,7 @@ id select_type table type possible_keys
EXPLAIN
SELECT * FROM t10000 WHERE I = 0 AND J <> 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range J,I I 10 NULL 101 Using where with pushed condition
+1 SIMPLE t10000 range J,I I 10 NULL 101 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE I <> 0 AND J = 1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -165,11 +165,11 @@ id select_type table type possible_keys
EXPLAIN
SELECT * FROM t10000 WHERE I <> 0 AND J <> 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range J,I I 5 NULL 9902 Using where with pushed condition
+1 SIMPLE t10000 range J,I I 5 NULL 9902 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE J <> 1 AND I = 0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range J,I I 10 NULL 101 Using where with pushed condition
+1 SIMPLE t10000 range J,I I 10 NULL 101 Using where with pushed condition; Using MRR
EXPLAIN
SELECT * FROM t10000 WHERE J = 1 AND I <> 0;
id select_type table type possible_keys key key_len ref rows Extra
@@ -177,7 +177,7 @@ id select_type table type possible_keys
EXPLAIN
SELECT * FROM t10000 WHERE J <> 1 AND I <> 0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t10000 range J,I I 5 NULL 9902 Using where with pushed condition
+1 SIMPLE t10000 range J,I I 5 NULL 9902 Using where with pushed condition; Using MRR
DROP TABLE t10,t100,t10000;
End of 5.1 tests
set @is_enable = @is_enable_default;
=== modified file 'sql/ha_ndbcluster.cc'
--- a/sql/ha_ndbcluster.cc 2011-11-09 19:34:25 +0000
+++ b/sql/ha_ndbcluster.cc 2011-11-10 13:08:24 +0000
@@ -14501,6 +14501,7 @@ ha_ndbcluster::multi_range_read_info_con
{
/* Use default MRR implementation */
*flags|= HA_MRR_USE_DEFAULT_IMPL;
+ *flags|= HA_MRR_SUPPORT_SORTED;
*bufsz= 0;
}
else
@@ -14527,17 +14528,20 @@ ha_ndbcluster::multi_range_read_info_con
{
/* Too small buffer limit to do MRR. */
*flags|= HA_MRR_USE_DEFAULT_IMPL;
+ *flags|= HA_MRR_SUPPORT_SORTED;
*bufsz= 0;
}
else
{
*flags&= ~HA_MRR_USE_DEFAULT_IMPL;
+ *flags|= HA_MRR_SUPPORT_SORTED;
*bufsz= min_total_size;
}
}
else
{
*flags&= ~HA_MRR_USE_DEFAULT_IMPL;
+ *flags|= HA_MRR_SUPPORT_SORTED;
*bufsz= min(save_bufsize, total_bufsize);
}
}
@@ -14579,6 +14583,7 @@ ha_ndbcluster::multi_range_read_info(uin
!(*flags & HA_MRR_NO_NULL_ENDPOINTS)))
{
*flags|= HA_MRR_USE_DEFAULT_IMPL;
+ *flags|= HA_MRR_SUPPORT_SORTED;
*bufsz= 0;
}
else
@@ -14595,17 +14600,20 @@ ha_ndbcluster::multi_range_read_info(uin
{
/* Too small buffer limit to do MRR. */
*flags|= HA_MRR_USE_DEFAULT_IMPL;
+ *flags|= HA_MRR_SUPPORT_SORTED;
*bufsz= 0;
}
else
{
*flags&= ~HA_MRR_USE_DEFAULT_IMPL;
+ *flags|= HA_MRR_SUPPORT_SORTED;
*bufsz= min_total_size;
}
}
else
{
*flags&= ~HA_MRR_USE_DEFAULT_IMPL;
+ *flags|= HA_MRR_SUPPORT_SORTED;
*bufsz= min(save_bufsize,
keys * entry_size + multi_range_fixed_size(n_ranges));
}
=== modified file 'sql/handler.cc'
--- a/sql/handler.cc 2011-10-18 10:43:35 +0000
+++ b/sql/handler.cc 2011-11-10 13:08:24 +0000
@@ -5277,6 +5277,10 @@ handler::multi_range_read_info_const(uin
/* Default MRR implementation doesn't need buffer */
*bufsz= 0;
+#ifndef MCP_BUG13330645
+ /* Assume NO_NULL_ENDPOINTS until we find one (below) */
+ *flags|= HA_MRR_NO_NULL_ENDPOINTS;
+#endif
seq_it= seq->init(seq_init_param, n_ranges, *flags);
while (!seq->next(seq_it, &range))
{
@@ -5310,12 +5314,19 @@ handler::multi_range_read_info_const(uin
}
}
total_rows += rows;
+#ifndef MCP_BUG13330645
+ if (range.range_flag & NULL_RANGE)
+ *flags&= ~HA_MRR_NO_NULL_ENDPOINTS;
+#endif
}
-
+
if (total_rows != HA_POS_ERROR)
{
/* The following calculation is the same as in multi_range_read_info(): */
*flags |= HA_MRR_USE_DEFAULT_IMPL;
+#ifndef MCP_BUG13330645
+ *flags|= HA_MRR_SUPPORT_SORTED;
+#endif
cost->zero();
cost->avg_io_cost= 1; /* assume random seeks */
if ((*flags & HA_MRR_INDEX_ONLY) && total_rows > 2)
@@ -5368,6 +5379,9 @@ ha_rows handler::multi_range_read_info(u
*bufsz= 0; /* Default implementation doesn't need a buffer */
*flags |= HA_MRR_USE_DEFAULT_IMPL;
+#ifndef MCP_BUG13330645
+ *flags|= HA_MRR_SUPPORT_SORTED;
+#endif
cost->zero();
cost->avg_io_cost= 1; /* assume random seeks */
@@ -5530,13 +5544,19 @@ int DsMrr_impl::dsmrr_init(handler *h_ar
handler *new_h2= 0;
int retval= 0;
DBUG_ENTER("DsMrr_impl::dsmrr_init");
+ THD *thd= current_thd;
/*
index_merge may invoke a scan on an object for which dsmrr_info[_const]
has not been called, so set the owner handler here as well.
*/
h= h_arg;
+#ifdef MCP_BUG13330645
if (mode & HA_MRR_USE_DEFAULT_IMPL || mode & HA_MRR_SORTED)
+#else
+ if (!thd->optimizer_switch_flag(OPTIMIZER_SWITCH_MRR) ||
+ mode & (HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED)) // DS-MRR doesn't sort
+#endif
{
use_default_impl= TRUE;
retval= h->handler::multi_range_read_init(seq_funcs, seq_init_param,
@@ -5586,7 +5606,6 @@ int DsMrr_impl::dsmrr_init(handler *h_ar
if (!h2)
{
/* Create a separate handler object to do rndpos() calls. */
- THD *thd= current_thd;
/*
::clone() takes up a lot of stack, especially on 64 bit platforms.
The constant 5 is an empiric result.
@@ -5860,12 +5879,17 @@ ha_rows DsMrr_impl::dsmrr_info(uint keyn
DBUG_ASSERT(!res);
if ((*flags & HA_MRR_USE_DEFAULT_IMPL) ||
+#ifdef MCP_BUG13330645
choose_mrr_impl(keyno, rows, &def_flags, &def_bufsz, cost))
+#else
+ choose_mrr_impl(keyno, rows, flags, bufsz, cost))
+#endif
{
/* Default implementation is choosen */
DBUG_PRINT("info", ("Default MRR implementation choosen"));
*flags= def_flags;
*bufsz= def_bufsz;
+ DBUG_ASSERT(*flags & HA_MRR_USE_DEFAULT_IMPL);
}
else
{
@@ -5949,12 +5973,18 @@ bool DsMrr_impl::choose_mrr_impl(uint ke
bool res;
THD *thd= current_thd;
if (!thd->optimizer_switch_flag(OPTIMIZER_SWITCH_MRR) ||
+#ifdef MCP_BUG13330645
*flags & HA_MRR_INDEX_ONLY ||
+#else
+ *flags & (HA_MRR_INDEX_ONLY | HA_MRR_SORTED) || // Unsupported by DS-MRR
+#endif
(keyno == table->s->primary_key && h->primary_key_is_clustered()) ||
key_uses_partial_cols(table, keyno))
{
- /* Use the default implementation */
+ /* Use the default implementation, don't modify args: See comments */
+#ifdef MCP_BUG13330645
*flags |= HA_MRR_USE_DEFAULT_IMPL;
+#endif
return TRUE;
}
@@ -5980,7 +6010,11 @@ bool DsMrr_impl::choose_mrr_impl(uint ke
if (force_dsmrr || dsmrr_cost.total_cost() <= cost->total_cost())
{
*flags &= ~HA_MRR_USE_DEFAULT_IMPL; /* Use the DS-MRR implementation */
+#ifdef MCP_BUG13330645
*flags &= ~HA_MRR_SORTED; /* We will return unordered output */
+#else
+ *flags &= ~HA_MRR_SUPPORT_SORTED; /* We can't provide ordered output */
+#endif
*cost= dsmrr_cost;
res= FALSE;
}
=== modified file 'sql/handler.h'
--- a/sql/handler.h 2011-10-05 07:24:39 +0000
+++ b/sql/handler.h 2011-11-10 13:08:24 +0000
@@ -1268,6 +1268,11 @@ void get_sweep_read_cost(TABLE *table, h
/*
The MRR user will provide ranges in key order, and MRR implementation
must return rows in key order.
+
+ Passing this flag to multi_read_range_init() may cause the
+ default MRR handler to be used even if HA_MRR_USE_DEFAULT_IMPL
+ was not specified.
+ (If the native MRR impl. can not provide SORTED result)
*/
#define HA_MRR_SORTED 8
@@ -1296,6 +1301,16 @@ void get_sweep_read_cost(TABLE *table, h
*/
#define HA_MRR_NO_NULL_ENDPOINTS 128
+#ifndef MCP_BUG13330645
+/*
+ Set by the MRR implementation to signal that it will natively
+ produced sorted result if multi_range_read_init() is called with
+ the HA_MRR_SORTED flag - Else multi_range_read_init(HA_MRR_SORTED)
+ will revert to use the default MRR implementation.
+*/
+#define HA_MRR_SUPPORT_SORTED 256
+#endif
+
class ha_statistics
{
public:
=== modified file 'sql/opt_explain.cc'
--- a/sql/opt_explain.cc 2011-09-06 12:43:05 +0000
+++ b/sql/opt_explain.cc 2011-11-10 13:08:24 +0000
@@ -798,6 +798,7 @@ void Explain_table_base::explain_extra_c
if (table->reginfo.not_exists_optimize)
str_extra->append(STRING_WITH_LEN("; Not exists"));
+#ifdef MCP_BUG13330645
if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE &&
!(((QUICK_RANGE_SELECT*)(select->quick))->mrr_flags &
(HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED)))
@@ -817,6 +818,28 @@ void Explain_table_base::explain_extra_c
*/
str_extra->append(STRING_WITH_LEN("; Using MRR"));
}
+#else
+ if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE)
+ {
+ uint mrr_flags=
+ ((QUICK_RANGE_SELECT*)(select->quick))->mrr_flags;
+
+ /*
+ During normal execution of a query, multi_range_read_init() is
+ called to initialize MRR. If HA_MRR_SORTED is set at this point,
+ multi_range_read_init() for any native MRR implementation will
+ revert to default MRR if not HA_MRR_SUPPORT_SORTED.
+ Calling multi_range_read_init() can potentially be costly, so it
+ is not done when executing an EXPLAIN. We therefore simulate
+ its effect here:
+ */
+ if (mrr_flags & HA_MRR_SORTED && !(mrr_flags & HA_MRR_SUPPORT_SORTED))
+ mrr_flags|= HA_MRR_USE_DEFAULT_IMPL;
+
+ if (!(mrr_flags & HA_MRR_USE_DEFAULT_IMPL))
+ str_extra->append(STRING_WITH_LEN("; Using MRR"));
+ }
+#endif
}
void Explain_table_base::explain_tmptable_and_filesort(bool need_tmp_table_arg,
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2011-09-27 12:11:16 +0000
+++ b/sql/opt_range.cc 2011-11-10 13:08:24 +0000
@@ -2565,7 +2565,11 @@ int SQL_SELECT::test_quick_select(THD *t
param.imerge_cost_buff_size= 0;
param.using_real_indexes= TRUE;
param.remove_jump_scans= TRUE;
+#ifdef MCP_BUG13330645
param.force_default_mrr= (interesting_order != ORDER::ORDER_NOT_RELEVANT);
+#else
+ param.force_default_mrr= (interesting_order == ORDER::ORDER_DESC);
+#endif
param.order_direction= interesting_order;
thd->no_errors=1; // Don't warn about NULL
@@ -5559,7 +5563,11 @@ QUICK_SELECT_I *TRP_ROR_INTERSECT::make_
{
if (!(quick= get_quick_select(param, (*current)->idx,
(*current)->sel_arg,
+#ifdef MCP_BUG13330645
HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED,
+#else
+ HA_MRR_SORTED,
+#endif
0, alloc)) ||
quick_intrsect->push_quick_back(quick))
{
@@ -5571,7 +5579,11 @@ QUICK_SELECT_I *TRP_ROR_INTERSECT::make_
{
if (!(quick= get_quick_select(param, cpk_scan->idx,
cpk_scan->sel_arg,
+#ifdef MCP_BUG13330645
HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED,
+#else
+ HA_MRR_SORTED,
+#endif
0, alloc)))
{
delete quick_intrsect;
@@ -8675,10 +8687,19 @@ ha_rows check_quick_select(PARAM *param,
param->is_ror_scan= FALSE;
*mrr_flags= param->force_default_mrr? HA_MRR_USE_DEFAULT_IMPL: 0;
+#ifdef MCP_BUG13330645
/*
Pass HA_MRR_SORTED to see if MRR implementation can handle sorting.
*/
*mrr_flags|= HA_MRR_NO_ASSOCIATION | HA_MRR_SORTED;
+#else
+ *mrr_flags|= HA_MRR_NO_ASSOCIATION;
+ /*
+ Pass HA_MRR_SORTED to see if MRR implementation can handle sorting.
+ */
+ if (param->order_direction != ORDER::ORDER_NOT_RELEVANT)
+ *mrr_flags|= HA_MRR_SORTED;
+#endif
bool pk_is_clustered= file->primary_key_is_clustered();
if (index_only &&
@@ -8687,7 +8708,11 @@ ha_rows check_quick_select(PARAM *param,
*mrr_flags |= HA_MRR_INDEX_ONLY;
if (current_thd->lex->sql_command != SQLCOM_SELECT)
+#ifdef MCP_BUG13330645
*mrr_flags |= HA_MRR_USE_DEFAULT_IMPL;
+#else
+ *mrr_flags|= HA_MRR_SORTED; // Assumed to give faster ins/upd/del
+#endif
*bufsize= param->thd->variables.read_rnd_buff_size;
// Sets is_ror_scan to false for some queries, e.g. multi-ranges
@@ -9212,7 +9237,11 @@ QUICK_RANGE_SELECT *get_quick_select_for
quick->mrr_flags= HA_MRR_NO_ASSOCIATION |
(table->key_read ? HA_MRR_INDEX_ONLY : 0);
if (thd->lex->sql_command != SQLCOM_SELECT)
+#ifdef MCP_BUG13330645
quick->mrr_flags |= HA_MRR_USE_DEFAULT_IMPL;
+#else
+ quick->mrr_flags|= HA_MRR_SORTED; // Assumed to give faster ins/upd/del
+#endif
#ifdef WITH_NDBCLUSTER_STORAGE_ENGINE
if (!ref->null_ref_key && !key_has_nulls(key_info, range->min_key,
ref->key_length))
@@ -10887,7 +10916,11 @@ get_best_group_min_max(PARAM *param, SEL
&cur_param_idx);
/* Check if this range tree can be used for prefix retrieval. */
COST_VECT dummy_cost;
+#ifdef MCP_BUG13330645
uint mrr_flags= HA_MRR_USE_DEFAULT_IMPL;
+#else
+ uint mrr_flags= HA_MRR_SORTED;
+#endif
uint mrr_bufsize=0;
cur_quick_prefix_records= check_quick_select(param, cur_param_idx,
FALSE /*don't care*/,
@@ -11515,7 +11548,9 @@ TRP_GROUP_MIN_MAX::make_quick(PARAM *par
/* Make a QUICK_RANGE_SELECT to be used for group prefix retrieval. */
quick->quick_prefix_select= get_quick_select(param, param_idx,
index_tree,
+#ifdef MCP_BUG13330645
HA_MRR_USE_DEFAULT_IMPL |
+#endif
HA_MRR_SORTED,
0,
&quick->alloc);
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk-cluster branch (ole.john.aske:3405) Bug#13330645 | Ole John Aske | 11 Nov |
