| 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; |