diff options
Diffstat (limited to 'mysql-test/r/index_merge.result')
| -rw-r--r-- | mysql-test/r/index_merge.result | 61 |
1 files changed, 61 insertions, 0 deletions
diff --git a/mysql-test/r/index_merge.result b/mysql-test/r/index_merge.result index 9456b4ec978..15aa636d740 100644 --- a/mysql-test/r/index_merge.result +++ b/mysql-test/r/index_merge.result @@ -455,3 +455,64 @@ a 1 UNLOCK TABLES; DROP TABLE t1, t2; +CREATE TABLE `t1` ( +`a` int(11) DEFAULT NULL, +`filler` char(200) DEFAULT NULL, +`b` int(11) DEFAULT NULL, +KEY `a` (`a`), +KEY `b` (`b`) +) ENGINE=MEMORY DEFAULT CHARSET=latin1; +insert into t1 values +(0, 'filler', 0), (1, 'filler', 1), (2, 'filler', 2), (3, 'filler', 3), +(4, 'filler', 4), (5, 'filler', 5), (6, 'filler', 6), (7, 'filler', 7), +(8, 'filler', 8), (9, 'filler', 9), (0, 'filler', 0), (1, 'filler', 1), +(2, 'filler', 2), (3, 'filler', 3), (4, 'filler', 4), (5, 'filler', 5), +(6, 'filler', 6), (7, 'filler', 7), (8, 'filler', 8), (9, 'filler', 9), +(10, 'filler', 10), (11, 'filler', 11), (12, 'filler', 12), (13, 'filler', 13), +(14, 'filler', 14), (15, 'filler', 15), (16, 'filler', 16), (17, 'filler', 17), +(18, 'filler', 18), (19, 'filler', 19), (4, '5 ', 0), (5, '4 ', 0), +(4, '4 ', 0), (4, 'qq ', 5), (5, 'qq ', 4), (4, 'zz ', 4); +create table t2( +`a` int(11) DEFAULT NULL, +`filler` char(200) DEFAULT NULL, +`b` int(11) DEFAULT NULL, +KEY USING BTREE (`a`), +KEY USING BTREE (`b`) +) ENGINE=MEMORY DEFAULT CHARSET=latin1; +insert into t2 select * from t1; +must use sort-union rather than union: +explain select * from t1 where a=4 or b=4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b a,b 5,5 NULL # Using sort_union(a,b); Using where +select * from t1 where a=4 or b=4; +a filler b +4 4 0 +4 5 0 +4 filler 4 +4 filler 4 +4 qq 5 +4 zz 4 +5 qq 4 +select * from t1 ignore index(a,b) where a=4 or b=4; +a filler b +4 4 0 +4 5 0 +4 filler 4 +4 filler 4 +4 qq 5 +4 zz 4 +5 qq 4 +must use union, not sort-union: +explain select * from t2 where a=4 or b=4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index_merge a,b a,b 5,5 NULL # Using union(a,b); Using where +select * from t2 where a=4 or b=4; +a filler b +4 4 0 +4 5 0 +4 filler 4 +4 filler 4 +4 qq 5 +4 zz 4 +5 qq 4 +drop table t1, t2; |