Debian Codegraph @

mysql-test/suite/opt_trace/r/histograms.result

raw
1 SET optimizer_trace_max_mem_size = 1000000;
2 SET optimizer_trace="enabled=on";
3 SET @path = "$**.filtering_effect";
4 CREATE TABLE t1 (col1 INT, col2 INT);
5 INSERT INTO t1 VALUES (1, 10), (2, 20), (3, 30), (4, 40), (5, 50), (NULL, NULL);
6 ANALYZE TABLE t1 UPDATE HISTOGRAM ON col1, col2 WITH 2 BUCKETS;
7 Table Op Msg_type Msg_text
8 test.t1 histogram status Histogram statistics created for column 'col1'.
9 test.t1 histogram status Histogram statistics created for column 'col2'.
10 UPDATE mysql.innodb_table_stats SET n_rows = 6
11 WHERE database_name = "test" AND table_name = "t1";
12 EXPLAIN SELECT * FROM t1 WHERE col1 < 4;
13 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
14 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 50.00 Using where
15 Warnings:
16 Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col1` < 4)
17 SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
18 JSON_EXTRACT(trace, @path)
19 [[{"condition": "(`t1`.`col1` < 4)", "histogram_selectivity": 0.5}]]
20 EXPLAIN SELECT * FROM t1 WHERE col1 > 4;
21 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
22 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 16.67 Using where
23 Warnings:
24 Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col1` > 4)
25 SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
26 JSON_EXTRACT(trace, @path)
27 [[{"condition": "(`t1`.`col1` > 4)", "histogram_selectivity": 0.166667}]]
28 EXPLAIN SELECT * FROM t1 WHERE col1 >= 4;
29 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
30 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 33.33 Using where
31 Warnings:
32 Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col1` >= 4)
33 SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
34 JSON_EXTRACT(trace, @path)
35 [[{"condition": "(`t1`.`col1` >= 4)", "histogram_selectivity": 0.333333}]]
36 EXPLAIN SELECT * FROM t1 WHERE col1 <= 4;
37 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
38 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 66.67 Using where
39 Warnings:
40 Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col1` <= 4)
41 SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
42 JSON_EXTRACT(trace, @path)
43 [[{"condition": "(`t1`.`col1` <= 4)", "histogram_selectivity": 0.666667}]]
44 EXPLAIN SELECT * FROM t1 WHERE col1 = 4;
45 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
46 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 16.67 Using where
47 Warnings:
48 Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col1` = 4)
49 SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
50 JSON_EXTRACT(trace, @path)
51 [[{"condition": "(`t1`.`col1` = 4)", "histogram_selectivity": 0.166667}]]
52 EXPLAIN SELECT * FROM t1 WHERE col1 != 4;
53 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
54 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 66.67 Using where
55 Warnings:
56 Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col1` <> 4)
57 SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
58 JSON_EXTRACT(trace, @path)
59 [[{"condition": "(`t1`.`col1` <> 4)", "histogram_selectivity": 0.666667}]]
60 EXPLAIN SELECT * FROM t1 WHERE col1 <> 4;
61 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
62 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 66.67 Using where
63 Warnings:
64 Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col1` <> 4)
65 SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
66 JSON_EXTRACT(trace, @path)
67 [[{"condition": "(`t1`.`col1` <> 4)", "histogram_selectivity": 0.666667}]]
68 EXPLAIN SELECT * FROM t1 WHERE col1 BETWEEN 4 AND 6;
69 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
70 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 33.33 Using where
71 Warnings:
72 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)
73 SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
74 JSON_EXTRACT(trace, @path)
75 [[{"condition": "(`t1`.`col1` between 4 and 6)", "histogram_selectivity": 0.333333}]]
76 EXPLAIN SELECT * FROM t1 WHERE col1 NOT BETWEEN 4 AND 6;
77 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
78 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 50.00 Using where
79 Warnings:
80 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)
81 SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
82 JSON_EXTRACT(trace, @path)
83 [[{"condition": "(`t1`.`col1` not between 4 and 6)", "histogram_selectivity": 0.5}]]
84 EXPLAIN SELECT * FROM t1 WHERE col1 IS NULL;
85 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
86 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 16.67 Using where
87 Warnings:
88 Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col1` is null)
89 SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
90 JSON_EXTRACT(trace, @path)
91 [[{"condition": "(`t1`.`col1` is null)", "histogram_selectivity": 0.166667}]]
92 EXPLAIN SELECT * FROM t1 WHERE col1 IS NOT NULL;
93 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
94 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 83.33 Using where
95 Warnings:
96 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)
97 SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
98 JSON_EXTRACT(trace, @path)
99 [[{"condition": "(`t1`.`col1` is not null)", "histogram_selectivity": 0.833333}]]
100 DROP TABLE t1;
101 #
102 # Show that histogram selecitivty estimation is printed for other acecss
103 # methods as well.
104 #
105 # Access method: RANGE
106 CREATE TABLE t1 (col1 INT, col2 INT);
107 INSERT INTO t1 VALUES (15, 84), (19, 51), (70, 52), (54, 82), (33, 93), (80, 0),
108 (51, 82), (50, 14), (22, 27), (96, 82), (96, 3), (86, 61), (4, 58), (95, 20),
109 (28, 56), (64, 99), (10, 85), (30, 81), (13, 85), (28, 94);
110 CREATE INDEX idx1 ON t1 (col1, col2);
111 ANALYZE TABLE t1;
112 Table Op Msg_type Msg_text
113 test.t1 analyze status OK
114 ANALYZE TABLE t1 UPDATE HISTOGRAM ON col1, col2 WITH 8 BUCKETS;
115 Table Op Msg_type Msg_text
116 test.t1 histogram status Histogram statistics created for column 'col1'.
117 test.t1 histogram status Histogram statistics created for column 'col2'.
118 EXPLAIN SELECT * FROM t1 WHERE col1 > 10 AND col2 < 88;
119 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
120 1 SIMPLE t1 NULL range idx1 idx1 5 NULL 18 85.00 Using where; Using index
121 Warnings:
122 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))
123 SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
124 JSON_EXTRACT(trace, @path)
125 [[{"condition": "(`t1`.`col2` < 88)", "histogram_selectivity": 0.85}]]
126 DROP TABLE t1;
127 #
128 # Show the difference between the calculated selectivity estimation for
129 # each predicate and the total calculated selectivity estimation
130 #
131 CREATE TABLE t1 (col1 INT);
132 INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8);
133 ANALYZE TABLE t1;
134 Table Op Msg_type Msg_text
135 test.t1 analyze status OK
136 ANALYZE TABLE t1 UPDATE HISTOGRAM ON col1 WITH 2 BUCKETS;
137 Table Op Msg_type Msg_text
138 test.t1 histogram status Histogram statistics created for column 'col1'.
139 EXPLAIN SELECT * FROM t1 WHERE col1 > 2 AND col1 <= 7;
140 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
141 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 8 65.62 Using where
142 Warnings:
143 Note 1003 /* select#1 */ select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where ((`test`.`t1`.`col1` > 2) and (`test`.`t1`.`col1` <= 7))
144 # Selectivity estimations for each conditions.
145 SELECT JSON_EXTRACT(trace, @path) FROM information_schema.OPTIMIZER_TRACE;
146 JSON_EXTRACT(trace, @path)
147 [[{"condition": "(`t1`.`col1` > 2)", "histogram_selectivity": 0.75}, {"condition": "(`t1`.`col1` <= 7)", "histogram_selectivity": 0.875}]]
148 # Total calculated selectivity estimation.
149 SELECT JSON_EXTRACT(trace, "$**.final_filtering_effect")
150 FROM information_schema.OPTIMIZER_TRACE;
151 JSON_EXTRACT(trace, "$**.final_filtering_effect")
152 [0.65625]
153 DROP TABLE t1;