Debian Codegraph @

mysql-test/suite/secondary_engine/r/index_statistics.result

raw
1 CREATE TABLE t1(id INT PRIMARY KEY, x INT, y INT, KEY (x))
2 SECONDARY_ENGINE MOCK;
3 INSERT INTO t1 VALUES
4 (1, 1, 1),
5 (2, 1, 1),
6 (3, 1, 1),
7 (4, 1, 1),
8 (5, 1, 1),
9 (6, 1, 1),
10 (7, 1, 1),
11 (8, 2, 2),
12 (9, 2, 2),
13 (10, 3, 3);
14 ALTER TABLE t1 SECONDARY_LOAD;
15 ANALYZE TABLE t1;
16 Table Op Msg_type Msg_text
17 test.t1 analyze status OK
18 # Uses the primary index to determine 10% filtering effect (one row).
19 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id = 1;
20 EXPLAIN
21 -> Filter: (t1.id = 1) (rows=1)
22 -> Table scan on t1 in secondary engine MOCK (rows=10)
23
24 Warnings:
25 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
26 # Uses the secondary index to determine 70% filtering effect (matches
27 # rows 1 to 7).
28 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 1;
29 EXPLAIN
30 -> Filter: (t1.x = 1) (rows=7)
31 -> Table scan on t1 in secondary engine MOCK (rows=10)
32
33 Warnings:
34 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
35 # Uses the secondary index to determine 20% filtering effect (matches
36 # rows 8 and 9).
37 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 2;
38 EXPLAIN
39 -> Filter: (t1.x = 2) (rows=2)
40 -> Table scan on t1 in secondary engine MOCK (rows=10)
41
42 Warnings:
43 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
44 # Uses the secondary index to determine 10% filtering effect (matches
45 # row 10).
46 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 3;
47 EXPLAIN
48 -> Filter: (t1.x = 3) (rows=1)
49 -> Table scan on t1 in secondary engine MOCK (rows=10)
50
51 Warnings:
52 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
53 # No index on y, so a heuristic is used for the filtering effect.
54 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE y = 1;
55 EXPLAIN
56 -> Filter: (t1.y = 1) (rows=1)
57 -> Table scan on t1 in secondary engine MOCK (rows=10)
58
59 Warnings:
60 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
61 # Ignoring indexes cause heuristics to be used.
62 EXPLAIN FORMAT=TREE SELECT * FROM t1 USE INDEX () WHERE id = 1;
63 EXPLAIN
64 -> Filter: (t1.id = 1) (rows=1)
65 -> Table scan on t1 in secondary engine MOCK (rows=10)
66
67 Warnings:
68 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
69 EXPLAIN FORMAT=TREE SELECT * FROM t1 USE INDEX () WHERE x = 1;
70 EXPLAIN
71 -> Filter: (t1.x = 1) (rows=1)
72 -> Table scan on t1 in secondary engine MOCK (rows=10)
73
74 Warnings:
75 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
76 EXPLAIN FORMAT=TREE SELECT * FROM t1 IGNORE INDEX (PRIMARY) WHERE id = 1;
77 EXPLAIN
78 -> Filter: (t1.id = 1) (rows=1)
79 -> Table scan on t1 in secondary engine MOCK (rows=10)
80
81 Warnings:
82 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
83 EXPLAIN FORMAT=TREE SELECT * FROM t1 IGNORE INDEX (x) WHERE x = 1;
84 EXPLAIN
85 -> Filter: (t1.x = 1) (rows=1)
86 -> Table scan on t1 in secondary engine MOCK (rows=10)
87
88 Warnings:
89 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
90 # No matching rows. Rounds up estimates to one matching row (10%).
91 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id = 100;
92 EXPLAIN
93 -> Filter: (t1.id = 100) (rows=1)
94 -> Table scan on t1 in secondary engine MOCK (rows=10)
95
96 Warnings:
97 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
98 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 100;
99 EXPLAIN
100 -> Filter: (t1.x = 100) (rows=1)
101 -> Table scan on t1 in secondary engine MOCK (rows=10)
102
103 Warnings:
104 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
105 # Matches two rows in the primary index (20%).
106 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id = 3 OR id = 7;
107 EXPLAIN
108 -> Filter: ((t1.id = 3) or (t1.id = 7)) (rows=2)
109 -> Table scan on t1 in secondary engine MOCK (rows=10)
110
111 Warnings:
112 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
113 # Matches three rows in the secondary index (30%).
114 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 2 OR x = 3;
115 EXPLAIN
116 -> Filter: ((t1.x = 2) or (t1.x = 3)) (rows=3)
117 -> Table scan on t1 in secondary engine MOCK (rows=10)
118
119 Warnings:
120 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
121 # Matches all rows in the secondary index (100%).
122 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 1 OR x = 2 OR x = 3;
123 EXPLAIN
124 -> Filter: ((t1.x = 1) or (t1.x = 2) or (t1.x = 3)) (rows=10)
125 -> Table scan on t1 in secondary engine MOCK (rows=10)
126
127 Warnings:
128 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
129 # Ranges in the primary index.
130 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id > 0;
131 EXPLAIN
132 -> Filter: (t1.id > 0) (rows=10)
133 -> Table scan on t1 in secondary engine MOCK (rows=10)
134
135 Warnings:
136 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
137 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id > 1;
138 EXPLAIN
139 -> Filter: (t1.id > 1) (rows=9)
140 -> Table scan on t1 in secondary engine MOCK (rows=10)
141
142 Warnings:
143 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
144 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id > 3;
145 EXPLAIN
146 -> Filter: (t1.id > 3) (rows=7)
147 -> Table scan on t1 in secondary engine MOCK (rows=10)
148
149 Warnings:
150 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
151 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id > 9;
152 EXPLAIN
153 -> Filter: (t1.id > 9) (rows=1)
154 -> Table scan on t1 in secondary engine MOCK (rows=10)
155
156 Warnings:
157 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
158 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id > 10;
159 EXPLAIN
160 -> Filter: (t1.id > 10) (rows=1)
161 -> Table scan on t1 in secondary engine MOCK (rows=10)
162
163 Warnings:
164 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
165 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id < 1;
166 EXPLAIN
167 -> Filter: (t1.id < 1) (rows=1)
168 -> Table scan on t1 in secondary engine MOCK (rows=10)
169
170 Warnings:
171 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
172 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id < 3;
173 EXPLAIN
174 -> Filter: (t1.id < 3) (rows=2)
175 -> Table scan on t1 in secondary engine MOCK (rows=10)
176
177 Warnings:
178 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
179 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id < 10;
180 EXPLAIN
181 -> Filter: (t1.id < 10) (rows=9)
182 -> Table scan on t1 in secondary engine MOCK (rows=10)
183
184 Warnings:
185 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
186 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id BETWEEN 3 AND 7;
187 EXPLAIN
188 -> Filter: (t1.id between 3 and 7) (rows=5)
189 -> Table scan on t1 in secondary engine MOCK (rows=10)
190
191 Warnings:
192 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
193 # Ranges in the secondary index.
194 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x < 1;
195 EXPLAIN
196 -> Filter: (t1.x < 1) (rows=1)
197 -> Table scan on t1 in secondary engine MOCK (rows=10)
198
199 Warnings:
200 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
201 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x < 2;
202 EXPLAIN
203 -> Filter: (t1.x < 2) (rows=7)
204 -> Table scan on t1 in secondary engine MOCK (rows=10)
205
206 Warnings:
207 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
208 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x < 3;
209 EXPLAIN
210 -> Filter: (t1.x < 3) (rows=9)
211 -> Table scan on t1 in secondary engine MOCK (rows=10)
212
213 Warnings:
214 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
215 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x < 4;
216 EXPLAIN
217 -> Filter: (t1.x < 4) (rows=10)
218 -> Table scan on t1 in secondary engine MOCK (rows=10)
219
220 Warnings:
221 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
222 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x BETWEEN 1 AND 2;
223 EXPLAIN
224 -> Filter: (t1.x between 1 and 2) (rows=9)
225 -> Table scan on t1 in secondary engine MOCK (rows=10)
226
227 Warnings:
228 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
229 # Indexes should only be used during optimization. Verify that the
230 # execution plan for this query does not use DYNAMIC RANGE access.
231 # (No occurrence of "Range checked for each record" in the plan.)
232 EXPLAIN FORMAT=TREE SELECT * FROM t1 a, t1 b WHERE a.id < b.id;
233 EXPLAIN
234 -> Filter: (a.id < b.id) (rows=33.3)
235 -> Inner hash join (no condition) (rows=33.3)
236 -> Table scan on b in secondary engine MOCK (rows=10)
237 -> Hash
238 -> Table scan on a in secondary engine MOCK (rows=10)
239
240 Warnings:
241 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
242 DROP TABLE t1;
243 # Getting range statistics from spatial indexes is not supported,
244 # since spatial indexes are not ordered.
245 CREATE TABLE geom(g GEOMETRY NOT NULL SRID 0, SPATIAL INDEX(g))
246 SECONDARY_ENGINE MOCK;
247 INSERT INTO geom VALUES (POINT(1, 1)), (POINT(2, 2)), (POINT(3, 3));
248 ALTER TABLE geom SECONDARY_LOAD;
249 ANALYZE TABLE geom;
250 Table Op Msg_type Msg_text
251 test.geom analyze status OK
252 EXPLAIN FORMAT=TREE SELECT ST_AsText(g) FROM geom
253 WHERE MBRContains(POLYGON(LINESTRING(POINT(0,0), POINT(3,0),
254 POINT(3,3), POINT(0,0))),
255 g);
256 EXPLAIN
257 -> Filter: mbrcontains(<cache>(polygon(linestring(point(0,0),point(3,0),point(3,3),point(0,0)))),geom.g) (rows=3)
258 -> Table scan on geom in secondary engine MOCK (rows=3)
259
260 Warnings:
261 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
262 DROP TABLE geom;