diff options
Diffstat (limited to 'mysql-test/r/derived_opt.result')
| -rw-r--r-- | mysql-test/r/derived_opt.result | 32 |
1 files changed, 16 insertions, 16 deletions
diff --git a/mysql-test/r/derived_opt.result b/mysql-test/r/derived_opt.result index 6e4ea1b5d36..e3e0df862ea 100644 --- a/mysql-test/r/derived_opt.result +++ b/mysql-test/r/derived_opt.result @@ -75,32 +75,32 @@ insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd' insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105); SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; pla_id mat_id -102 1 -101 1 100 1 -104 2 +101 1 +102 1 103 2 +104 2 105 3 SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; pla_id test -102 1 -101 1 100 1 -104 2 +101 1 +102 1 103 2 +104 2 105 3 explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY m2 ALL NULL NULL NULL NULL 9 -1 PRIMARY <derived2> ref key0 key0 7 test.m2.matintnum 2 +1 PRIMARY <derived2> hash_ALL key0 #hash#key0 7 test.m2.matintnum 9 Using join buffer (flat, BNLH join) 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort -2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 +2 DERIVED m1 hash_ALL PRIMARY #hash#PRIMARY 3 test.mp.mat_id 9 Using join buffer (flat, BNLH join) explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY m2 ALL NULL NULL NULL NULL 9 -1 PRIMARY <derived2> ref key0 key0 7 test.m2.matintnum 2 +1 PRIMARY <derived2> hash_ALL key0 #hash#key0 7 test.m2.matintnum 9 Using join buffer (flat, BNLH join) 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort -2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 +2 DERIVED m1 hash_ALL PRIMARY #hash#PRIMARY 3 test.mp.mat_id 9 Using join buffer (flat, BNLH join) drop table t1,t2; create table t1 (E1 INTEGER UNSIGNED NOT NULL, E2 INTEGER UNSIGNED NOT NULL, E3 INTEGER UNSIGNED NOT NULL, PRIMARY KEY(E1) ); @@ -111,7 +111,7 @@ count(*) explain select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY A ALL NULL NULL NULL NULL 2 Using where -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.A.E2 1 Using where +1 PRIMARY t1 hash_ALL PRIMARY #hash#PRIMARY 4 test.A.E2 2 Using where; Using join buffer (flat, BNLH join) 3 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 2 Using where drop table t1; create table t1 (a int); @@ -324,13 +324,13 @@ JOIN t4 AS td ON tc.a = td.a) tu) limit 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL # -1 PRIMARY tx eq_ref PRIMARY PRIMARY 4 tu.pk # Using index -1 PRIMARY ty eq_ref PRIMARY PRIMARY 4 tu.pk # Using index +1 PRIMARY tx hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 tu.pk # Using index; Using join buffer (flat, BNLH join) +1 PRIMARY ty hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 tu.pk # Using index; Using join buffer (incremental, BNLH join) 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL # 3 DERIVED td system PRIMARY NULL NULL NULL # Using temporary -3 DERIVED tc ref PRIMARY,a a 3 const # -3 DERIVED ta eq_ref PRIMARY PRIMARY 4 test.tc.pk # Using index -3 DERIVED tb eq_ref PRIMARY PRIMARY 4 test.tc.pk # Using index; Distinct +3 DERIVED tc ref PRIMARY,a a 3 const # Using index condition +3 DERIVED ta hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.tc.pk # Using index; Using join buffer (flat, BNLH join) +3 DERIVED tb hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.tc.pk # Using index; Using join buffer (incremental, BNLH join) SELECT * FROM t3 AS tX JOIN t2 AS tY ON (tX.pk = tY.pk) WHERE tX.pk IN |