diff options
| author | Igor Babaev <igor@askmonty.org> | 2011-12-15 00:21:15 -0800 |
|---|---|---|
| committer | Igor Babaev <igor@askmonty.org> | 2011-12-15 00:21:15 -0800 |
| commit | f5dac20f38fcf581b0616562cd2da21fb8c50218 (patch) | |
| tree | bfa94f668924256290b553ee80b1380b1066344c /mysql-test/r/group_by.result | |
| parent | efb57a8ebf798eee816981488c901539ec9fcdad (diff) | |
| download | mariadb-git-f5dac20f38fcf581b0616562cd2da21fb8c50218.tar.gz | |
Made the optimizer switch flags 'outer_join_with_cache', 'semijoin_with_cache'
set to 'on' by default.
Diffstat (limited to 'mysql-test/r/group_by.result')
| -rw-r--r-- | mysql-test/r/group_by.result | 9 |
1 files changed, 9 insertions, 0 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 7073a6cdfbc..6ac3257ca6c 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -450,6 +450,8 @@ drop table t1,t2; CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID )); insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; select S.ID as xID, S.ID1 as xID1 from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2; xID xID1 1 1 @@ -468,6 +470,7 @@ xID xID1 Level 2 2 ** 3 134 *** 4 185 **** +SET optimizer_switch=@save_optimizer_switch; drop table t1; CREATE TABLE t1 ( pid int(11) unsigned NOT NULL default '0', @@ -1539,12 +1542,15 @@ USE INDEX FOR JOIN (i2) USE INDEX FOR JOIN (i2,i2); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL i2 9 NULL 144 Using index +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='semijoin_with_cache=off'; EXPLAIN SELECT 1 FROM t1 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index PRIMARY,i2 PRIMARY 4 NULL 144 Using index 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 144 +SET optimizer_switch=@save_optimizer_switch; CREATE TABLE t2 (a INT, b INT, KEY(a)); INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; @@ -1553,11 +1559,14 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='semijoin_with_cache=off'; EXPLAIN SELECT 1 FROM t2 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 4 Using index 1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t2) +SET optimizer_switch=@save_optimizer_switch; SHOW VARIABLES LIKE 'old'; Variable_name Value old OFF |