| 1 |
|
| 2 |
--source include/have_optimizer_trace.inc |
| 3 |
--source include/not_hypergraph.inc # Does not output the same optimizer trace. |
| 4 |
|
| 5 |
SET optimizer_trace_max_mem_size = 1000000; |
| 6 |
SET optimizer_trace="enabled=on"; |
| 7 |
|
| 8 |
SET @path = "$**.filtering_effect"; |
| 9 |
let $trace_query = |
| 10 |
SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE; |
| 11 |
|
| 12 |
CREATE TABLE t1 (col1 INT, col2 INT); |
| 13 |
|
| 14 |
INSERT INTO t1 VALUES (1, 10), (2, 20), (3, 30), (4, 40), (5, 50), (NULL, NULL); |
| 15 |
ANALYZE TABLE t1 UPDATE HISTOGRAM ON col1, col2 WITH 2 BUCKETS; |
| 16 |
UPDATE mysql.innodb_table_stats SET n_rows = 6 |
| 17 |
WHERE database_name = "test" AND table_name = "t1"; |
| 18 |
|
| 19 |
EXPLAIN SELECT * FROM t1 WHERE col1 < 4; |
| 20 |
eval $trace_query; |
| 21 |
|
| 22 |
EXPLAIN SELECT * FROM t1 WHERE col1 > 4; |
| 23 |
eval $trace_query; |
| 24 |
|
| 25 |
EXPLAIN SELECT * FROM t1 WHERE col1 >= 4; |
| 26 |
eval $trace_query; |
| 27 |
|
| 28 |
EXPLAIN SELECT * FROM t1 WHERE col1 <= 4; |
| 29 |
eval $trace_query; |
| 30 |
|
| 31 |
EXPLAIN SELECT * FROM t1 WHERE col1 = 4; |
| 32 |
eval $trace_query; |
| 33 |
|
| 34 |
EXPLAIN SELECT * FROM t1 WHERE col1 != 4; |
| 35 |
eval $trace_query; |
| 36 |
|
| 37 |
EXPLAIN SELECT * FROM t1 WHERE col1 <> 4; |
| 38 |
eval $trace_query; |
| 39 |
|
| 40 |
EXPLAIN SELECT * FROM t1 WHERE col1 BETWEEN 4 AND 6; |
| 41 |
eval $trace_query; |
| 42 |
|
| 43 |
EXPLAIN SELECT * FROM t1 WHERE col1 NOT BETWEEN 4 AND 6; |
| 44 |
eval $trace_query; |
| 45 |
|
| 46 |
EXPLAIN SELECT * FROM t1 WHERE col1 IS NULL; |
| 47 |
eval $trace_query; |
| 48 |
|
| 49 |
EXPLAIN SELECT * FROM t1 WHERE col1 IS NOT NULL; |
| 50 |
eval $trace_query; |
| 51 |
|
| 52 |
DROP TABLE t1; |
| 53 |
|
| 54 |
--echo # |
| 55 |
--echo # Show that histogram selecitivty estimation is printed for other acecss |
| 56 |
--echo # methods as well. |
| 57 |
--echo # |
| 58 |
|
| 59 |
--echo # Access method: RANGE |
| 60 |
CREATE TABLE t1 (col1 INT, col2 INT); |
| 61 |
INSERT INTO t1 VALUES (15, 84), (19, 51), (70, 52), (54, 82), (33, 93), (80, 0), |
| 62 |
(51, 82), (50, 14), (22, 27), (96, 82), (96, 3), (86, 61), (4, 58), (95, 20), |
| 63 |
(28, 56), (64, 99), (10, 85), (30, 81), (13, 85), (28, 94); |
| 64 |
CREATE INDEX idx1 ON t1 (col1, col2); |
| 65 |
ANALYZE TABLE t1; |
| 66 |
ANALYZE TABLE t1 UPDATE HISTOGRAM ON col1, col2 WITH 8 BUCKETS; |
| 67 |
|
| 68 |
EXPLAIN SELECT * FROM t1 WHERE col1 > 10 AND col2 < 88; |
| 69 |
eval $trace_query; |
| 70 |
DROP TABLE t1; |
| 71 |
|
| 72 |
--echo # |
| 73 |
--echo # Show the difference between the calculated selectivity estimation for |
| 74 |
--echo # each predicate and the total calculated selectivity estimation |
| 75 |
--echo # |
| 76 |
|
| 77 |
CREATE TABLE t1 (col1 INT); |
| 78 |
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8); |
| 79 |
ANALYZE TABLE t1; |
| 80 |
ANALYZE TABLE t1 UPDATE HISTOGRAM ON col1 WITH 2 BUCKETS; |
| 81 |
|
| 82 |
EXPLAIN SELECT * FROM t1 WHERE col1 > 2 AND col1 <= 7; |
| 83 |
|
| 84 |
--echo # Selectivity estimations for each conditions. |
| 85 |
eval $trace_query; |
| 86 |
--echo # Total calculated selectivity estimation. |
| 87 |
SELECT JSON_EXTRACT(trace, "$**.final_filtering_effect") |
| 88 |
FROM information_schema.OPTIMIZER_TRACE; |
| 89 |
|
| 90 |
DROP TABLE t1; |