| 1 |
--source include/have_optimizer_trace.inc |
| 2 |
--source include/not_hypergraph.inc # Does not output the same optimizer trace. |
| 3 |
|
| 4 |
CREATE TABLE t1 (f1 INT, f2 INT, f3 CHAR(1), f4 CHAR(1), f5 CHAR(1), f6 CHAR(1), |
| 5 |
PRIMARY KEY (f5, f1), KEY (f2), KEY (f3), KEY (f4)) ENGINE=INNODB; |
| 6 |
INSERT INTO t1 VALUES (1, 1, 'a', 'h', 'i', ''), (2, 3, 'a', 'h', 'i', ''), (3, 2, 'b', '', 'j', ''), (4, 2, 'b', '', 'j', ''); |
| 7 |
INSERT INTO t1 VALUES (5, 1, 'a', 'h', 'i', ''), (6, 3, 'a', 'h', 'i', ''), (7, 2, 'b', '', 'j', ''), (8, 2, 'b', '', 'j', ''); |
| 8 |
INSERT INTO t1 SELECT f1 + 8, f2, f3, f4, f5, f6 FROM t1; |
| 9 |
INSERT INTO t1 SELECT f1 + 16, f2, f3, f4, f5, f6 FROM t1; |
| 10 |
INSERT INTO t1 VALUES (33, 3, 'c', 'g', '', ''), (34, 4, 'c', 'g', '', ''), (35, 5, 'd', 'f', '', ''), (36, 6, 'd', 'f', '', ''); |
| 11 |
INSERT INTO t1 SELECT f1 + 36, f2, f3, f4, f5, f6 FROM t1; |
| 12 |
INSERT INTO t1 SELECT f1 + 72, f2, f3, f4, f5, f6 FROM t1; |
| 13 |
INSERT INTO t1 SELECT f1 + 144, f2, f3, f4, f5, f6 FROM t1; |
| 14 |
ANALYZE TABLE t1; |
| 15 |
|
| 16 |
SET optimizer_trace_max_mem_size=1048576; # 1MB |
| 17 |
SET end_markers_in_json=on; |
| 18 |
SET optimizer_trace="enabled=on,one_line=off"; |
| 19 |
|
| 20 |
SET optimizer_switch='index_merge_union=off,index_merge=off'; |
| 21 |
EXPLAIN SELECT /*+ INDEX_MERGE(t1 f2, f3) */ f1 FROM t1 WHERE f2 = 5 OR f3 = 'c' OR f4 = 'f'; |
| 22 |
SELECT * FROM information_schema.OPTIMIZER_TRACE; |
| 23 |
|
| 24 |
SET optimizer_switch='index_merge=on,index_merge_intersection=on'; |
| 25 |
EXPLAIN SELECT /*+ NO_INDEX_MERGE(t1 f3) */ COUNT(*) FROM t1 WHERE f4 = 'h' AND f2 = 2 AND f3 = 'b'; |
| 26 |
SELECT * FROM information_schema.OPTIMIZER_TRACE; |
| 27 |
|
| 28 |
SET optimizer_switch='index_merge_union=off,index_merge=off'; |
| 29 |
# sort_union(f2,f4) |
| 30 |
EXPLAIN SELECT /*+ INDEX_MERGE(t1 f2, f3, f4) */ * FROM t1 WHERE (f2 = 5 OR f3 = 'b') AND (f2 BETWEEN 300 AND 310 OR f4 = 'b'); |
| 31 |
SELECT * FROM information_schema.OPTIMIZER_TRACE; |
| 32 |
# union(f2,f3) |
| 33 |
EXPLAIN SELECT /*+ INDEX_MERGE(t1 f2, f3, f4) */ * FROM t1 WHERE (f2 = 5 OR f3 = 'b') AND (f2 BETWEEN 1 AND 10 OR f4 = 'b'); |
| 34 |
SELECT * FROM information_schema.OPTIMIZER_TRACE; |
| 35 |
|
| 36 |
SET optimizer_switch=default; |
| 37 |
|
| 38 |
DROP TABLE t1; |