diff options
Diffstat (limited to 'mysql-test/main/innodb_icp.result')
-rw-r--r-- | mysql-test/main/innodb_icp.result | 26 |
1 files changed, 14 insertions, 12 deletions
diff --git a/mysql-test/main/innodb_icp.result b/mysql-test/main/innodb_icp.result index 96ff1964ac1..c89d49cad0c 100644 --- a/mysql-test/main/innodb_icp.result +++ b/mysql-test/main/innodb_icp.result @@ -1,5 +1,5 @@ -set @save_storage_engine= @@storage_engine; -set storage_engine=InnoDB; +set @save_storage_engine= @@default_storage_engine; +set default_storage_engine=InnoDB; set @innodb_stats_persistent_save= @@innodb_stats_persistent; set @innodb_stats_persistent_sample_pages_save= @@innodb_stats_persistent_sample_pages; @@ -435,9 +435,9 @@ EXPLAIN SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func 1 Using where -2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using index; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where +2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func # Using where +2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL # Using index; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10); pk i @@ -658,7 +658,7 @@ SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL b NULL NULL NULL # Using where; Using filesort +1 SIMPLE t1 range b b 13 NULL # Using where; Rowid-ordered scan; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; @@ -670,7 +670,7 @@ SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL b NULL NULL NULL # Using where; Using filesort +1 SIMPLE t1 range b b 13 NULL # Using index condition; Rowid-ordered scan; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; @@ -682,16 +682,18 @@ DROP TABLE t1; # CREATE TABLE t1 (b int NOT NULL, c int, a varchar(1024), PRIMARY KEY (b)); INSERT INTO t1 VALUES (1,4,'Ill'); +insert into t1 select seq+100,5,seq from seq_1_to_100; CREATE TABLE t2 (a varchar(1024), KEY (a(512))); INSERT INTO t2 VALUES ('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w'); +insert into t2 select seq from seq_1_to_100; SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort -1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where; Using filesort +1 SIMPLE t2 ref a a 515 test.t1.a # Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c @@ -701,8 +703,8 @@ EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort -1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where; Using filesort +1 SIMPLE t2 ref a a 515 test.t1.a # Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c @@ -963,7 +965,7 @@ pk key1 key2 142 frequent-val rare-42 drop table t1, t2; set optimizer_switch=@innodb_icp_tmp; -set storage_engine= @save_storage_engine; +set default_storage_engine= @save_storage_engine; set global innodb_stats_persistent= @innodb_stats_persistent_save; set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save; |