diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/main/myisam_icp.result | 28 |
1 files changed, 21 insertions, 7 deletions
diff --git a/mysql-test/main/myisam_icp.result b/mysql-test/main/myisam_icp.result index 3078f8bc..1c06c6d8 100644 --- a/mysql-test/main/myisam_icp.result +++ b/mysql-test/main/myisam_icp.result @@ -454,9 +454,10 @@ c1 INT NOT NULL, PRIMARY KEY (pk) ); INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1); +insert into t1 select seq,seq from seq_100_to_110; EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 5 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 16 Using where SET SESSION optimizer_switch='index_condition_pushdown=off'; SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); pk c1 @@ -464,6 +465,17 @@ pk c1 2 7 4 3 5 1 +100 100 +101 101 +102 102 +103 103 +104 104 +105 105 +106 106 +107 107 +108 108 +109 109 +110 110 DROP TABLE t1; set optimizer_switch= @save_optimizer_switch; # @@ -681,7 +693,6 @@ 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'); @@ -697,8 +708,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 ALL PRIMARY NULL NULL NULL # Using where; Using filesort -1 SIMPLE t2 ref a a 515 test.t1.a # Using where +1 SIMPLE t1 system PRIMARY NULL NULL NULL # +1 SIMPLE t2 ref a a 515 const # 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 @@ -708,8 +719,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 ALL PRIMARY NULL NULL NULL # Using where; Using filesort -1 SIMPLE t2 ref a a 515 test.t1.a # Using where +1 SIMPLE t1 system PRIMARY NULL NULL NULL # +1 SIMPLE t2 ref a a 515 const # 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 @@ -821,6 +832,8 @@ test.t2 analyze status Engine-independent statistics collected test.t2 analyze status Table is already up to date SET @save_optimize_switch=@@optimizer_switch; SET optimizer_switch='materialization=on'; +set @save_optimizer_where_cost=@@optimizer_where_cost; +set @@optimizer_where_cost=1; EXPLAIN SELECT COUNT(*) FROM t1 AS t, t2 WHERE c = g @@ -844,6 +857,7 @@ OR a = 0 AND h < 'z' ); COUNT(*) 1478 SET optimizer_switch=@save_optimizer_switch; +set @@optimizer_where_cost=@save_optimizer_where_cost; DROP TABLE t1,t2; # check "Handler_pushed" status varuiables CREATE TABLE t1 ( @@ -934,7 +948,7 @@ DROP TABLE t1; # Bug#870046: ICP for a GROUP BY query # CREATE TABLE t1 (a int, b varchar(1), c varchar(1), INDEX idx(b)); -INSERT INTO t1 VALUES (2,'x','x'), (5,'x','y'); +INSERT INTO t1 VALUES (2,'x','x'), (5,'x','y'), (6,'a','b'), (7,'a','b'); SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; |