summaryrefslogtreecommitdiff
path: root/mysql-test/r/subselect3_jcl6.result
diff options
context:
space:
mode:
authorSergey Petrunya <psergey@askmonty.org>2011-04-02 14:04:45 +0400
committerSergey Petrunya <psergey@askmonty.org>2011-04-02 14:04:45 +0400
commit997445bc8eb578355b41abc3f4e42f579f900043 (patch)
tree1224e5382ffa0b158db9d4d109114ba28b6c361d /mysql-test/r/subselect3_jcl6.result
parent886d84d6d15f17d91a37453875b386167a9fef76 (diff)
downloadmariadb-git-997445bc8eb578355b41abc3f4e42f579f900043.tar.gz
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.
Diffstat (limited to 'mysql-test/r/subselect3_jcl6.result')
-rw-r--r--mysql-test/r/subselect3_jcl6.result18
1 files changed, 9 insertions, 9 deletions
diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result
index a3ed949cda2..a41d0dfe4b9 100644
--- a/mysql-test/r/subselect3_jcl6.result
+++ b/mysql-test/r/subselect3_jcl6.result
@@ -167,7 +167,7 @@ from t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 100.00 Using where; Full scan on NULL key
-2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where; Using join buffer (flat, BKA join)
+2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
Warnings:
Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`))))) AS `Z` from `test`.`t3`
@@ -195,7 +195,7 @@ from t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 DEPENDENT SUBQUERY t1 ref a a 4 func 2 100.00 Using where; Full scan on NULL key
-2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where; Using join buffer (flat, BKA join)
+2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
Warnings:
Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`)))))) AS `Z` from `test`.`t3`
@@ -739,7 +739,7 @@ WHERE t3.name='xxx' AND t2.id=t3.id);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where
2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Using index; Full scan on NULL key
-2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 Using index condition(BKA); Using where; Full scan on NULL key; Using join buffer (flat, BKA join)
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 Using index condition(BKA); Using where; Full scan on NULL key; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT * FROM t1
WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3
WHERE t3.name='xxx' AND t2.id=t3.id);
@@ -1142,7 +1142,7 @@ and t4.pk=t1.c);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 100
1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1
-2 SUBQUERY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR
+2 SUBQUERY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Rowid-ordered scan
2 SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index
drop table t1, t3, t4;
create table t1 (a int) as select * from t0 where a < 5;
@@ -1175,7 +1175,7 @@ insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B;
explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2
-1 PRIMARY t3 ref a a 5 test.t2.a 1 Using join buffer (flat, BKA join)
+1 PRIMARY t3 ref a a 5 test.t2.a 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
select * from t3 where a in (select a from t2);
a filler
@@ -1224,7 +1224,7 @@ explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where
1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10
-1 PRIMARY t3 ref a a 5 test.t2.a 10 Using join buffer (flat, BKA join)
+1 PRIMARY t3 ref a a 5 test.t2.a 10 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where
explain select straight_join * from t1 A, t1 B where A.a in (select a from t2);
id select_type table type possible_keys key key_len ref rows Extra
@@ -1254,14 +1254,14 @@ explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t0 system NULL NULL NULL NULL 1
1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10
-1 PRIMARY t3 ref a a 5 test.t2.a 10 Using join buffer (flat, BKA join)
+1 PRIMARY t3 ref a a 5 test.t2.a 10 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where
create table t4 as select a as x, a as y from t1;
explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t0 system NULL NULL NULL NULL 1
1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10
-1 PRIMARY t3 ref a a 5 test.t4.x 10 Using where; Using join buffer (flat, BKA join)
+1 PRIMARY t3 ref a a 5 test.t4.x 10 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
2 SUBQUERY t4 ALL NULL NULL NULL NULL 10 Using where
drop table t0,t1,t2,t3,t4;
create table t0 (a int);
@@ -1412,7 +1412,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 1.00
1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer (flat, BNL join)
2 SUBQUERY cona ALL NULL NULL NULL NULL 2 100.00 Using where
-2 SUBQUERY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 Using join buffer (flat, BKA join)
+2 SUBQUERY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
Warnings:
Note 1003 select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where ((`test`.`c`.`idContact` = `test`.`cona`.`idContact`) and (`test`.`a`.`idIndividual` = `test`.`c`.`idObj`) and (`test`.`cona`.`postalStripped` = 'T2H3B2'))
drop table t1,t2,t3;