diff options
Diffstat (limited to 'mysql-test/main/subselect_sj2.result')
| -rw-r--r-- | mysql-test/main/subselect_sj2.result | 73 |
1 files changed, 55 insertions, 18 deletions
diff --git a/mysql-test/main/subselect_sj2.result b/mysql-test/main/subselect_sj2.result index 948be5766a2..a127c18280e 100644 --- a/mysql-test/main/subselect_sj2.result +++ b/mysql-test/main/subselect_sj2.result @@ -1,3 +1,8 @@ +set @innodb_stats_persistent_save= @@innodb_stats_persistent; +set @innodb_stats_persistent_sample_pages_save= +@@innodb_stats_persistent_sample_pages; +set global innodb_stats_persistent= 1; +set global innodb_stats_persistent_sample_pages=100; set @subselect_sj2_tmp= @@optimizer_switch; set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; @@ -67,6 +72,14 @@ primary key(pk1, pk2, pk3) ) engine=innodb; insert into t3 select a,a, a,a,a from t0; insert into t3 select a,a, a+100,a+100,a+100 from t0; +analyze table t1,t2,t3; +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 +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK 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 20 @@ -119,7 +132,7 @@ 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 Using where -1 PRIMARY t2 ref b b 5 test.t1.a 2 Using index; FirstMatch(t1) +1 PRIMARY t2 ref b b 5 test.t1.a 1 Using index; FirstMatch(t1) select * from t1; a b 1 1 @@ -717,9 +730,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 <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED 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 @@ -803,10 +815,10 @@ explain SELECT * FROM t3 WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias1 const PRIMARY PRIMARY 4 const 1 Using index -1 PRIMARY alias2 index f12 f12 7 NULL 1 Using index; LooseScan -1 PRIMARY t1 index NULL PRIMARY 4 NULL 2 Using index; FirstMatch(alias2) -1 PRIMARY t3 ALL NULL NULL NULL NULL 7 Using where; Using join buffer (flat, BNL join) +1 PRIMARY alias1 const PRIMARY PRIMARY 4 const # Using index +1 PRIMARY alias2 index f12 f12 7 NULL # Using index; LooseScan +1 PRIMARY t1 index NULL PRIMARY 4 NULL # Using index; FirstMatch(alias2) +1 PRIMARY t3 ALL NULL NULL NULL NULL # Using where; Using join buffer (flat, BNL join) SELECT * FROM t3 WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24); f12 @@ -922,6 +934,12 @@ INSERT INTO t2 VALUES (6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1), (11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5), (15,'g',6),(16,'x',7),(17,'f',8); +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 * FROM t1 WHERE b IN ( SELECT d FROM t2, t1 @@ -953,6 +971,10 @@ INSERT INTO t1 VALUES ('y','y'),('t','t'),('d','d'),('s','s'),('r','r'), ('m','m'),('b','b'),('x','x'),('g','g'),('p','p'), ('q','q'),('w','w'),('d','d'),('e','e'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; # This query returned 6 rows instead of 19 SELECT * FROM v1 @@ -986,6 +1008,10 @@ y y CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(1) NOT NULL, KEY(a)) ENGINE=InnoDB; INSERT INTO t2 SELECT * FROM t1; INSERT INTO t2 SELECT * FROM t1; +ANALYZE TABLE t2; +Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK EXPLAIN SELECT * FROM t2 WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 @@ -1081,11 +1107,11 @@ WHERE alias5.b = alias4.b AND ( alias5.b >= alias3.b OR alias5.c != alias3.c ) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL 19 Using where -1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d 1 Using index -1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b 1 Using where; FirstMatch(alias3) -1 PRIMARY alias2 ALL NULL NULL NULL NULL 14 Using join buffer (flat, BNL join) -1 PRIMARY alias1 ALL NULL NULL NULL NULL 14 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # +1 PRIMARY alias5 index PRIMARY c 4 NULL # Using where; Using index +1 PRIMARY alias4 eq_ref PRIMARY,c PRIMARY 4 test.alias5.b # Using where; FirstMatch(alias3) +1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) +1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 @@ -1102,11 +1128,11 @@ WHERE alias5.b = alias4.b AND ( alias5.b >= alias3.b OR alias3.c != alias5.c ) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL 19 Using where -1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d 1 Using index -1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b 1 Using where; FirstMatch(alias3) -1 PRIMARY alias2 ALL NULL NULL NULL NULL 14 Using join buffer (flat, BNL join) -1 PRIMARY alias1 ALL NULL NULL NULL NULL 14 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # +1 PRIMARY alias5 index PRIMARY c 4 NULL # Using where; Using index +1 PRIMARY alias4 eq_ref PRIMARY,c PRIMARY 4 test.alias5.b # Using where; FirstMatch(alias3) +1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) +1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 @@ -1232,6 +1258,14 @@ INSERT IGNORE INTO t2 (t2id, t1idref) SELECT t1id, t1id FROM t1; INSERT IGNORE INTO t1 VALUES (200001, 'a'); INSERT IGNORE INTO t2 (t2id, t1idref) VALUES (200011, 200001),(200012, 200001),(200013, 200001); INSERT IGNORE INTO t3 VALUES (1, 200011, 1), (1, 200012, 2), (1, 200013, 3); +ANALYZE TABLE t1,t2,t3; +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 +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK set @tmp7474= @@optimizer_search_depth; SET SESSION optimizer_search_depth = 1; SELECT SQL_NO_CACHE @@ -1332,4 +1366,7 @@ a pk b DROP TABLE t1,t2,t3; DROP VIEW v3; # This must be the last in the file: +set global innodb_stats_persistent= @innodb_stats_persistent_save; +set global innodb_stats_persistent_sample_pages= +@innodb_stats_persistent_sample_pages_save; set optimizer_switch=@subselect_sj2_tmp; |