Make EXPLAIN better at displaying MRR/BKA:

- "Using MRR" is no longer shown with range access.
- Instead, both range and BKA accesses will show one of the following:
  = "Rowid-ordered scan"
  = "Key-ordered scan"
  = "Key-ordered Rowid-ordered scan"
depending on whether DS-MRR implementation will do scan keys in order, rowids in order,
or both.
- The patch also introduces a way for other storage engines/MRR implementations to
  pass information to EXPLAIN output about the properties of employed MRR scans.

1 files changed, 39 insertions, 39 deletions

diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index 55da7572a77..caa0fcb5cf6 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -611,15 +611,15 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	period	NULL	NULL	NULL	41810	Using temporary; Using filesort
-1	SIMPLE	t3	ref	period	period	4	test.t1.period	4181	Using join buffer (flat, BKA join)
+1	SIMPLE	t3	ref	period	period	4	test.t1.period	4181	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	ALL	period	NULL	NULL	NULL	41810	Using temporary; Using filesort
-1	SIMPLE	t1	ref	period	period	4	test.t3.period	4181	Using join buffer (flat, BKA join)
+1	SIMPLE	t1	ref	period	period	4	test.t3.period	4181	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	period	NULL	NULL	NULL	41810	Using temporary; Using filesort
-1	SIMPLE	t3	ref	period	period	4	test.t1.period	4181	Using join buffer (flat, BKA join)
+1	SIMPLE	t3	ref	period	period	4	test.t1.period	4181	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 select period from t1;
 period
 9410
@@ -1366,7 +1366,7 @@ count(*)
 explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1200	
-1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using where; Not exists; Using join buffer (flat, BKA join)
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using where; Not exists; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	
@@ -1386,15 +1386,15 @@ delete from t2 where fld1=999999;
 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
-1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using join buffer (flat, BKA join)
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
-1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using join buffer (flat, BKA join)
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
-1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using join buffer (flat, BKA join)
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
@@ -3424,7 +3424,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
 FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	
-1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using MRR
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Rowid-ordered scan
 DROP TABLE t1,t2;
 CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
 INSERT t1 SET i = 0;
@@ -3460,8 +3460,8 @@ In next EXPLAIN, B.rows must be exactly 10:
 explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5
 and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	A	range	PRIMARY	PRIMARY	12	NULL	4	Using index condition; Using where; Using MRR
-1	SIMPLE	B	ref	PRIMARY	PRIMARY	8	const,test.A.e	10	Using join buffer (flat, BKA join)
+1	SIMPLE	A	range	PRIMARY	PRIMARY	12	NULL	4	Using index condition; Using where; Rowid-ordered scan
+1	SIMPLE	B	ref	PRIMARY	PRIMARY	8	const,test.A.e	10	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 drop table t1, t2;
 CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b));
 INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2),
@@ -3474,13 +3474,13 @@ INSERT INTO t2 VALUES
 EXPLAIN
 SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using index condition; Using MRR
-1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	Using join buffer (flat, BKA join)
+1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using index condition; Rowid-ordered scan
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 EXPLAIN
 SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using index condition; Using where; Using MRR
