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
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
|
SET optimizer_trace_max_mem_size = 1000000;
SET optimizer_trace="enabled=on";
SET @path = "$**.filtering_effect";
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;
Table Op Msg_type Msg_text
test.t1 histogram status Histogram statistics created for column 'col1'.
test.t1 histogram status Histogram statistics created for column 'col2'.
UPDATE mysql.innodb_table_stats SET n_rows = 6
WHERE database_name = "test" AND table_name = "t1";
EXPLAIN SELECT * FROM t1 WHERE col1 < 4;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 50.00 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col1` < 4)
SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
JSON_EXTRACT(trace, @path)
[[{"condition": "(`t1`.`col1` < 4)", "histogram_selectivity": 0.5}]]
EXPLAIN SELECT * FROM t1 WHERE col1 > 4;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 16.67 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col1` > 4)
SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
JSON_EXTRACT(trace, @path)
[[{"condition": "(`t1`.`col1` > 4)", "histogram_selectivity": 0.166667}]]
EXPLAIN SELECT * FROM t1 WHERE col1 >= 4;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 33.33 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col1` >= 4)
SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
JSON_EXTRACT(trace, @path)
[[{"condition": "(`t1`.`col1` >= 4)", "histogram_selectivity": 0.333333}]]
EXPLAIN SELECT * FROM t1 WHERE col1 <= 4;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 66.67 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col1` <= 4)
SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
JSON_EXTRACT(trace, @path)
[[{"condition": "(`t1`.`col1` <= 4)", "histogram_selectivity": 0.666667}]]
EXPLAIN SELECT * FROM t1 WHERE col1 = 4;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 16.67 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col1` = 4)
SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
JSON_EXTRACT(trace, @path)
[[{"condition": "(`t1`.`col1` = 4)", "histogram_selectivity": 0.166667}]]
EXPLAIN SELECT * FROM t1 WHERE col1 != 4;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 66.67 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col1` <> 4)
SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
JSON_EXTRACT(trace, @path)
[[{"condition": "(`t1`.`col1` <> 4)", "histogram_selectivity": 0.666667}]]
EXPLAIN SELECT * FROM t1 WHERE col1 <> 4;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 66.67 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col1` <> 4)
SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
JSON_EXTRACT(trace, @path)
[[{"condition": "(`t1`.`col1` <> 4)", "histogram_selectivity": 0.666667}]]
EXPLAIN SELECT * FROM t1 WHERE col1 BETWEEN 4 AND 6;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 33.33 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col1` between 4 and 6)
SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
JSON_EXTRACT(trace, @path)
[[{"condition": "(`t1`.`col1` between 4 and 6)", "histogram_selectivity": 0.333333}]]
EXPLAIN SELECT * FROM t1 WHERE col1 NOT BETWEEN 4 AND 6;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 50.00 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col1` not between 4 and 6)
SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
JSON_EXTRACT(trace, @path)
[[{"condition": "(`t1`.`col1` not between 4 and 6)", "histogram_selectivity": 0.5}]]
EXPLAIN SELECT * FROM t1 WHERE col1 IS NULL;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 16.67 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col1` is null)
SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
JSON_EXTRACT(trace, @path)
[[{"condition": "(`t1`.`col1` is null)", "histogram_selectivity": 0.166667}]]
EXPLAIN SELECT * FROM t1 WHERE col1 IS NOT NULL;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 83.33 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col1` is not null)
SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
JSON_EXTRACT(trace, @path)
[[{"condition": "(`t1`.`col1` is not null)", "histogram_selectivity": 0.833333}]]
DROP TABLE t1;
#
# Show that histogram selecitivty estimation is printed for other acecss
# methods as well.
#
# 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;
Table Op Msg_type Msg_text
test.t1 analyze status OK
ANALYZE TABLE t1 UPDATE HISTOGRAM ON col1, col2 WITH 8 BUCKETS;
Table Op Msg_type Msg_text
test.t1 histogram status Histogram statistics created for column 'col1'.
test.t1 histogram status Histogram statistics created for column 'col2'.
EXPLAIN SELECT * FROM t1 WHERE col1 > 10 AND col2 < 88;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL range idx1 idx1 5 NULL 18 85.00 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where ((`test`.`t1`.`col1` > 10) and (`test`.`t1`.`col2` < 88))
SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
JSON_EXTRACT(trace, @path)
[[{"condition": "(`t1`.`col2` < 88)", "histogram_selectivity": 0.85}]]
DROP TABLE t1;
#
# Show the difference between the calculated selectivity estimation for
# each predicate and the total calculated selectivity estimation
#
CREATE TABLE t1 (col1 INT);
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8);
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
ANALYZE TABLE t1 UPDATE HISTOGRAM ON col1 WITH 2 BUCKETS;
Table Op Msg_type Msg_text
test.t1 histogram status Histogram statistics created for column 'col1'.
EXPLAIN SELECT * FROM t1 WHERE col1 > 2 AND col1 <= 7;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 8 65.62 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where ((`test`.`t1`.`col1` > 2) and (`test`.`t1`.`col1` <= 7))
# Selectivity estimations for each conditions.
SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
JSON_EXTRACT(trace, @path)
[[{"condition": "(`t1`.`col1` > 2)", "histogram_selectivity": 0.75}, {"condition": "(`t1`.`col1` <= 7)", "histogram_selectivity": 0.875}]]
# Total calculated selectivity estimation.
SELECT JSON_EXTRACT(trace, "$**.final_filtering_effect")
FROM information_schema.OPTIMIZER_TRACE;
JSON_EXTRACT(trace, "$**.final_filtering_effect")
[0.65625]
DROP TABLE t1;
|
