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
|
--echo #
--echo # BASIC CASES
--echo #
EXPLAIN SELECT * FROM t1 WHERE t1.b <> 10;
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
EXPLAIN SELECT * FROM t1 WHERE t1.b > 10;
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
EXPLAIN SELECT * FROM t1 WHERE t1.b < 10;
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
EXPLAIN SELECT * FROM t1 WHERE t1.b = 10;
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
EXPLAIN SELECT * FROM t1 WHERE t1.b+2 = 10;
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
EXPLAIN SELECT * FROM t1 WHERE a IN (1,2,3);
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 and 5;
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
--echo # Equi-join condition
EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.b;
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
--echo # CASE where selectivity estimates are not accurate
EXPLAIN SELECT * FROM t1 WHERE abs(t1.b) = 10;
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
--echo #
--echo # The cardinality here would be not accurate because we don't have
--echo # selectivity for the predicate t1.b=10
--echo #
EXPLAIN SELECT * FROM t1 WHERE t1.c = 10;
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
--echo #
--echo # BASIC CASES FOR AND CONJUNCT
--echo #
EXPLAIN SELECT * FROM t1 WHERE a >= 5 and b <= 6;
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 AND b <= 6;
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 AND t2.b < 10;
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
--echo #
--echo # BASIC CASES FOR OR CONJUNCT
--echo #
EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR a <= 2;
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 OR a <= 0;
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
--echo # The cardinality here would be not accurate because for the
--echo # OR conjunct all predicates can't be resolved to one column
EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 OR t2.b < 10;
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR b <= 6;
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
--echo #
--echo # COMPLEX CASES WITH AND-OR CONJUNCTS
--echo #
EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t1.a < 10);
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.b < 10);
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.a < 10);
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
EXPLAIN SELECT * FROM t1
WHERE ((t1.a > 20 and t1.a < 30) or (t1.a > 40 and t1.a < 50)) AND
((t1.a > 25 and t1.a < 35) or (t1.a > 45 and t1.a < 55)) OR
((t1.a > 120 and t1.a < 130) or (t1.a > 140 and t1.a < 150)) AND
((t1.a > 125 and t1.a < 135) or (t1.a > 145 and t1.a < 155));
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
EXPLAIN EXTENDED SELECT * from t1 WHERE ((t1.a IS NULL) or (t1.a = 5));
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
--echo #
--echo # Test with subquery
--echo #
EXPLAIN SELECT * from t1 WHERE t1.a = (select t2.a from t2 limit 1);
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
EXPLAIN SELECT * from t1
WHERE t1.a = (select t2.a from t2 where t1.b=t2.b limit 1);
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|