diff options
Diffstat (limited to 'mysql-test/main/join_cache.result')
| -rw-r--r-- | mysql-test/main/join_cache.result | 56 |
1 files changed, 28 insertions, 28 deletions
diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index b5ed58e4382..0965ac8a555 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -3125,15 +3125,15 @@ t1.metaid = t2.metaid AND t1.affiliateid = '2'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 system PRIMARY NULL NULL NULL 1 1 SIMPLE t5 ref PRIMARY,t5_formattypeid t5_formattypeid 4 const 1 -1 SIMPLE t9 index PRIMARY,t9_subgenreid,t9_metaid PRIMARY 8 NULL 2 Using index; Using join buffer (flat, BNL join) -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t9.metaid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t4 ref PRIMARY,t4_formatclassid,t4_formats_idx t4_formatclassid 4 test.t5.formatclassid 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_formatid 4 test.t4.formatid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t1 ref t1_affiliateid,t1_metaid t1_metaid 4 test.t3.metaid 1 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.metaid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t3.metaid 1 Using index +1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t9 ref PRIMARY,t9_subgenreid,t9_metaid t9_metaid 4 test.t3.metaid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t9.metaid 1 Using index -1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaidformatid 4 test.t9.metaid 1 Using index condition; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t4 eq_ref PRIMARY,t4_formatclassid,t4_formats_idx PRIMARY 4 test.t3.formatid 1 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t1 ref t1_affiliateid,t1_metaid t1_affiliateid 4 const 2 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan SELECT t1.uniquekey, t1.xml AS affiliateXml, t8.name AS artistName, t8.artistid, t11.name AS genreName, t11.genreid, t11.priority AS genrePriority, @@ -3273,7 +3273,7 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; 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 i_a i_a 4 test.t1.a 2 Using where; Not exists +1 SIMPLE t2 ref i_a i_a 4 test.t1.a 1 Using where; Not exists SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; a a b 3 NULL NULL @@ -3284,7 +3284,7 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; 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 i_a i_a 4 test.t1.a 2 Using where; Not exists; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t2 ref i_a i_a 4 test.t1.a 1 Using where; Not exists; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; a a b 3 NULL NULL @@ -3311,7 +3311,7 @@ select t1.a, count(t2.p) as count from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index; Using temporary; Using filesort -1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t2 ref i_a i_a 5 test.t1.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan select t1.a, count(t2.p) as count from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a; a count @@ -4085,7 +4085,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.carrier 1 Using where -1 SIMPLE t4 ref carrier_id carrier_id 5 test.t3.id 2 Using index +1 SIMPLE t4 ref carrier_id carrier_id 5 test.t3.id 1 Using index SET join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3,t4; # @@ -4101,7 +4101,7 @@ set join_cache_level = 5; explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 ref b b 5 test.t1.b 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t2 ref b b 5 test.t1.b 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; a NULL @@ -4110,7 +4110,7 @@ set join_cache_level = 8; explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 ref b b 5 test.t1.b 2 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE t2 ref b b 5 test.t1.b 1 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; a NULL @@ -4121,7 +4121,7 @@ set join_cache_level = 5; explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 ref b b 5 test.t1.b 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t2 ref b b 5 test.t1.b 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; a NULL @@ -4136,7 +4136,7 @@ set join_cache_level = 5; explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 ref b b 103 test.t1.b 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t2 ref b b 103 test.t1.b 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; a NULL @@ -4145,7 +4145,7 @@ set join_cache_level = 8; explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 ref b b 103 test.t1.b 2 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE t2 ref b b 103 test.t1.b 1 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; a NULL @@ -4529,7 +4529,7 @@ EXPLAIN SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 -1 SIMPLE t1 ref cu cu 33 func 2 Using where; Using index +1 SIMPLE t1 ref cu cu 33 func 1 Using where; Using index SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ; i 6 @@ -4862,7 +4862,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ref idx idx 5 test.t1.a1 2 100.00 Using where +1 SIMPLE t4 ref idx idx 5 test.t1.a1 1 100.00 Using where 1 SIMPLE t5 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a1` AS `a1`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`c2` AS `c2`,`test`.`t2`.`d2` AS `d2`,`test`.`t3`.`pk` AS `pk`,`test`.`t3`.`a3` AS `a3`,`test`.`t3`.`c3` AS `c3`,`test`.`t3`.`d3` AS `d3`,`test`.`t4`.`pk` AS `pk`,`test`.`t4`.`a4` AS `a4`,`test`.`t5`.`pk` AS `pk`,`test`.`t5`.`a5` AS `a5` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`d2` = `test`.`t1`.`pk` and `test`.`t3`.`a3` = `test`.`t2`.`c2`) left join `test`.`t4` on(`test`.`t4`.`a4` = `test`.`t1`.`a1` and `test`.`t1`.`a1` is not null) left join `test`.`t5` on(`test`.`t5`.`a5` = `test`.`t3`.`a3`) where 1 @@ -4884,7 +4884,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t4 ref idx idx 5 test.t1.a1 2 100.00 Using where +1 SIMPLE t4 ref idx idx 5 test.t1.a1 1 100.00 Using where 1 SIMPLE t5 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a1` AS `a1`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`c2` AS `c2`,`test`.`t2`.`d2` AS `d2`,`test`.`t3`.`pk` AS `pk`,`test`.`t3`.`a3` AS `a3`,`test`.`t3`.`c3` AS `c3`,`test`.`t3`.`d3` AS `d3`,`test`.`t4`.`pk` AS `pk`,`test`.`t4`.`a4` AS `a4`,`test`.`t5`.`pk` AS `pk`,`test`.`t5`.`a5` AS `a5` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`d2` = `test`.`t1`.`pk` and `test`.`t3`.`a3` = `test`.`t2`.`c2`) left join `test`.`t4` on(`test`.`t4`.`a4` = `test`.`t1`.`a1` and `test`.`t1`.`a1` is not null) left join `test`.`t5` on(`test`.`t5`.`a5` = `test`.`t3`.`a3`) where 1 @@ -4906,7 +4906,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ref idx idx 5 test.t1.a1 2 100.00 Using where +1 SIMPLE t4 ref idx idx 5 test.t1.a1 1 100.00 Using where 1 SIMPLE t5 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a1` AS `a1`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`c2` AS `c2`,`test`.`t2`.`d2` AS `d2`,`test`.`t3`.`pk` AS `pk`,`test`.`t3`.`a3` AS `a3`,`test`.`t3`.`c3` AS `c3`,`test`.`t3`.`d3` AS `d3`,`test`.`t4`.`pk` AS `pk`,`test`.`t4`.`a4` AS `a4`,`test`.`t5`.`pk` AS `pk`,`test`.`t5`.`a5` AS `a5` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`d2` = `test`.`t1`.`pk` and `test`.`t3`.`a3` = `test`.`t2`.`c2`) left join `test`.`t4` on(`test`.`t4`.`a4` = `test`.`t1`.`a1` and `test`.`t1`.`a1` is not null) left join `test`.`t5` on(`test`.`t5`.`a5` = `test`.`t3`.`a3`) where 1 @@ -5154,7 +5154,7 @@ EXPLAIN SELECT * FROM t1,t2 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0, 100) ORDER BY t1.f2 LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range f1,f2 f1 5 NULL 3 Using where; Rowid-ordered scan; Using filesort -1 SIMPLE t2 ref f3 f3 67 test.t1.f2 2 Using where; Using index +1 SIMPLE t2 ref f3 f3 67 test.t1.f2 1 Using where; Using index SELECT * FROM t1,t2 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1; f1 f2 f3 @@ -5164,7 +5164,7 @@ EXPLAIN SELECT * FROM t1,t2 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range f1,f2 f1 5 NULL 3 Using index condition; Using where; Rowid-ordered scan; Using filesort -1 SIMPLE t2 ref f3 f3 67 test.t1.f2 2 Using where; Using index +1 SIMPLE t2 ref f3 f3 67 test.t1.f2 1 Using where; Using index SELECT * FROM t1,t2 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1; f1 f2 f3 @@ -5265,7 +5265,7 @@ EXPLAIN SELECT a FROM t1,t2 WHERE t2.v = t1.v ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where -1 SIMPLE t2 ref idx idx 4 test.t1.v 2 +1 SIMPLE t2 ref idx idx 4 test.t1.v 1 SELECT a FROM t1,t2 WHERE t2.v = t1.v ; a 11 @@ -5323,7 +5323,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -1 PRIMARY t2 ref c c 5 test.t1.b 2 Using index; Start temporary; End temporary +1 PRIMARY t2 ref c c 5 test.t1.b 1 Using index; Start temporary; End temporary SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); a b 3914 17 @@ -5503,7 +5503,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t3 system NULL NULL NULL NULL 1 1 PRIMARY t2 range a,c a 5 NULL 2 Using index condition; Using where; Using filesort -1 PRIMARY t4 ref c c 5 test.t2.c 2 Using where; Start temporary; End temporary +1 PRIMARY t4 ref c c 5 test.t2.c 1 Using where; Start temporary; End temporary SELECT * FROM t1,t2 WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND t2.a BETWEEN 4 and 5 @@ -5521,7 +5521,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 Using temporary; Using filesort 1 PRIMARY t3 system NULL NULL NULL NULL 1 1 PRIMARY t2 range a,c a 5 NULL 2 Using index condition; Using where -1 PRIMARY t4 ref c c 5 test.t2.c 2 Using where; Start temporary; End temporary +1 PRIMARY t4 ref c c 5 test.t2.c 1 Using where; Start temporary; End temporary SELECT * FROM t1,t2 WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND t2.a BETWEEN 4 and 5 @@ -5540,7 +5540,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 Using temporary; Using filesort 1 PRIMARY t3 system NULL NULL NULL NULL 1 1 PRIMARY t2 range a,c a 5 NULL 2 Using index condition; Using where -1 PRIMARY t4 ref c c 5 test.t2.c 2 Using where; Start temporary; End temporary +1 PRIMARY t4 ref c c 5 test.t2.c 1 Using where; Start temporary; End temporary SELECT * FROM t1,t2 WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND t2.a BETWEEN 4 and 5 @@ -5583,7 +5583,7 @@ and t2.uid=t1.fid; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary 1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index -1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 Using where; End temporary; Using join buffer (flat, BKAH join); Rowid-ordered scan +1 PRIMARY t1 ref uid uid 5 test.t3.fid 1 Using where; End temporary; Using join buffer (flat, BKAH join); Rowid-ordered scan 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.fid 1 Using join buffer (flat, BKAH join); Rowid-ordered scan select name from t2, t1 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) |