diff options
Diffstat (limited to 'mysql-test/r/subselect_sj2.result')
| -rw-r--r-- | mysql-test/r/subselect_sj2.result | 44 |
1 files changed, 17 insertions, 27 deletions
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index 9a7b6748d29..81caed83113 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -32,9 +32,8 @@ a b 9 5 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 3 -1 PRIMARY t2 ref b b 5 test.t1.a 2 -2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Start temporary +1 PRIMARY t2 ref b b 5 test.t1.a 2 End temporary select * from t2 where b in (select a from t1); a b 1 1 @@ -51,9 +50,8 @@ primary key(pk1, pk2, pk3) insert into t3 select a,a, a,a,a from t0; explain select * from t3 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL b NULL NULL NULL 10 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 -2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Start temporary +1 PRIMARY t3 ref b b 5 test.t1.a 1 End temporary select * from t3 where b in (select a from t1); a b pk1 pk2 pk3 1 1 1 1 1 @@ -75,9 +73,8 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B where B.a <5; explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 -1 PRIMARY t3 ref b b 5 test.t0.a 1 -2 SUBQUERY t0 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where; Start temporary +1 PRIMARY t3 ref b b 5 test.t0.a 1 End temporary set @save_ecp= @@engine_condition_pushdown; set engine_condition_pushdown=0; select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); @@ -101,9 +98,8 @@ set join_buffer_size= @save_join_buffer_size; set max_heap_table_size= @save_max_heap_table_size; explain select * from t1 where a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 -2 SUBQUERY t2 index b b 5 NULL 10 Using index +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t2 ref b b 5 test.t1.a 2 Using index; FirstMatch(t1) select * from t1; a b 1 1 @@ -130,9 +126,8 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 22 -1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer (flat, BNL join) -2 SUBQUERY it ALL NULL NULL NULL NULL 22 +1 PRIMARY it ALL NULL NULL NULL NULL 22 Start temporary +1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; End temporary; Using join buffer (flat, BNL join) select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -164,8 +159,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 -2 SUBQUERY it ALL NULL NULL NULL NULL 32 +1 PRIMARY it ALL NULL NULL NULL NULL 32 Using where; FirstMatch(ot) select a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); @@ -198,9 +192,8 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 22 -1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer (flat, BNL join) -2 SUBQUERY it ALL NULL NULL NULL NULL 22 +1 PRIMARY it ALL NULL NULL NULL NULL 22 Start temporary +1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; End temporary; Using join buffer (flat, BNL join) select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -232,8 +225,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 -2 SUBQUERY it ALL NULL NULL NULL NULL 52 +1 PRIMARY it ALL NULL NULL NULL NULL 52 Using where; FirstMatch(ot) select a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); @@ -349,8 +341,7 @@ WHERE t1.Code IN ( SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 -2 SUBQUERY t2 ALL CountryCode NULL NULL NULL 545 Using where +1 PRIMARY t2 ref CountryCode CountryCode 3 test.t1.Code 18 Using where; FirstMatch(t1) SELECT Name FROM t1 WHERE t1.Code IN ( SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); @@ -692,9 +683,8 @@ alter table t3 add primary key(id), add key(a); The following must use loose index scan over t3, key a: explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index a a 5 NULL 1000 Using index -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 -2 SUBQUERY t3 index a a 5 NULL 30000 Using index +1 PRIMARY t2 index a a 5 NULL 1000 Using where; Using index +1 PRIMARY t3 ref a a 5 test.t2.a 30 Using index; FirstMatch(t2) select count(a) from t2 where a in ( SELECT a FROM t3); count(a) 1000 |