summaryrefslogtreecommitdiff
path: root/mysql-test/main/subselect_sj2_mat.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/subselect_sj2_mat.result')
-rw-r--r--mysql-test/main/subselect_sj2_mat.result129
1 files changed, 96 insertions, 33 deletions
diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result
index 884451d7dff..73f682755da 100644
--- a/mysql-test/main/subselect_sj2_mat.result
+++ b/mysql-test/main/subselect_sj2_mat.result
@@ -1,5 +1,10 @@
set optimizer_switch='materialization=on';
set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
+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';
@@ -69,6 +74,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
@@ -121,7 +134,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
@@ -719,9 +732,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
@@ -805,10 +817,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
@@ -924,6 +936,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
@@ -955,6 +973,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
@@ -988,6 +1010,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
@@ -1083,11 +1109,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
@@ -1104,11 +1130,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
@@ -1234,6 +1260,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
@@ -1334,7 +1368,15 @@ 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;
+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 optimizer_switch=default;
select @@optimizer_switch like '%materialization=on%';
@@optimizer_switch like '%materialization=on%'
@@ -1419,7 +1461,7 @@ WHERE t1.cat_id = t3.cat_id AND
t3.cat_id IN (SELECT cat_id FROM t2) AND
t3.sack_id = 33479 AND t3.kit_id = 6;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ref PRIMARY PRIMARY 5 const,const 4 Using index
+1 PRIMARY t3 ref PRIMARY PRIMARY 5 const,const 5 Using index
1 PRIMARY t2 ref cat_id cat_id 4 test.t3.cat_id 2 Using where; Using index; FirstMatch(t3)
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.cat_id 1 Using where; Using index
SELECT count(*) FROM t1, t3
@@ -1435,7 +1477,7 @@ WHERE t1.cat_id = t3.cat_id AND
t3.cat_id IN (SELECT cat_id FROM t4) AND
t3.sack_id = 33479 AND t3.kit_id = 6;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ref PRIMARY PRIMARY 5 const,const 4 Using index
+1 PRIMARY t3 ref PRIMARY PRIMARY 5 const,const 5 Using index
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.cat_id 1 Using index
2 MATERIALIZED t4 index cat_id cat_id 4 NULL 19 Using index
@@ -1451,7 +1493,7 @@ WHERE t1.cat_id = t3.cat_id AND
t3.cat_id IN (SELECT cat_id FROM t2) AND
t3.sack_id = 33479 AND t3.kit_id = 6;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ref PRIMARY PRIMARY 5 const,const 4 Using index
+1 PRIMARY t3 ref PRIMARY PRIMARY 5 const,const 5 Using index
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.cat_id 1 Using index
2 MATERIALIZED t2 index cat_id cat_id 4 NULL 19 Using index
@@ -1692,6 +1734,10 @@ insert into t1(`id`,`local_name`) values
(11,'Rollover - Internet Payday'),
(12,'AL Monthly Installment'),
(13,'AL Semi-Monthly Installment');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
explain
SELECT SQL_NO_CACHE t.id
FROM t1 t
@@ -1704,7 +1750,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t index PRIMARY PRIMARY 4 NULL 13 Using where; Using index
2 MATERIALIZED <subquery3> ALL distinct_key NULL NULL NULL 8
2 MATERIALIZED A ALL PRIMARY NULL NULL NULL 13 Using where; Using join buffer (flat, BNL join)
-3 MATERIALIZED B ALL PRIMARY NULL NULL NULL 13 Using where
+3 MATERIALIZED B range PRIMARY PRIMARY 4 NULL 8 Using where
SELECT SQL_NO_CACHE t.id
FROM t1 t
WHERE (
@@ -1841,6 +1887,19 @@ CREATE TABLE t5 (id_product int) ENGINE=MyISAM;
INSERT INTO `t5` VALUES
(652),(668),(669),(670),(671),(673),(674),(675),(676),
(677),(679),(680),(681),(682),(683),(684),(685),(686);
+ANALYZE TABLE t1,t2,t3,t,t5;
+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 Table is already up to date
+test.t3 analyze status Engine-independent statistics collected
+test.t3 analyze status OK
+test.t analyze Error Table 'test.t' doesn't exist
+test.t analyze status Operation failed
+test.t5 analyze status Engine-independent statistics collected
+test.t5 analyze status OK
+set optimizer_switch='rowid_filter=off';
explain
SELECT * FROM t3
JOIN t4 ON (t4.id_product = t3.id_product AND t4.id_shop = 1)
@@ -1853,18 +1912,22 @@ AND t3.id_product IN (SELECT id_product FROM t2 t2_3 WHERE t2_3.id_t2 = 18 OR t2
AND t3.id_product IN (SELECT id_product FROM t2 t2_4 WHERE t2_4.id_t2 = 34 OR t2_4.id_t2 = 23)
AND t3.id_product IN (SELECT id_product FROM t2 t2_5 WHERE t2_5.id_t2 = 29 OR t2_5.id_t2 = 28 OR t2_5.id_t2 = 26);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL PRIMARY 8 NULL 73 Using index
-1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.id_product 1 Using index
+1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 12
+1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t2_2.id_product 1 Using where; Using index
1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 4 func 1 Using where
-1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 Using where
-1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where
-1 PRIMARY t4 eq_ref PRIMARY PRIMARY 8 test.t1.id_product,const 1 Using where; Using index
-1 PRIMARY <subquery6> eq_ref distinct_key distinct_key 4 func 1 Using where
1 PRIMARY t5 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY <subquery6> eq_ref distinct_key distinct_key 4 func 1 Using where
+1 PRIMARY t4 eq_ref PRIMARY PRIMARY 8 test.t3.id_product,const 1 Using where; Using index
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where
+1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 Using where
+1 PRIMARY t1 index NULL PRIMARY 8 NULL 73 Using where; Using index; Using join buffer (flat, BNL join)
+3 MATERIALIZED t2_2 ref id_t2,id_product id_t2 5 const 12 Using where
5 MATERIALIZED t2_4 range id_t2,id_product id_t2 5 NULL 18 Using index condition; Using where
-4 MATERIALIZED t2_3 range id_t2,id_product id_t2 5 NULL 32 Using index condition; Using where
-3 MATERIALIZED t2_2 ref id_t2,id_product id_t2 5 const 12
-2 MATERIALIZED t2_1 ref id_t2,id_product id_t2 5 const 50
-6 MATERIALIZED t2_5 range id_t2,id_product id_t2 5 NULL 30 Using index condition; Using where
+6 MATERIALIZED t2_5 range id_t2,id_product id_t2 5 NULL 31 Using index condition; Using where
+2 MATERIALIZED t2_1 ref id_t2,id_product id_t2 5 const 51
+4 MATERIALIZED t2_3 range id_t2,id_product id_t2 5 NULL 33 Using index condition; Using where
+set optimizer_switch='rowid_filter=default';
drop table t1,t2,t3,t4,t5;
+set global innodb_stats_persistent= @innodb_stats_persistent_save;
+set global innodb_stats_persistent_sample_pages=
+@innodb_stats_persistent_sample_pages_save;
View Lorry Controller Status