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