diff options
Diffstat (limited to 'mysql-test/main/subselect_sj_jcl6.result')
| -rw-r--r-- | mysql-test/main/subselect_sj_jcl6.result | 41 |
1 files changed, 25 insertions, 16 deletions
diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result index e9a19b2a1c3..02e9a833db6 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -216,7 +216,7 @@ a b a b insert into t1 select (A.a + 10 * B.a),1 from t0 A, t0 B; explain extended select * from t1 where a in (select pk from t10 where pk<3); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t10 range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index +1 PRIMARY t10 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where; Using index 1 PRIMARY t1 ALL NULL NULL NULL NULL 103 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t10`.`pk` and `test`.`t10`.`pk` < 3 @@ -1252,7 +1252,7 @@ set @tmp_optimizer_switch=@@optimizer_switch; set optimizer_switch='semijoin=on,materialization=off'; set optimizer_switch='firstmatch=off,loosescan=on'; set @tmp_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; -set optimizer_use_condition_selectivity=1; +set optimizer_use_condition_selectivity=4; CREATE TABLE t1 (i INTEGER); INSERT INTO t1 VALUES (1), (2), (3), (4), (5); CREATE TABLE t2 (i INTEGER, j INTEGER, KEY k(i, j)); @@ -1260,8 +1260,8 @@ INSERT INTO t2 VALUES (1, 0), (1, 1), (2, 0), (2, 1); EXPLAIN SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 -1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; Start temporary; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; Start temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; End temporary; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); i 1 @@ -2576,7 +2576,9 @@ INSERT INTO t2 VALUES (1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1); analyze table t1,t2; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK explain SELECT a, b, d FROM t1, t2 @@ -2634,8 +2636,11 @@ insert into t2 select (A.a+1000*B.a)/20, 'filler_data' from t10 A, t0 B; analyze table t1,t2,t3; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status Table is already up to date +test.t3 analyze status Engine-independent statistics collected test.t3 analyze status OK delete from t1 where kp2 in (1,3); # Ref + LooseScan on t1: @@ -2646,7 +2651,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 const PRIMARY PRIMARY 4 const 1 Using index 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where 1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 10 Using where; Using index; LooseScan -1 PRIMARY t2 ref a a 5 test.t1.kp2 20 Using index +1 PRIMARY t2 ref a a 5 test.t1.kp2 19 Using index select sum(t2.a) from t2,t3 where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2); @@ -3151,9 +3156,11 @@ set optimizer_switch= @tmp_mdev6859; set @tmp_mdev12675=@@optimizer_switch; set optimizer_switch=default; create table t1 (a int) engine=myisam; -insert into t1 values (5),(3),(2),(7),(2),(5),(1); +insert into t1 values (3),(2),(7),(2),(1); create table t2 (b int, index idx(b)) engine=myisam; -insert into t2 values (2),(3),(2),(1),(3),(4); +insert into t2 values (2),(3),(2),(1),(3),(4),(1),(2),(1),(2); +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; insert into t2 select b+10 from t2; insert into t2 select b+10 from t2; insert into t2 select b+10 from t2; @@ -3167,20 +3174,22 @@ insert into t2 select b+10 from t2; insert into t2 select b+10 from t2; analyze table t1,t2; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK explain select a from t1, t2 where b between 1 and 2 and 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 7 Using where -1 PRIMARY t2 ref idx idx 5 test.t1.a 256 Using index; FirstMatch(t1) -1 PRIMARY t2 range idx idx 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY t2 ref idx idx 5 test.t1.a 1462 Using index; FirstMatch(t1) +1 PRIMARY t2 range idx idx 5 NULL 5 Using where; Using index; Using join buffer (flat, BNL join) explain select a from t1 join t2 on b between 1 and 2 and 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 7 Using where -1 PRIMARY t2 ref idx idx 5 test.t1.a 256 Using index; FirstMatch(t1) -1 PRIMARY t2 range idx idx 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY t2 ref idx idx 5 test.t1.a 1462 Using index; FirstMatch(t1) +1 PRIMARY t2 range idx idx 5 NULL 5 Using where; Using index; Using join buffer (flat, BNL join) drop table t1,t2; set optimizer_switch= @tmp_mdev12675; # @@ -3292,7 +3301,7 @@ SELECT Id FROM t1 WHERE Id in (SELECT t1_Id FROM t2 WHERE t2.col1 IS NULL); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 MATERIALIZED t2 ref col1 col1 5 const 1 100.00 Using index condition; Using where +2 MATERIALIZED t2 ref col1 col1 5 const 2 100.00 Using index condition; Using where Warnings: Note 1003 select 1 AS `Id` from (`test`.`t2`) where `test`.`t2`.`t1_Id` = 1 and `test`.`t2`.`col1` is null DROP TABLE t1, t2; @@ -3527,7 +3536,7 @@ EXPLAIN SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) GROUP BY a HAVING a != 'z'; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t index idx_a idx_a 4 NULL 3 Using index +1 PRIMARY t range idx_a idx_a 4 NULL 3 Using where; Using index 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index @@ -3541,7 +3550,7 @@ EXPLAIN SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) GROUP BY a HAVING a != 'z'; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t index idx_a idx_a 4 NULL 3 Using index +1 PRIMARY t range idx_a idx_a 4 NULL 3 Using where; Using index 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index |