-1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	Using join buffer (flat, BKA join)
+1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using index condition; Using where; Rowid-ordered scan
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 DROP TABLE t1, t2;
 create table t1 (
 a int unsigned    not null auto_increment primary key,
@@ -3569,20 +3569,20 @@ EXPLAIN SELECT t2.*
 FROM t1 JOIN t2 ON t2.fk=t1.pk
 WHERE t2.fk < 'c' AND t2.pk=t1.fk;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	PRIMARY	PRIMARY	12	NULL	3	Using index condition; Using where; Using MRR
-1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	18	test.t1.fk	1	Using where; Using join buffer (flat, BKA join)
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	12	NULL	3	Using index condition; Using where; Rowid-ordered scan
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	18	test.t1.fk	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 EXPLAIN SELECT t2.* 
 FROM t1 JOIN t2 ON t2.fk=t1.pk 
 WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	PRIMARY	PRIMARY	12	NULL	2	Using index condition; Using where; Using MRR
-1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	18	test.t1.fk	1	Using where; Using join buffer (flat, BKA join)
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	12	NULL	2	Using index condition; Using where; Rowid-ordered scan
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	18	test.t1.fk	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 EXPLAIN SELECT t2.* 
 FROM t1 JOIN t2 ON t2.fk=t1.pk 
 WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	PRIMARY	PRIMARY	12	NULL	2	Using index condition; Using where; Using MRR
-1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	18	test.t1.fk	1	Using where; Using join buffer (flat, BKA join)
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	12	NULL	2	Using index condition; Using where; Rowid-ordered scan
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	18	test.t1.fk	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 DROP TABLE t1,t2;
 CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));
 CREATE TABLE t2 (a int, b varchar(20) NOT NULL,
@@ -3615,32 +3615,32 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
 t3.a=t2.a AND t3.c IN ('bb','ee');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
-1	SIMPLE	t2	range	si	si	5	NULL	4	Using index condition; Using where; Using MRR
-1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where; Using join buffer (flat, BKA join)
+1	SIMPLE	t2	range	si	si	5	NULL	4	Using index condition; Using where; Rowid-ordered scan
+1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 EXPLAIN
 SELECT t3.a FROM t1,t2,t3
 WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
 t3.a=t2.a AND t3.c IN ('bb','ee') ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
-1	SIMPLE	t2	range	si,ai	si	5	NULL	4	Using index condition; Using where; Using MRR
-1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where; Using join buffer (flat, BKA join)
+1	SIMPLE	t2	range	si,ai	si	5	NULL	4	Using index condition; Using where; Rowid-ordered scan
+1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 EXPLAIN 
 SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
 t3.c IN ('bb','ee');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
-1	SIMPLE	t2	range	si	si	5	NULL	2	Using index condition; Using where; Using MRR
-1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where; Using join buffer (flat, BKA join)
+1	SIMPLE	t2	range	si	si	5	NULL	2	Using index condition; Using where; Rowid-ordered scan
+1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 EXPLAIN 
 SELECT t3.a FROM t1,t2,t3
 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
 t3.c IN ('bb','ee');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
-1	SIMPLE	t2	range	si,ai	si	5	NULL	2	Using index condition; Using where; Using MRR
-1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where; Using join buffer (flat, BKA join)
+1	SIMPLE	t2	range	si,ai	si	5	NULL	2	Using index condition; Using where; Rowid-ordered scan
+1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
 CREATE TABLE t2 ( f11 int PRIMARY KEY );
@@ -3759,7 +3759,7 @@ AND t1.ts BETWEEN t2.dt1 AND t2.dt2
 AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	
-1	SIMPLE	t1	range	ts	ts	4	NULL	1	Using index condition; Using where; Using MRR
+1	SIMPLE	t1	range	ts	ts	4	NULL	1	Using index condition; Using where; Rowid-ordered scan
 Warnings:
 Warning	1292	Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
 SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
@@ -4383,12 +4383,12 @@ CREATE TABLE t1 (a INT KEY, b INT);
 INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4);
 EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND a = b LIMIT 2;
 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	Using index condition; Using where; Using MRR
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	100.00	Using index condition; Using where; Rowid-ordered scan
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` > 1)) limit 2
 EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2;
 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	Using index condition; Using where; Using MRR
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	100.00	Using index condition; Using where; Rowid-ordered scan
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` > 1)) limit 2
 DROP TABLE t1;
@@ -4827,7 +4827,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	12	
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
-1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	4	const	1	Using join buffer (incremental, BKA join)
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	4	const	1	Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (incremental, BNL join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (incremental, BNL join)
 SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6;
@@ -4907,18 +4907,18 @@ SET SESSION optimizer_switch='index_condition_pushdown=off';
 EXPLAIN SELECT * from t1,t2,t3 WHERE t3.a3=t1.a1 AND t2.a2=t1.a1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	3	
-1	SIMPLE	t2	ref	idx	idx	4	test.t1.a1	2	Using join buffer (flat, BKA join)
-1	SIMPLE	t3	ref	idx	idx	4	test.t1.a1	5	Using join buffer (incremental, BKA join)
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.a1	2	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	SIMPLE	t3	ref	idx	idx	4	test.t1.a1	5	Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 EXPLAIN SELECT * FROM t1,t2,t3 WHERE t2.a2=t1.a1 AND t3.a3=t1.a1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	3	
-1	SIMPLE	t2	ref	idx	idx	4	test.t1.a1	2	Using join buffer (flat, BKA join)
-1	SIMPLE	t3	ref	idx	idx	4	test.t1.a1	5	Using join buffer (incremental, BKA join)
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.a1	2	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	SIMPLE	t3	ref	idx	idx	4	test.t1.a1	5	Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 EXPLAIN SELECT * FROM t1,t2,t3 WHERE t2.a2=t1.a1 AND t3.a3=t2.a2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	3	
-1	SIMPLE	t2	ref	idx	idx	4	test.t1.a1	2	Using join buffer (flat, BKA join)
-1	SIMPLE	t3	ref	idx	idx	4	test.t1.a1	5	Using join buffer (incremental, BKA join)
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.a1	2	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	SIMPLE	t3	ref	idx	idx	4	test.t1.a1	5	Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 SELECT * from t1,t2,t3
 WHERE t3.a3=t1.a1 AND t2.a2=t1.a1 AND
 LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7;
@@ -5013,7 +5013,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t5	system	PRIMARY	NULL	NULL	NULL	1	
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
 1	SIMPLE	t2	ref	f22	f22	5	const	1	
-1	SIMPLE	t6	ref	f61	f61	5	const	1	Using where; Using join buffer (flat, BKA join)
+1	SIMPLE	t6	ref	f61	f61	5	const	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 1	SIMPLE	t4	ref	f42	f42	5	const	1	Using index
 1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
 SELECT t2.f23 FROM