Debian Codegraph @

mysql-test/suite/ndb/r/ndb_read_location_domain.result

raw
1 use test;
2 Test read-locality of READ-BACKUP
3 create table test.t1 (a int primary key,
4 b int,
5 c int,
6 d int,
7 unique(b) using hash,
8 key(c),
9 key(a,c))
10 comment="NDB_TABLE=READ_BACKUP=1"
11 engine=ndb;
12 Warnings:
13 Warning 1121 Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan
14 create table test.t2 (a int primary key,
15 b int,
16 c int,
17 d text,
18 unique(b) using hash,
19 key(c))
20 comment="NDB_TABLE=READ_BACKUP=1"
21 engine=ndb;
22 Warnings:
23 Warning 1121 Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan
24 select id into @t1_id from ndbinfo.ndb$dict_obj_info where fq_name = 'test/def/t1';
25 select id into @t1ui_id from ndbinfo.ndb$dict_obj_info where parent_obj_id = @t1_id and type = 3;
26 select id into @t2_id from ndbinfo.ndb$dict_obj_info where fq_name = 'test/def/t2';
27 select id into @t2ui_id from ndbinfo.ndb$dict_obj_info where parent_obj_id = @t2_id and type = 3;
28 select id into @t2bt_id from ndbinfo.ndb$dict_obj_info where parent_obj_id = @t2_id and fq_name like 'test/def/NDB$BLOB%';
29 Put some data in.
30 Make it more interesting for joining
31 update test.t1 set c=c+1, d=d+1;
32 select count(distinct node_id) into @nodes
33 from ndbinfo.ndb$membership where group_id < 0xFFFFFF00;
34 Check replicas
35 select count(distinct node_id)
36 from ndbinfo.operations_per_fragment
37 where fq_name='test/def/t1'
38 and fragment_num=0;
39 count(distinct node_id)
40 2
41 Check nodes with table data
42 select count(distinct node_id)
43 from ndbinfo.operations_per_fragment
44 where fq_name='test/def/t1';
45 count(distinct node_id)
46 2
47 Check UI replicas
48 select count(distinct node_id)
49 from ndbinfo.operations_per_fragment
50 where table_id = @t1ui_id
51 and fragment_num=0;
52 count(distinct node_id)
53 2
54 Check nodes with UI data
55 select count(distinct node_id)
56 from ndbinfo.operations_per_fragment
57 where table_id = @t1ui_id;
58 count(distinct node_id)
59 2
60 Put some data in.
61 insert into test.t2 select a,b,c,repeat('BJC',300) from test.t1;
62 Check blob table replicas
63 select count(distinct node_id)
64 from ndbinfo.operations_per_fragment
65 where table_id = @t2bt_id
66 and fragment_num=0;
67 count(distinct node_id)
68 2
69 Check nodes with blob data
70 select count(distinct node_id)
71 from ndbinfo.operations_per_fragment
72 where table_id = @t2bt_id;
73 count(distinct node_id)
74 2
75 set @partitions = 'PARTITION p2 VALUES IN (2)';
76 set @partitions = concat(@partitions,',PARTITION p1 VALUES IN (1)');
77 set @@new = 1;
78 Warnings:
79 Warning 1287 '@@new' is deprecated and will be removed in a future release.
80 create table hints (i int primary key) engine=ndb partition by list(i) (PARTITION p2 VALUES IN (2),PARTITION p1 VALUES IN (1));
81 insert into hints values (2);
82 Hint node by 2 of 2
83 Tests on T1 (!Blobs)
84 Test some basic NdbApi
85 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
86 from ndbinfo.operations_per_fragment
87 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
88 (tot_key_reads > 0 or tot_frag_scans > 0)
89 group by parent_fq_name, fq_name, type, node_id;
90 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
91 NULL test/def/t1 User table 1 0 8
92 NULL test/def/t1 User table 2 0 24
93 PK lookups
94 explain select * from test.t1 where t1.a=6;
95 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
96 1 SIMPLE t1 # eq_ref PRIMARY,a PRIMARY 4 const # # NULL
97 Warnings:
98 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (`test`.`t1`.`a` = 6)
99 select node_id, counter_name, sum(val) from ndbinfo.counters
100 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
101 group by node_id, counter_name;
102 node_id counter_name sum(val)
103 1 READS 16
104 1 LOCAL_READS 3
105 2 READS 5
106 2 LOCAL_READS 3
107 3 READS 1
108 3 LOCAL_READS 0
109 4 READS 4
110 4 LOCAL_READS 0
111 5 READS 4
112 5 LOCAL_READS 0
113 6 READS 6
114 6 LOCAL_READS 0
115 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
116 from ndbinfo.operations_per_fragment
117 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
118 (tot_key_reads > 0 or tot_frag_scans > 0)
119 group by parent_fq_name, fq_name, type, node_id;
120 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
121 NULL test/def/t1 User table 1 0 8
122 NULL test/def/t1 User table 2 40 24
123 select node_id, counter_name, sum(val) from ndbinfo.counters
124 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
125 group by node_id, counter_name;
126 node_id counter_name sum(val)
127 1 READS 16
128 1 LOCAL_READS 3
129 2 READS 45
130 2 LOCAL_READS 43
131 3 READS 1
132 3 LOCAL_READS 0
133 4 READS 4
134 4 LOCAL_READS 0
135 5 READS 4
136 5 LOCAL_READS 0
137 6 READS 6
138 6 LOCAL_READS 0
139 UI lookups
140 explain select * from test.t1 where t1.b=6;
141 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
142 1 SIMPLE t1 # ref b b 5 const # # NULL
143 Warnings:
144 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (`test`.`t1`.`b` = 6)
145 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
146 from ndbinfo.operations_per_fragment
147 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
148 (tot_key_reads > 0 or tot_frag_scans > 0)
149 group by parent_fq_name, fq_name, type, node_id;
150 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
151 test/def/t1 sys/def/10/b$unique Unique hash index 2 40 0
152 NULL test/def/t1 User table 1 0 8
153 NULL test/def/t1 User table 2 80 24
154 select node_id, counter_name, sum(val) from ndbinfo.counters
155 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
156 group by node_id, counter_name;
157 node_id counter_name sum(val)
158 1 READS 16
159 1 LOCAL_READS 3
160 2 READS 125
161 2 LOCAL_READS 123
162 3 READS 1
163 3 LOCAL_READS 0
164 4 READS 4
165 4 LOCAL_READS 0
166 5 READS 4
167 5 LOCAL_READS 0
168 6 READS 6
169 6 LOCAL_READS 0
170 OI scan
171 explain select * from test.t1 where c > 6;
172 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
173 1 SIMPLE t1 # range c c 5 NULL # # Using pushed condition (`test`.`t1`.`c` > 6); Using MRR
174 Warnings:
175 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (`test`.`t1`.`c` > 6)
176 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
177 from ndbinfo.operations_per_fragment
178 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
179 (tot_key_reads > 0 or tot_frag_scans > 0)
180 group by parent_fq_name, fq_name, type, node_id;
181 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
182 test/def/t1 sys/def/10/b$unique Unique hash index 2 40 0
183 test/def/t1 sys/def/10/c Ordered index 2 0 8
184 NULL test/def/t1 User table 1 0 8
185 NULL test/def/t1 User table 2 80 24
186 Table scan
187 explain select * from test.t1;
188 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
189 1 SIMPLE t1 # ALL NULL NULL NULL NULL # # NULL
190 Warnings:
191 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1`
192 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
193 from ndbinfo.operations_per_fragment
194 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
195 (tot_key_reads > 0 or tot_frag_scans > 0)
196 group by parent_fq_name, fq_name, type, node_id;
197 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
198 test/def/t1 sys/def/10/b$unique Unique hash index 2 40 0
199 test/def/t1 sys/def/10/c Ordered index 2 0 8
200 NULL test/def/t1 User table 1 0 8
201 NULL test/def/t1 User table 2 80 32
202 Test pushed queries (SPJ)
203 Pushed pk->pk
204 Lookup to get a.d, feeding pk lookup of b.a
205 explain select a.a, a.d, b.d
206 from
207 test.t1 a
208 join
209 test.t1 b
210 on
211 a.d = b.a
212 where a.a = 6;
213 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
214 1 SIMPLE a # eq_ref PRIMARY,a PRIMARY 4 const # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
215 1 SIMPLE b # eq_ref PRIMARY,a PRIMARY 4 test.a.d # # Child of 'a' in pushed join@1
216 Warnings:
217 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`a` = `test`.`a`.`d`) and (`test`.`a`.`a` = 6))
218 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
219 from ndbinfo.operations_per_fragment
220 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
221 (tot_key_reads > 0 or tot_frag_scans > 0)
222 group by parent_fq_name, fq_name, type, node_id;
223 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
224 test/def/t1 sys/def/10/b$unique Unique hash index 2 40 0
225 test/def/t1 sys/def/10/c Ordered index 2 0 8
226 NULL test/def/t1 User table 1 0 8
227 NULL test/def/t1 User table 2 160 40
228 Pushed uk->uk
229 Lookup to get a.d, feeding uk lookup of b.b
230 explain select a.a, a.d, b.d
231 from
232 test.t1 a
233 join
234 test.t1 b
235 on
236 a.d = b.b
237 where a.b = 6;
238 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
239 1 SIMPLE a # ref b b 5 const # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
240 1 SIMPLE b # ref b b 5 test.a.d # # Child of 'a' in pushed join@1
241 Warnings:
242 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`b` = `test`.`a`.`d`) and (`test`.`a`.`b` = 6))
243 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
244 from ndbinfo.operations_per_fragment
245 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
246 (tot_key_reads > 0 or tot_frag_scans > 0)
247 group by parent_fq_name, fq_name, type, node_id;
248 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
249 test/def/t1 sys/def/10/b$unique Unique hash index 2 120 0
250 test/def/t1 sys/def/10/c Ordered index 2 0 8
251 NULL test/def/t1 User table 1 0 8
252 NULL test/def/t1 User table 2 239 40
253 Pushed uk->pk
254 Lookup to get a.d, feeding pk lookup of b.a
255 explain select a.a, a.d, b.d
256 from
257 test.t1 a
258 join
259 test.t1 b
260 on
261 a.d = b.a
262 where a.b = 6;
263 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
264 1 SIMPLE a # ref b b 5 const # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
265 1 SIMPLE b # eq_ref PRIMARY,a PRIMARY 4 test.a.d # # Child of 'a' in pushed join@1
266 Warnings:
267 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`a` = `test`.`a`.`d`) and (`test`.`a`.`b` = 6))
268 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
269 from ndbinfo.operations_per_fragment
270 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
271 (tot_key_reads > 0 or tot_frag_scans > 0)
272 group by parent_fq_name, fq_name, type, node_id;
273 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
274 test/def/t1 sys/def/10/b$unique Unique hash index 2 160 0
275 test/def/t1 sys/def/10/c Ordered index 2 0 8
276 NULL test/def/t1 User table 1 0 8
277 NULL test/def/t1 User table 2 319 40
278 Pushed pk->uk
279 Lookup to get a.d, feeding uk lookup of b.b
280 explain select a.a, a.d, b.d
281 from
282 test.t1 a
283 join
284 test.t1 b
285 on
286 a.d = b.b
287 where a.a = 6;
288 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
289 1 SIMPLE a # eq_ref PRIMARY,a PRIMARY 4 const # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
290 1 SIMPLE b # ref b b 5 test.a.d # # Child of 'a' in pushed join@1
291 Warnings:
292 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`b` = `test`.`a`.`d`) and (`test`.`a`.`a` = 6))
293 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
294 from ndbinfo.operations_per_fragment
295 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
296 (tot_key_reads > 0 or tot_frag_scans > 0)
297 group by parent_fq_name, fq_name, type, node_id;
298 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
299 test/def/t1 sys/def/10/b$unique Unique hash index 2 200 0
300 test/def/t1 sys/def/10/c Ordered index 2 0 8
301 NULL test/def/t1 User table 1 0 8
302 NULL test/def/t1 User table 2 398 40
303 Pushed scan originating sub-lookups
304 Table scan to get a.d, feeding pk lookups of b.a
305 explain select a.a, a.d, b.d
306 from
307 test.t1 a
308 join
309 test.t1 b
310 on
311 a.d = b.a;
312 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
313 1 SIMPLE a # ALL NULL NULL NULL NULL # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
314 1 SIMPLE b # eq_ref PRIMARY,a PRIMARY 4 test.a.d # # Child of 'a' in pushed join@1
315 Warnings:
316 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where (`test`.`b`.`a` = `test`.`a`.`d`)
317 select a.a, a.d, b.d
318 from
319 test.t1 a
320 join
321 test.t1 b
322 on
323 a.d = b.a;
324 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
325 from ndbinfo.operations_per_fragment
326 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
327 (tot_key_reads > 0 or tot_frag_scans > 0)
328 group by parent_fq_name, fq_name, type, node_id;
329 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
330 test/def/t1 sys/def/10/b$unique Unique hash index 2 200 0
331 test/def/t1 sys/def/10/c Ordered index 2 0 8
332 NULL test/def/t1 User table 1 0 8
333 NULL test/def/t1 User table 2 438 48
334 Pushed scan originating sub-lookups
335 Table scan to get a.d, feeding uk lookups of b.b
336 explain select a.a, a.d, b.d
337 from
338 test.t1 a
339 join
340 test.t1 b
341 on
342 a.d = b.b;
343 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
344 1 SIMPLE a # ALL NULL NULL NULL NULL # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
345 1 SIMPLE b # ref b b 5 test.a.d # # Child of 'a' in pushed join@1
346 Warnings:
347 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where (`test`.`b`.`b` = `test`.`a`.`d`)
348 select a.a, a.d, b.d
349 from
350 test.t1 a
351 join
352 test.t1 b
353 on
354 a.d = b.b;
355 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
356 from ndbinfo.operations_per_fragment
357 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
358 (tot_key_reads > 0 or tot_frag_scans > 0)
359 group by parent_fq_name, fq_name, type, node_id;
360 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
361 test/def/t1 sys/def/10/b$unique Unique hash index 2 240 0
362 test/def/t1 sys/def/10/c Ordered index 2 0 8
363 NULL test/def/t1 User table 1 0 8
364 NULL test/def/t1 User table 2 477 56
365 Pushed scan originating sub-scans
366 Range scan on a.a > 10 getting a.d, feeding range scans on b.c = a.d
367 explain select a.a, a.d, b.d
368 from
369 test.t1 a
370 join
371 test.t1 b
372 on
373 b.c=a.d
374 where
375 a.a>10;
376 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
377 1 SIMPLE a # range PRIMARY,a PRIMARY 4 NULL # # Parent of 2 pushed join@1; Using pushed condition ((`test`.`a`.`a` > 10) and (`test`.`a`.`d` is not null)); Using MRR
378 1 SIMPLE b # ref c c 5 test.a.d # # Child of 'a' in pushed join@1
379 Warnings:
380 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`c` = `test`.`a`.`d`) and (`test`.`a`.`a` > 10))
381 select a.a, a.d, b.d
382 from
383 test.t1 a
384 join
385 test.t1 b
386 on
387 b.c=a.d
388 where
389 a.a>10;
390 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
391 from ndbinfo.operations_per_fragment
392 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
393 (tot_key_reads > 0 or tot_frag_scans > 0)
394 group by parent_fq_name, fq_name, type, node_id;
395 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
396 test/def/t1 sys/def/10/b$unique Unique hash index 2 240 0
397 test/def/t1 sys/def/10/c Ordered index 2 0 16
398 test/def/t1 sys/def/10/PRIMARY Ordered index 2 0 8
399 NULL test/def/t1 User table 1 0 8
400 NULL test/def/t1 User table 2 477 56
401 Tests on T2 (Blobs)
402 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
403 from ndbinfo.operations_per_fragment
404 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
405 (tot_key_reads > 0 or tot_frag_scans > 0)
406 group by parent_fq_name, fq_name, type, node_id;
407 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
408 NULL test/def/t2 User table 1 0 4
409 NULL test/def/t2 User table 2 0 12
410 select node_id, counter_name, sum(val) from ndbinfo.counters
411 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
412 group by node_id, counter_name;
413 node_id counter_name sum(val)
414 1 READS 16
415 1 LOCAL_READS 3
416 2 READS 285
417 2 LOCAL_READS 283
418 3 READS 1
419 3 LOCAL_READS 0
420 4 READS 4
421 4 LOCAL_READS 0
422 5 READS 4
423 5 LOCAL_READS 0
424 6 READS 6
425 6 LOCAL_READS 0
426 Test some basic NdbApi
427 explain select * from test.t2 where t2.a=6;
428 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
429 1 SIMPLE t2 # eq_ref PRIMARY PRIMARY 4 const # # NULL
430 Warnings:
431 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`a` = 6)
432 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
433 from ndbinfo.operations_per_fragment
434 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
435 (tot_key_reads > 0 or tot_frag_scans > 0)
436 group by parent_fq_name, fq_name, type, node_id;
437 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
438 test/def/t2 test/def/NDB$BLOB_15_3 User table 2 40 0
439 NULL test/def/t2 User table 1 0 4
440 NULL test/def/t2 User table 2 80 12
441 select node_id, counter_name, sum(val) from ndbinfo.counters
442 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
443 group by node_id, counter_name;
444 node_id counter_name sum(val)
445 1 READS 16
446 1 LOCAL_READS 3
447 2 READS 367
448 2 LOCAL_READS 364
449 3 READS 1
450 3 LOCAL_READS 0
451 4 READS 4
452 4 LOCAL_READS 0
453 5 READS 4
454 5 LOCAL_READS 0
455 6 READS 6
456 6 LOCAL_READS 0
457 UI lookups
458 explain select * from test.t2 where t2.b=6;
459 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
460 1 SIMPLE t2 # ref b b 5 const # # NULL
461 Warnings:
462 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 6)
463 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
464 from ndbinfo.operations_per_fragment
465 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
466 (tot_key_reads > 0 or tot_frag_scans > 0)
467 group by parent_fq_name, fq_name, type, node_id;
468 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
469 test/def/t2 test/def/NDB$BLOB_15_3 User table 2 80 0
470 test/def/t2 sys/def/15/b$unique Unique hash index 2 80 0
471 NULL test/def/t2 User table 1 0 4
472 NULL test/def/t2 User table 2 160 12
473 select node_id, counter_name, sum(val) from ndbinfo.counters
474 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
475 group by node_id, counter_name;
476 node_id counter_name sum(val)
477 1 READS 16
478 1 LOCAL_READS 3
479 2 READS 567
480 2 LOCAL_READS 564
481 3 READS 1
482 3 LOCAL_READS 0
483 4 READS 4
484 4 LOCAL_READS 0
485 5 READS 4
486 5 LOCAL_READS 0
487 6 READS 6
488 6 LOCAL_READS 0
489 OI scan
490 explain select * from test.t2 where c > 6;
491 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
492 1 SIMPLE t2 # range c c 5 NULL # # Using pushed condition (`test`.`t2`.`c` > 6)
493 Warnings:
494 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`c` > 6)
495 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
496 from ndbinfo.operations_per_fragment
497 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
498 (tot_key_reads > 0 or tot_frag_scans > 0)
499 group by parent_fq_name, fq_name, type, node_id;
500 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
501 test/def/t2 test/def/NDB$BLOB_15_3 User table 2 115 0
502 test/def/t2 sys/def/15/b$unique Unique hash index 2 80 0
503 test/def/t2 sys/def/15/c Ordered index 2 0 8
504 NULL test/def/t2 User table 1 0 4
505 NULL test/def/t2 User table 2 160 12
506 Table scan
507 explain select * from test.t2;
508 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
509 1 SIMPLE t2 # ALL NULL NULL NULL NULL # # NULL
510 Warnings:
511 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`
512 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
513 from ndbinfo.operations_per_fragment
514 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
515 (tot_key_reads > 0 or tot_frag_scans > 0)
516 group by parent_fq_name, fq_name, type, node_id;
517 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
518 test/def/t2 test/def/NDB$BLOB_15_3 User table 2 155 0
519 test/def/t2 sys/def/15/b$unique Unique hash index 2 80 0
520 test/def/t2 sys/def/15/c Ordered index 2 0 8
521 NULL test/def/t2 User table 1 0 4
522 NULL test/def/t2 User table 2 160 20
523 drop table hints;
524 delete from t1;
525 delete from t2;
526 drop table test.t1;
527 drop table test.t2;
528 Test read-locality of FULLY-REPLICATED
529 create table test.t1 (a int primary key,
530 b int,
531 c int,
532 d int,
533 unique(b) using hash,
534 key(c),
535 key(a,c))
536 comment="NDB_TABLE=FULLY_REPLICATED=1"
537 engine=ndb;
538 Warnings:
539 Warning 1121 Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan
540 create table test.t2 (a int primary key,
541 b int,
542 c int,
543 d text,
544 unique(b) using hash,
545 key(c))
546 comment="NDB_TABLE=FULLY_REPLICATED=1"
547 engine=ndb;
548 Warnings:
549 Warning 1121 Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan
550 select id into @t1_id from ndbinfo.ndb$dict_obj_info where fq_name = 'test/def/t1';
551 select id into @t1ui_id from ndbinfo.ndb$dict_obj_info where parent_obj_id = @t1_id and type = 3;
552 select id into @t2_id from ndbinfo.ndb$dict_obj_info where fq_name = 'test/def/t2';
553 select id into @t2ui_id from ndbinfo.ndb$dict_obj_info where parent_obj_id = @t2_id and type = 3;
554 select id into @t2bt_id from ndbinfo.ndb$dict_obj_info where parent_obj_id = @t2_id and fq_name like 'test/def/NDB$BLOB%';
555 Put some data in.
556 Make it more interesting for joining
557 update test.t1 set c=c+1, d=d+1;
558 select count(distinct node_id) into @nodes
559 from ndbinfo.ndb$membership where group_id < 0xFFFFFF00;
560 Check replicas
561 select count(distinct node_id)
562 from ndbinfo.operations_per_fragment
563 where fq_name='test/def/t1'
564 and fragment_num=0;
565 count(distinct node_id)
566 2
567 Check nodes with table data
568 select count(distinct node_id)
569 from ndbinfo.operations_per_fragment
570 where fq_name='test/def/t1';
571 count(distinct node_id)
572 2
573 Check UI replicas
574 select count(distinct node_id)
575 from ndbinfo.operations_per_fragment
576 where table_id = @t1ui_id
577 and fragment_num=0;
578 count(distinct node_id)
579 2
580 Check nodes with UI data
581 select count(distinct node_id)
582 from ndbinfo.operations_per_fragment
583 where table_id = @t1ui_id;
584 count(distinct node_id)
585 2
586 Put some data in.
587 insert into test.t2 select a,b,c,repeat('BJC',300) from test.t1;
588 Check blob table replicas
589 select count(distinct node_id)
590 from ndbinfo.operations_per_fragment
591 where table_id = @t2bt_id
592 and fragment_num=0;
593 count(distinct node_id)
594 2
595 Check nodes with blob data
596 select count(distinct node_id)
597 from ndbinfo.operations_per_fragment
598 where table_id = @t2bt_id;
599 count(distinct node_id)
600 2
601 set @partitions = 'PARTITION p2 VALUES IN (2)';
602 set @partitions = concat(@partitions,',PARTITION p1 VALUES IN (1)');
603 set @@new = 1;
604 Warnings:
605 Warning 1287 '@@new' is deprecated and will be removed in a future release.
606 create table hints (i int primary key) engine=ndb partition by list(i) (PARTITION p2 VALUES IN (2),PARTITION p1 VALUES IN (1));
607 insert into hints values (2);
608 Hint node by 2 of 2
609 Tests on T1 (!Blobs)
610 Test some basic NdbApi
611 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
612 from ndbinfo.operations_per_fragment
613 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
614 (tot_key_reads > 0 or tot_frag_scans > 0)
615 group by parent_fq_name, fq_name, type, node_id;
616 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
617 NULL test/def/t1 User table 1 0 8
618 NULL test/def/t1 User table 2 0 24
619 PK lookups
620 explain select * from test.t1 where t1.a=6;
621 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
622 1 SIMPLE t1 # eq_ref PRIMARY,a PRIMARY 4 const # # NULL
623 Warnings:
624 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (`test`.`t1`.`a` = 6)
625 select node_id, counter_name, sum(val) from ndbinfo.counters
626 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
627 group by node_id, counter_name;
628 node_id counter_name sum(val)
629 1 READS 41
630 1 LOCAL_READS 15
631 2 READS 580
632 2 LOCAL_READS 571
633 3 READS 9
634 3 LOCAL_READS 0
635 4 READS 57
636 4 LOCAL_READS 0
637 5 READS 12
638 5 LOCAL_READS 0
639 6 READS 96
640 6 LOCAL_READS 0
641 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
642 from ndbinfo.operations_per_fragment
643 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
644 (tot_key_reads > 0 or tot_frag_scans > 0)
645 group by parent_fq_name, fq_name, type, node_id;
646 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
647 NULL test/def/t1 User table 1 0 8
648 NULL test/def/t1 User table 2 40 24
649 select node_id, counter_name, sum(val) from ndbinfo.counters
650 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
651 group by node_id, counter_name;
652 node_id counter_name sum(val)
653 1 READS 41
654 1 LOCAL_READS 15
655 2 READS 620
656 2 LOCAL_READS 611
657 3 READS 9
658 3 LOCAL_READS 0
659 4 READS 57
660 4 LOCAL_READS 0
661 5 READS 12
662 5 LOCAL_READS 0
663 6 READS 96
664 6 LOCAL_READS 0
665 UI lookups
666 explain select * from test.t1 where t1.b=6;
667 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
668 1 SIMPLE t1 # ref b b 5 const # # NULL
669 Warnings:
670 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (`test`.`t1`.`b` = 6)
671 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
672 from ndbinfo.operations_per_fragment
673 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
674 (tot_key_reads > 0 or tot_frag_scans > 0)
675 group by parent_fq_name, fq_name, type, node_id;
676 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
677 test/def/t1 sys/def/10/b$unique Unique hash index 2 40 0
678 NULL test/def/t1 User table 1 0 8
679 NULL test/def/t1 User table 2 80 24
680 select node_id, counter_name, sum(val) from ndbinfo.counters
681 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
682 group by node_id, counter_name;
683 node_id counter_name sum(val)
684 1 READS 41
685 1 LOCAL_READS 15
686 2 READS 700
687 2 LOCAL_READS 691
688 3 READS 9
689 3 LOCAL_READS 0
690 4 READS 57
691 4 LOCAL_READS 0
692 5 READS 12
693 5 LOCAL_READS 0
694 6 READS 96
695 6 LOCAL_READS 0
696 OI scan
697 explain select * from test.t1 where c > 6;
698 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
699 1 SIMPLE t1 # range c c 5 NULL # # Using pushed condition (`test`.`t1`.`c` > 6); Using MRR
700 Warnings:
701 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (`test`.`t1`.`c` > 6)
702 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
703 from ndbinfo.operations_per_fragment
704 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
705 (tot_key_reads > 0 or tot_frag_scans > 0)
706 group by parent_fq_name, fq_name, type, node_id;
707 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
708 test/def/t1 sys/def/10/b$unique Unique hash index 2 40 0
709 test/def/t1 sys/def/10/c Ordered index 2 0 8
710 NULL test/def/t1 User table 1 0 8
711 NULL test/def/t1 User table 2 80 24
712 Table scan
713 explain select * from test.t1;
714 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
715 1 SIMPLE t1 # ALL NULL NULL NULL NULL # # NULL
716 Warnings:
717 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1`
718 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
719 from ndbinfo.operations_per_fragment
720 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
721 (tot_key_reads > 0 or tot_frag_scans > 0)
722 group by parent_fq_name, fq_name, type, node_id;
723 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
724 test/def/t1 sys/def/10/b$unique Unique hash index 2 40 0
725 test/def/t1 sys/def/10/c Ordered index 2 0 8
726 NULL test/def/t1 User table 1 0 8
727 NULL test/def/t1 User table 2 80 32
728 Test pushed queries (SPJ)
729 Pushed pk->pk
730 Lookup to get a.d, feeding pk lookup of b.a
731 explain select a.a, a.d, b.d
732 from
733 test.t1 a
734 join
735 test.t1 b
736 on
737 a.d = b.a
738 where a.a = 6;
739 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
740 1 SIMPLE a # eq_ref PRIMARY,a PRIMARY 4 const # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
741 1 SIMPLE b # eq_ref PRIMARY,a PRIMARY 4 test.a.d # # Child of 'a' in pushed join@1
742 Warnings:
743 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`a` = `test`.`a`.`d`) and (`test`.`a`.`a` = 6))
744 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
745 from ndbinfo.operations_per_fragment
746 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
747 (tot_key_reads > 0 or tot_frag_scans > 0)
748 group by parent_fq_name, fq_name, type, node_id;
749 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
750 test/def/t1 sys/def/10/b$unique Unique hash index 2 40 0
751 test/def/t1 sys/def/10/c Ordered index 2 0 8
752 NULL test/def/t1 User table 1 0 8
753 NULL test/def/t1 User table 2 160 40
754 Pushed uk->uk
755 Lookup to get a.d, feeding uk lookup of b.b
756 explain select a.a, a.d, b.d
757 from
758 test.t1 a
759 join
760 test.t1 b
761 on
762 a.d = b.b
763 where a.b = 6;
764 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
765 1 SIMPLE a # ref b b 5 const # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
766 1 SIMPLE b # ref b b 5 test.a.d # # Child of 'a' in pushed join@1
767 Warnings:
768 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`b` = `test`.`a`.`d`) and (`test`.`a`.`b` = 6))
769 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
770 from ndbinfo.operations_per_fragment
771 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
772 (tot_key_reads > 0 or tot_frag_scans > 0)
773 group by parent_fq_name, fq_name, type, node_id;
774 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
775 test/def/t1 sys/def/10/b$unique Unique hash index 2 120 0
776 test/def/t1 sys/def/10/c Ordered index 2 0 8
777 NULL test/def/t1 User table 1 0 8
778 NULL test/def/t1 User table 2 239 40
779 Pushed uk->pk
780 Lookup to get a.d, feeding pk lookup of b.a
781 explain select a.a, a.d, b.d
782 from
783 test.t1 a
784 join
785 test.t1 b
786 on
787 a.d = b.a
788 where a.b = 6;
789 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
790 1 SIMPLE a # ref b b 5 const # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
791 1 SIMPLE b # eq_ref PRIMARY,a PRIMARY 4 test.a.d # # Child of 'a' in pushed join@1
792 Warnings:
793 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`a` = `test`.`a`.`d`) and (`test`.`a`.`b` = 6))
794 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
795 from ndbinfo.operations_per_fragment
796 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
797 (tot_key_reads > 0 or tot_frag_scans > 0)
798 group by parent_fq_name, fq_name, type, node_id;
799 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
800 test/def/t1 sys/def/10/b$unique Unique hash index 2 160 0
801 test/def/t1 sys/def/10/c Ordered index 2 0 8
802 NULL test/def/t1 User table 1 0 8
803 NULL test/def/t1 User table 2 319 40
804 Pushed pk->uk
805 Lookup to get a.d, feeding uk lookup of b.b
806 explain select a.a, a.d, b.d
807 from
808 test.t1 a
809 join
810 test.t1 b
811 on
812 a.d = b.b
813 where a.a = 6;
814 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
815 1 SIMPLE a # eq_ref PRIMARY,a PRIMARY 4 const # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
816 1 SIMPLE b # ref b b 5 test.a.d # # Child of 'a' in pushed join@1
817 Warnings:
818 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`b` = `test`.`a`.`d`) and (`test`.`a`.`a` = 6))
819 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
820 from ndbinfo.operations_per_fragment
821 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
822 (tot_key_reads > 0 or tot_frag_scans > 0)
823 group by parent_fq_name, fq_name, type, node_id;
824 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
825 test/def/t1 sys/def/10/b$unique Unique hash index 2 200 0
826 test/def/t1 sys/def/10/c Ordered index 2 0 8
827 NULL test/def/t1 User table 1 0 8
828 NULL test/def/t1 User table 2 398 40
829 Pushed scan originating sub-lookups
830 Table scan to get a.d, feeding pk lookups of b.a
831 explain select a.a, a.d, b.d
832 from
833 test.t1 a
834 join
835 test.t1 b
836 on
837 a.d = b.a;
838 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
839 1 SIMPLE a # ALL NULL NULL NULL NULL # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
840 1 SIMPLE b # eq_ref PRIMARY,a PRIMARY 4 test.a.d # # Child of 'a' in pushed join@1
841 Warnings:
842 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where (`test`.`b`.`a` = `test`.`a`.`d`)
843 select a.a, a.d, b.d
844 from
845 test.t1 a
846 join
847 test.t1 b
848 on
849 a.d = b.a;
850 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
851 from ndbinfo.operations_per_fragment
852 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
853 (tot_key_reads > 0 or tot_frag_scans > 0)
854 group by parent_fq_name, fq_name, type, node_id;
855 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
856 test/def/t1 sys/def/10/b$unique Unique hash index 2 200 0
857 test/def/t1 sys/def/10/c Ordered index 2 0 8
858 NULL test/def/t1 User table 1 0 8
859 NULL test/def/t1 User table 2 438 48
860 Pushed scan originating sub-lookups
861 Table scan to get a.d, feeding uk lookups of b.b
862 explain select a.a, a.d, b.d
863 from
864 test.t1 a
865 join
866 test.t1 b
867 on
868 a.d = b.b;
869 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
870 1 SIMPLE a # ALL NULL NULL NULL NULL # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
871 1 SIMPLE b # ref b b 5 test.a.d # # Child of 'a' in pushed join@1
872 Warnings:
873 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where (`test`.`b`.`b` = `test`.`a`.`d`)
874 select a.a, a.d, b.d
875 from
876 test.t1 a
877 join
878 test.t1 b
879 on
880 a.d = b.b;
881 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
882 from ndbinfo.operations_per_fragment
883 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
884 (tot_key_reads > 0 or tot_frag_scans > 0)
885 group by parent_fq_name, fq_name, type, node_id;
886 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
887 test/def/t1 sys/def/10/b$unique Unique hash index 2 240 0
888 test/def/t1 sys/def/10/c Ordered index 2 0 8
889 NULL test/def/t1 User table 1 0 8
890 NULL test/def/t1 User table 2 477 56
891 Pushed scan originating sub-scans
892 Range scan on a.a > 10 getting a.d, feeding range scans on b.c = a.d
893 explain select a.a, a.d, b.d
894 from
895 test.t1 a
896 join
897 test.t1 b
898 on
899 b.c=a.d
900 where
901 a.a>10;
902 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
903 1 SIMPLE a # range PRIMARY,a PRIMARY 4 NULL # # Parent of 2 pushed join@1; Using pushed condition ((`test`.`a`.`a` > 10) and (`test`.`a`.`d` is not null)); Using MRR
904 1 SIMPLE b # ref c c 5 test.a.d # # Child of 'a' in pushed join@1
905 Warnings:
906 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`c` = `test`.`a`.`d`) and (`test`.`a`.`a` > 10))
907 select a.a, a.d, b.d
908 from
909 test.t1 a
910 join
911 test.t1 b
912 on
913 b.c=a.d
914 where
915 a.a>10;
916 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
917 from ndbinfo.operations_per_fragment
918 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
919 (tot_key_reads > 0 or tot_frag_scans > 0)
920 group by parent_fq_name, fq_name, type, node_id;
921 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
922 test/def/t1 sys/def/10/b$unique Unique hash index 2 240 0
923 test/def/t1 sys/def/10/c Ordered index 2 0 16
924 test/def/t1 sys/def/10/PRIMARY Ordered index 2 0 8
925 NULL test/def/t1 User table 1 0 8
926 NULL test/def/t1 User table 2 477 56
927 Tests on T2 (Blobs)
928 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
929 from ndbinfo.operations_per_fragment
930 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
931 (tot_key_reads > 0 or tot_frag_scans > 0)
932 group by parent_fq_name, fq_name, type, node_id;
933 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
934 NULL test/def/t2 User table 1 0 4
935 NULL test/def/t2 User table 2 0 12
936 select node_id, counter_name, sum(val) from ndbinfo.counters
937 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
938 group by node_id, counter_name;
939 node_id counter_name sum(val)
940 1 READS 41
941 1 LOCAL_READS 15
942 2 READS 860
943 2 LOCAL_READS 851
944 3 READS 9
945 3 LOCAL_READS 0
946 4 READS 57
947 4 LOCAL_READS 0
948 5 READS 12
949 5 LOCAL_READS 0
950 6 READS 96
951 6 LOCAL_READS 0
952 Test some basic NdbApi
953 explain select * from test.t2 where t2.a=6;
954 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
955 1 SIMPLE t2 # eq_ref PRIMARY PRIMARY 4 const # # NULL
956 Warnings:
957 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`a` = 6)
958 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
959 from ndbinfo.operations_per_fragment
960 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
961 (tot_key_reads > 0 or tot_frag_scans > 0)
962 group by parent_fq_name, fq_name, type, node_id;
963 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
964 test/def/t2 test/def/NDB$BLOB_15_3 User table 2 40 0
965 NULL test/def/t2 User table 1 0 4
966 NULL test/def/t2 User table 2 80 12
967 select node_id, counter_name, sum(val) from ndbinfo.counters
968 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
969 group by node_id, counter_name;
970 node_id counter_name sum(val)
971 1 READS 41
972 1 LOCAL_READS 15
973 2 READS 942
974 2 LOCAL_READS 931
975 3 READS 9
976 3 LOCAL_READS 0
977 4 READS 57
978 4 LOCAL_READS 0
979 5 READS 12
980 5 LOCAL_READS 0
981 6 READS 96
982 6 LOCAL_READS 0
983 UI lookups
984 explain select * from test.t2 where t2.b=6;
985 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
986 1 SIMPLE t2 # ref b b 5 const # # NULL
987 Warnings:
988 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 6)
989 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
990 from ndbinfo.operations_per_fragment
991 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
992 (tot_key_reads > 0 or tot_frag_scans > 0)
993 group by parent_fq_name, fq_name, type, node_id;
994 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
995 test/def/t2 test/def/NDB$BLOB_15_3 User table 2 80 0
996 test/def/t2 sys/def/15/b$unique Unique hash index 2 80 0
997 NULL test/def/t2 User table 1 0 4
998 NULL test/def/t2 User table 2 160 12
999 select node_id, counter_name, sum(val) from ndbinfo.counters
1000 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
1001 group by node_id, counter_name;
1002 node_id counter_name sum(val)
1003 1 READS 41
1004 1 LOCAL_READS 15
1005 2 READS 1142
1006 2 LOCAL_READS 1131
1007 3 READS 9
1008 3 LOCAL_READS 0
1009 4 READS 57
1010 4 LOCAL_READS 0
1011 5 READS 12
1012 5 LOCAL_READS 0
1013 6 READS 96
1014 6 LOCAL_READS 0
1015 OI scan
1016 explain select * from test.t2 where c > 6;
1017 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1018 1 SIMPLE t2 # range c c 5 NULL # # Using pushed condition (`test`.`t2`.`c` > 6)
1019 Warnings:
1020 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`c` > 6)
1021 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1022 from ndbinfo.operations_per_fragment
1023 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
1024 (tot_key_reads > 0 or tot_frag_scans > 0)
1025 group by parent_fq_name, fq_name, type, node_id;
1026 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1027 test/def/t2 test/def/NDB$BLOB_15_3 User table 2 115 0
1028 test/def/t2 sys/def/15/b$unique Unique hash index 2 80 0
1029 test/def/t2 sys/def/15/c Ordered index 2 0 8
1030 NULL test/def/t2 User table 1 0 4
1031 NULL test/def/t2 User table 2 160 12
1032 Table scan
1033 explain select * from test.t2;
1034 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1035 1 SIMPLE t2 # ALL NULL NULL NULL NULL # # NULL
1036 Warnings:
1037 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`
1038 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1039 from ndbinfo.operations_per_fragment
1040 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
1041 (tot_key_reads > 0 or tot_frag_scans > 0)
1042 group by parent_fq_name, fq_name, type, node_id;
1043 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1044 test/def/t2 test/def/NDB$BLOB_15_3 User table 2 155 0
1045 test/def/t2 sys/def/15/b$unique Unique hash index 2 80 0
1046 test/def/t2 sys/def/15/c Ordered index 2 0 8
1047 NULL test/def/t2 User table 1 0 4
1048 NULL test/def/t2 User table 2 160 20
1049 drop table hints;
1050 delete from t1;
1051 delete from t2;
1052 Test read-locality of FULLY-REPLICATED with 2 node groups
1053 Nodegroup 1 created
1054 alter table t1 algorithm=inplace, reorganize partition;
1055 alter table t2 algorithm=inplace, reorganize partition;
1056 select id into @t1_id from ndbinfo.ndb$dict_obj_info where fq_name = 'test/def/t1';
1057 select id into @t1ui_id from ndbinfo.ndb$dict_obj_info where parent_obj_id = @t1_id and type = 3;
1058 select id into @t2_id from ndbinfo.ndb$dict_obj_info where fq_name = 'test/def/t2';
1059 select id into @t2ui_id from ndbinfo.ndb$dict_obj_info where parent_obj_id = @t2_id and type = 3;
1060 select id into @t2bt_id from ndbinfo.ndb$dict_obj_info where parent_obj_id = @t2_id and fq_name like 'test/def/NDB$BLOB%';
1061 Put some data in.
1062 Make it more interesting for joining
1063 update test.t1 set c=c+1, d=d+1;
1064 select count(distinct node_id) into @nodes
1065 from ndbinfo.ndb$membership where group_id < 0xFFFFFF00;
1066 Check replicas
1067 select count(distinct node_id)
1068 from ndbinfo.operations_per_fragment
1069 where fq_name='test/def/t1'
1070 and fragment_num=0;
1071 count(distinct node_id)
1072 2
1073 Check nodes with table data
1074 select count(distinct node_id)
1075 from ndbinfo.operations_per_fragment
1076 where fq_name='test/def/t1';
1077 count(distinct node_id)
1078 4
1079 Check UI replicas
1080 select count(distinct node_id)
1081 from ndbinfo.operations_per_fragment
1082 where table_id = @t1ui_id
1083 and fragment_num=0;
1084 count(distinct node_id)
1085 2
1086 Check nodes with UI data
1087 select count(distinct node_id)
1088 from ndbinfo.operations_per_fragment
1089 where table_id = @t1ui_id;
1090 count(distinct node_id)
1091 4
1092 Put some data in.
1093 insert into test.t2 select a,b,c,repeat('BJC',300) from test.t1;
1094 Check blob table replicas
1095 select count(distinct node_id)
1096 from ndbinfo.operations_per_fragment
1097 where table_id = @t2bt_id
1098 and fragment_num=0;
1099 count(distinct node_id)
1100 2
1101 Check nodes with blob data
1102 select count(distinct node_id)
1103 from ndbinfo.operations_per_fragment
1104 where table_id = @t2bt_id;
1105 count(distinct node_id)
1106 4
1107 set @partitions = 'PARTITION p4 VALUES IN (4)';
1108 set @partitions = concat(@partitions,',PARTITION p3 VALUES IN (3)');
1109 set @partitions = concat(@partitions,',PARTITION p2 VALUES IN (2)');
1110 set @partitions = concat(@partitions,',PARTITION p1 VALUES IN (1)');
1111 set @@new = 1;
1112 Warnings:
1113 Warning 1287 '@@new' is deprecated and will be removed in a future release.
1114 create table hints (i int primary key) engine=ndb partition by list(i) (PARTITION p4 VALUES IN (4),PARTITION p3 VALUES IN (3),PARTITION p2 VALUES IN (2),PARTITION p1 VALUES IN (1));
1115 insert into hints values (4);
1116 Hint node by 4 of 4
1117 Tests on T1 (!Blobs)
1118 Test some basic NdbApi
1119 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1120 from ndbinfo.operations_per_fragment
1121 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1122 (tot_key_reads > 0 or tot_frag_scans > 0)
1123 group by parent_fq_name, fq_name, type, node_id;
1124 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1125 test/def/t1 sys/def/10/b$unique Unique hash index 1 0 8
1126 test/def/t1 sys/def/10/b$unique Unique hash index 2 240 8
1127 test/def/t1 sys/def/10/c Ordered index 2 0 16
1128 test/def/t1 sys/def/10/PRIMARY Ordered index 2 0 8
1129 NULL test/def/t1 User table 1 0 24
1130 NULL test/def/t1 User table 2 477 88
1131 PK lookups
1132 explain select * from test.t1 where t1.a=6;
1133 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1134 1 SIMPLE t1 # eq_ref PRIMARY,a PRIMARY 4 const # # NULL
1135 Warnings:
1136 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (`test`.`t1`.`a` = 6)
1137 select node_id, counter_name, sum(val) from ndbinfo.counters
1138 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
1139 group by node_id, counter_name;
1140 node_id counter_name sum(val)
1141 1 READS 51
1142 1 LOCAL_READS 22
1143 2 READS 1270
1144 2 LOCAL_READS 1253
1145 3 READS 12
1146 3 LOCAL_READS 0
1147 4 READS 60
1148 4 LOCAL_READS 0
1149 5 READS 15
1150 5 LOCAL_READS 0
1151 6 READS 99
1152 6 LOCAL_READS 0
1153 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1154 from ndbinfo.operations_per_fragment
1155 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1156 (tot_key_reads > 0 or tot_frag_scans > 0)
1157 group by parent_fq_name, fq_name, type, node_id;
1158 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1159 test/def/t1 sys/def/10/b$unique Unique hash index 1 0 8
1160 test/def/t1 sys/def/10/b$unique Unique hash index 2 240 8
1161 test/def/t1 sys/def/10/c Ordered index 2 0 16
1162 test/def/t1 sys/def/10/PRIMARY Ordered index 2 0 8
1163 NULL test/def/t1 User table 1 0 24
1164 NULL test/def/t1 User table 2 517 88
1165 select node_id, counter_name, sum(val) from ndbinfo.counters
1166 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
1167 group by node_id, counter_name;
1168 node_id counter_name sum(val)
1169 1 READS 51
1170 1 LOCAL_READS 22
1171 2 READS 1310
1172 2 LOCAL_READS 1293
1173 3 READS 12
1174 3 LOCAL_READS 0
1175 4 READS 60
1176 4 LOCAL_READS 0
1177 5 READS 15
1178 5 LOCAL_READS 0
1179 6 READS 99
1180 6 LOCAL_READS 0
1181 UI lookups
1182 explain select * from test.t1 where t1.b=6;
1183 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1184 1 SIMPLE t1 # ref b b 5 const # # NULL
1185 Warnings:
1186 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (`test`.`t1`.`b` = 6)
1187 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1188 from ndbinfo.operations_per_fragment
1189 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1190 (tot_key_reads > 0 or tot_frag_scans > 0)
1191 group by parent_fq_name, fq_name, type, node_id;
1192 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1193 test/def/t1 sys/def/10/b$unique Unique hash index 1 0 8
1194 test/def/t1 sys/def/10/b$unique Unique hash index 2 280 8
1195 test/def/t1 sys/def/10/c Ordered index 2 0 16
1196 test/def/t1 sys/def/10/PRIMARY Ordered index 2 0 8
1197 NULL test/def/t1 User table 1 0 24
1198 NULL test/def/t1 User table 2 557 88
1199 select node_id, counter_name, sum(val) from ndbinfo.counters
1200 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
1201 group by node_id, counter_name;
1202 node_id counter_name sum(val)
1203 1 READS 51
1204 1 LOCAL_READS 22
1205 2 READS 1390
1206 2 LOCAL_READS 1373
1207 3 READS 12
1208 3 LOCAL_READS 0
1209 4 READS 60
1210 4 LOCAL_READS 0
1211 5 READS 15
1212 5 LOCAL_READS 0
1213 6 READS 99
1214 6 LOCAL_READS 0
1215 OI scan
1216 explain select * from test.t1 where c > 6;
1217 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1218 1 SIMPLE t1 # range c c 5 NULL # # Using pushed condition (`test`.`t1`.`c` > 6); Using MRR
1219 Warnings:
1220 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (`test`.`t1`.`c` > 6)
1221 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1222 from ndbinfo.operations_per_fragment
1223 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1224 (tot_key_reads > 0 or tot_frag_scans > 0)
1225 group by parent_fq_name, fq_name, type, node_id;
1226 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1227 test/def/t1 sys/def/10/b$unique Unique hash index 1 0 8
1228 test/def/t1 sys/def/10/b$unique Unique hash index 2 280 8
1229 test/def/t1 sys/def/10/c Ordered index 2 0 24
1230 test/def/t1 sys/def/10/PRIMARY Ordered index 2 0 8
1231 NULL test/def/t1 User table 1 0 24
1232 NULL test/def/t1 User table 2 557 88
1233 Table scan
1234 explain select * from test.t1;
1235 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1236 1 SIMPLE t1 # ALL NULL NULL NULL NULL # # NULL
1237 Warnings:
1238 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1`
1239 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1240 from ndbinfo.operations_per_fragment
1241 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1242 (tot_key_reads > 0 or tot_frag_scans > 0)
1243 group by parent_fq_name, fq_name, type, node_id;
1244 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1245 test/def/t1 sys/def/10/b$unique Unique hash index 1 0 8
1246 test/def/t1 sys/def/10/b$unique Unique hash index 2 280 8
1247 test/def/t1 sys/def/10/c Ordered index 2 0 24
1248 test/def/t1 sys/def/10/PRIMARY Ordered index 2 0 8
1249 NULL test/def/t1 User table 1 0 24
1250 NULL test/def/t1 User table 2 557 96
1251 Test pushed queries (SPJ)
1252 Pushed pk->pk
1253 Lookup to get a.d, feeding pk lookup of b.a
1254 explain select a.a, a.d, b.d
1255 from
1256 test.t1 a
1257 join
1258 test.t1 b
1259 on
1260 a.d = b.a
1261 where a.a = 6;
1262 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1263 1 SIMPLE a # eq_ref PRIMARY,a PRIMARY 4 const # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
1264 1 SIMPLE b # eq_ref PRIMARY,a PRIMARY 4 test.a.d # # Child of 'a' in pushed join@1
1265 Warnings:
1266 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`a` = `test`.`a`.`d`) and (`test`.`a`.`a` = 6))
1267 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1268 from ndbinfo.operations_per_fragment
1269 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1270 (tot_key_reads > 0 or tot_frag_scans > 0)
1271 group by parent_fq_name, fq_name, type, node_id;
1272 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1273 test/def/t1 sys/def/10/b$unique Unique hash index 1 0 8
1274 test/def/t1 sys/def/10/b$unique Unique hash index 2 280 8
1275 test/def/t1 sys/def/10/c Ordered index 2 0 24
1276 test/def/t1 sys/def/10/PRIMARY Ordered index 2 0 8
1277 NULL test/def/t1 User table 1 0 24
1278 NULL test/def/t1 User table 2 637 104
1279 Pushed uk->uk
1280 Lookup to get a.d, feeding uk lookup of b.b
1281 explain select a.a, a.d, b.d
1282 from
1283 test.t1 a
1284 join
1285 test.t1 b
1286 on
1287 a.d = b.b
1288 where a.b = 6;
1289 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1290 1 SIMPLE a # ref b b 5 const # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
1291 1 SIMPLE b # ref b b 5 test.a.d # # Child of 'a' in pushed join@1
1292 Warnings:
1293 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`b` = `test`.`a`.`d`) and (`test`.`a`.`b` = 6))
1294 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1295 from ndbinfo.operations_per_fragment
1296 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1297 (tot_key_reads > 0 or tot_frag_scans > 0)
1298 group by parent_fq_name, fq_name, type, node_id;
1299 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1300 test/def/t1 sys/def/10/b$unique Unique hash index 1 0 8
1301 test/def/t1 sys/def/10/b$unique Unique hash index 2 360 8
1302 test/def/t1 sys/def/10/c Ordered index 2 0 24
1303 test/def/t1 sys/def/10/PRIMARY Ordered index 2 0 8
1304 NULL test/def/t1 User table 1 0 24
1305 NULL test/def/t1 User table 2 716 104
1306 Pushed uk->pk
1307 Lookup to get a.d, feeding pk lookup of b.a
1308 explain select a.a, a.d, b.d
1309 from
1310 test.t1 a
1311 join
1312 test.t1 b
1313 on
1314 a.d = b.a
1315 where a.b = 6;
1316 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1317 1 SIMPLE a # ref b b 5 const # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
1318 1 SIMPLE b # eq_ref PRIMARY,a PRIMARY 4 test.a.d # # Child of 'a' in pushed join@1
1319 Warnings:
1320 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`a` = `test`.`a`.`d`) and (`test`.`a`.`b` = 6))
1321 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1322 from ndbinfo.operations_per_fragment
1323 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1324 (tot_key_reads > 0 or tot_frag_scans > 0)
1325 group by parent_fq_name, fq_name, type, node_id;
1326 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1327 test/def/t1 sys/def/10/b$unique Unique hash index 1 0 8
1328 test/def/t1 sys/def/10/b$unique Unique hash index 2 400 8
1329 test/def/t1 sys/def/10/c Ordered index 2 0 24
1330 test/def/t1 sys/def/10/PRIMARY Ordered index 2 0 8
1331 NULL test/def/t1 User table 1 0 24
1332 NULL test/def/t1 User table 2 796 104
1333 Pushed pk->uk
1334 Lookup to get a.d, feeding uk lookup of b.b
1335 explain select a.a, a.d, b.d
1336 from
1337 test.t1 a
1338 join
1339 test.t1 b
1340 on
1341 a.d = b.b
1342 where a.a = 6;
1343 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1344 1 SIMPLE a # eq_ref PRIMARY,a PRIMARY 4 const # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
1345 1 SIMPLE b # ref b b 5 test.a.d # # Child of 'a' in pushed join@1
1346 Warnings:
1347 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`b` = `test`.`a`.`d`) and (`test`.`a`.`a` = 6))
1348 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1349 from ndbinfo.operations_per_fragment
1350 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1351 (tot_key_reads > 0 or tot_frag_scans > 0)
1352 group by parent_fq_name, fq_name, type, node_id;
1353 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1354 test/def/t1 sys/def/10/b$unique Unique hash index 1 0 8
1355 test/def/t1 sys/def/10/b$unique Unique hash index 2 440 8
1356 test/def/t1 sys/def/10/c Ordered index 2 0 24
1357 test/def/t1 sys/def/10/PRIMARY Ordered index 2 0 8
1358 NULL test/def/t1 User table 1 0 24
1359 NULL test/def/t1 User table 2 875 104
1360 Pushed scan originating sub-lookups
1361 Table scan to get a.d, feeding pk lookups of b.a
1362 explain select a.a, a.d, b.d
1363 from
1364 test.t1 a
1365 join
1366 test.t1 b
1367 on
1368 a.d = b.a;
1369 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1370 1 SIMPLE a # ALL NULL NULL NULL NULL # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
1371 1 SIMPLE b # eq_ref PRIMARY,a PRIMARY 4 test.a.d # # Child of 'a' in pushed join@1
1372 Warnings:
1373 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where (`test`.`b`.`a` = `test`.`a`.`d`)
1374 select a.a, a.d, b.d
1375 from
1376 test.t1 a
1377 join
1378 test.t1 b
1379 on
1380 a.d = b.a;
1381 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1382 from ndbinfo.operations_per_fragment
1383 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1384 (tot_key_reads > 0 or tot_frag_scans > 0)
1385 group by parent_fq_name, fq_name, type, node_id;
1386 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1387 test/def/t1 sys/def/10/b$unique Unique hash index 1 0 8
1388 test/def/t1 sys/def/10/b$unique Unique hash index 2 440 8
1389 test/def/t1 sys/def/10/c Ordered index 2 0 24
1390 test/def/t1 sys/def/10/PRIMARY Ordered index 2 0 8
1391 NULL test/def/t1 User table 1 0 24
1392 NULL test/def/t1 User table 2 915 112
1393 Pushed scan originating sub-lookups
1394 Table scan to get a.d, feeding uk lookups of b.b
1395 explain select a.a, a.d, b.d
1396 from
1397 test.t1 a
1398 join
1399 test.t1 b
1400 on
1401 a.d = b.b;
1402 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1403 1 SIMPLE a # ALL NULL NULL NULL NULL # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
1404 1 SIMPLE b # ref b b 5 test.a.d # # Child of 'a' in pushed join@1
1405 Warnings:
1406 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where (`test`.`b`.`b` = `test`.`a`.`d`)
1407 select a.a, a.d, b.d
1408 from
1409 test.t1 a
1410 join
1411 test.t1 b
1412 on
1413 a.d = b.b;
1414 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1415 from ndbinfo.operations_per_fragment
1416 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1417 (tot_key_reads > 0 or tot_frag_scans > 0)
1418 group by parent_fq_name, fq_name, type, node_id;
1419 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1420 test/def/t1 sys/def/10/b$unique Unique hash index 1 0 8
1421 test/def/t1 sys/def/10/b$unique Unique hash index 2 480 8
1422 test/def/t1 sys/def/10/c Ordered index 2 0 24
1423 test/def/t1 sys/def/10/PRIMARY Ordered index 2 0 8
1424 NULL test/def/t1 User table 1 0 24
1425 NULL test/def/t1 User table 2 954 120
1426 Pushed scan originating sub-scans
1427 Range scan on a.a > 10 getting a.d, feeding range scans on b.c = a.d
1428 explain select a.a, a.d, b.d
1429 from
1430 test.t1 a
1431 join
1432 test.t1 b
1433 on
1434 b.c=a.d
1435 where
1436 a.a>10;
1437 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1438 1 SIMPLE a # range PRIMARY,a PRIMARY 4 NULL # # Parent of 2 pushed join@1; Using pushed condition ((`test`.`a`.`a` > 10) and (`test`.`a`.`d` is not null)); Using MRR
1439 1 SIMPLE b # ref c c 5 test.a.d # # Child of 'a' in pushed join@1
1440 Warnings:
1441 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`c` = `test`.`a`.`d`) and (`test`.`a`.`a` > 10))
1442 select a.a, a.d, b.d
1443 from
1444 test.t1 a
1445 join
1446 test.t1 b
1447 on
1448 b.c=a.d
1449 where
1450 a.a>10;
1451 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1452 from ndbinfo.operations_per_fragment
1453 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1454 (tot_key_reads > 0 or tot_frag_scans > 0)
1455 group by parent_fq_name, fq_name, type, node_id;
1456 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1457 test/def/t1 sys/def/10/b$unique Unique hash index 1 0 8
1458 test/def/t1 sys/def/10/b$unique Unique hash index 2 480 8
1459 test/def/t1 sys/def/10/c Ordered index 2 0 32
1460 test/def/t1 sys/def/10/PRIMARY Ordered index 2 0 16
1461 NULL test/def/t1 User table 1 0 24
1462 NULL test/def/t1 User table 2 954 120
1463 Tests on T2 (Blobs)
1464 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1465 from ndbinfo.operations_per_fragment
1466 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
1467 (tot_key_reads > 0 or tot_frag_scans > 0)
1468 group by parent_fq_name, fq_name, type, node_id;
1469 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1470 test/def/t2 test/def/NDB$BLOB_15_3 User table 1 0 8
1471 test/def/t2 test/def/NDB$BLOB_15_3 User table 2 155 8
1472 test/def/t2 sys/def/15/b$unique Unique hash index 1 0 8
1473 test/def/t2 sys/def/15/b$unique Unique hash index 2 80 8
1474 test/def/t2 sys/def/15/c Ordered index 2 0 8
1475 NULL test/def/t2 User table 1 0 16
1476 NULL test/def/t2 User table 2 200 40
1477 select node_id, counter_name, sum(val) from ndbinfo.counters
1478 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
1479 group by node_id, counter_name;
1480 node_id counter_name sum(val)
1481 1 READS 51
1482 1 LOCAL_READS 22
1483 2 READS 1550
1484 2 LOCAL_READS 1533
1485 3 READS 12
1486 3 LOCAL_READS 0
1487 4 READS 60
1488 4 LOCAL_READS 0
1489 5 READS 15
1490 5 LOCAL_READS 0
1491 6 READS 99
1492 6 LOCAL_READS 0
1493 Test some basic NdbApi
1494 explain select * from test.t2 where t2.a=6;
1495 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1496 1 SIMPLE t2 # eq_ref PRIMARY PRIMARY 4 const # # NULL
1497 Warnings:
1498 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`a` = 6)
1499 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1500 from ndbinfo.operations_per_fragment
1501 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
1502 (tot_key_reads > 0 or tot_frag_scans > 0)
1503 group by parent_fq_name, fq_name, type, node_id;
1504 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1505 test/def/t2 test/def/NDB$BLOB_15_3 User table 1 0 8
1506 test/def/t2 test/def/NDB$BLOB_15_3 User table 2 177 8
1507 test/def/t2 test/def/NDB$BLOB_15_3 User table 4 18 0
1508 test/def/t2 sys/def/15/b$unique Unique hash index 1 0 8
1509 test/def/t2 sys/def/15/b$unique Unique hash index 2 80 8
1510 test/def/t2 sys/def/15/c Ordered index 2 0 8
1511 NULL test/def/t2 User table 1 0 16
1512 NULL test/def/t2 User table 2 244 40
1513 NULL test/def/t2 User table 4 36 0
1514 select node_id, counter_name, sum(val) from ndbinfo.counters
1515 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
1516 group by node_id, counter_name;
1517 node_id counter_name sum(val)
1518 1 READS 51
1519 1 LOCAL_READS 22
1520 2 READS 1594
1521 2 LOCAL_READS 1577
1522 3 READS 12
1523 3 LOCAL_READS 0
1524 4 READS 96
1525 4 LOCAL_READS 36
1526 5 READS 15
1527 5 LOCAL_READS 0
1528 6 READS 99
1529 6 LOCAL_READS 0
1530 UI lookups
1531 explain select * from test.t2 where t2.b=6;
1532 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1533 1 SIMPLE t2 # ref b b 5 const # # NULL
1534 Warnings:
1535 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 6)
1536 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1537 from ndbinfo.operations_per_fragment
1538 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
1539 (tot_key_reads > 0 or tot_frag_scans > 0)
1540 group by parent_fq_name, fq_name, type, node_id;
1541 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1542 test/def/t2 test/def/NDB$BLOB_15_3 User table 1 0 8
1543 test/def/t2 test/def/NDB$BLOB_15_3 User table 2 197 8
1544 test/def/t2 test/def/NDB$BLOB_15_3 User table 4 38 0
1545 test/def/t2 sys/def/15/b$unique Unique hash index 1 0 8
1546 test/def/t2 sys/def/15/b$unique Unique hash index 2 120 8
1547 test/def/t2 sys/def/15/b$unique Unique hash index 4 40 0
1548 test/def/t2 sys/def/15/c Ordered index 2 0 8
1549 NULL test/def/t2 User table 1 0 16
1550 NULL test/def/t2 User table 2 284 40
1551 NULL test/def/t2 User table 4 76 0
1552 select node_id, counter_name, sum(val) from ndbinfo.counters
1553 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
1554 group by node_id, counter_name;
1555 node_id counter_name sum(val)
1556 1 READS 51
1557 1 LOCAL_READS 22
1558 2 READS 1694
1559 2 LOCAL_READS 1677
1560 3 READS 12
1561 3 LOCAL_READS 0
1562 4 READS 196
1563 4 LOCAL_READS 136
1564 5 READS 15
1565 5 LOCAL_READS 0
1566 6 READS 99
1567 6 LOCAL_READS 0
1568 OI scan
1569 explain select * from test.t2 where c > 6;
1570 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1571 1 SIMPLE t2 # range c c 5 NULL # # Using pushed condition (`test`.`t2`.`c` > 6)
1572 Warnings:
1573 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`c` > 6)
1574 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1575 from ndbinfo.operations_per_fragment
1576 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
1577 (tot_key_reads > 0 or tot_frag_scans > 0)
1578 group by parent_fq_name, fq_name, type, node_id;
1579 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1580 test/def/t2 test/def/NDB$BLOB_15_3 User table 1 0 8
1581 test/def/t2 test/def/NDB$BLOB_15_3 User table 2 197 8
1582 test/def/t2 test/def/NDB$BLOB_15_3 User table 4 73 0
1583 test/def/t2 sys/def/15/b$unique Unique hash index 1 0 8
1584 test/def/t2 sys/def/15/b$unique Unique hash index 2 120 8
1585 test/def/t2 sys/def/15/b$unique Unique hash index 4 40 0
1586 test/def/t2 sys/def/15/c Ordered index 2 0 8
1587 test/def/t2 sys/def/15/c Ordered index 4 0 8
1588 NULL test/def/t2 User table 1 0 16
1589 NULL test/def/t2 User table 2 284 40
1590 NULL test/def/t2 User table 4 76 0
1591 Table scan
1592 explain select * from test.t2;
1593 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1594 1 SIMPLE t2 # ALL NULL NULL NULL NULL # # NULL
1595 Warnings:
1596 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`
1597 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1598 from ndbinfo.operations_per_fragment
1599 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
1600 (tot_key_reads > 0 or tot_frag_scans > 0)
1601 group by parent_fq_name, fq_name, type, node_id;
1602 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1603 test/def/t2 test/def/NDB$BLOB_15_3 User table 1 0 8
1604 test/def/t2 test/def/NDB$BLOB_15_3 User table 2 237 8
1605 test/def/t2 test/def/NDB$BLOB_15_3 User table 4 73 0
1606 test/def/t2 sys/def/15/b$unique Unique hash index 1 0 8
1607 test/def/t2 sys/def/15/b$unique Unique hash index 2 120 8
1608 test/def/t2 sys/def/15/b$unique Unique hash index 4 40 0
1609 test/def/t2 sys/def/15/c Ordered index 2 0 8
1610 test/def/t2 sys/def/15/c Ordered index 4 0 8
1611 NULL test/def/t2 User table 1 0 16
1612 NULL test/def/t2 User table 2 284 48
1613 NULL test/def/t2 User table 4 76 0
1614 drop table hints;
1615 delete from t1;
1616 delete from t2;
1617 drop table test.t1;
1618 drop table test.t2;
1619 Test read-locality of READ-BACKUP with 3 node groups
1620 Nodegroup 2 created
1621 create table test.t1 (a int primary key,
1622 b int,
1623 c int,
1624 d int,
1625 unique(b) using hash,
1626 key(c),
1627 key(a,c))
1628 comment="NDB_TABLE=READ_BACKUP=1"
1629 engine=ndb;
1630 Warnings:
1631 Warning 1121 Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan
1632 create table test.t2 (a int primary key,
1633 b int,
1634 c int,
1635 d text,
1636 unique(b) using hash,
1637 key(c))
1638 comment="NDB_TABLE=READ_BACKUP=1"
1639 engine=ndb;
1640 Warnings:
1641 Warning 1121 Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan
1642 select id into @t1_id from ndbinfo.ndb$dict_obj_info where fq_name = 'test/def/t1';
1643 select id into @t1ui_id from ndbinfo.ndb$dict_obj_info where parent_obj_id = @t1_id and type = 3;
1644 select id into @t2_id from ndbinfo.ndb$dict_obj_info where fq_name = 'test/def/t2';
1645 select id into @t2ui_id from ndbinfo.ndb$dict_obj_info where parent_obj_id = @t2_id and type = 3;
1646 select id into @t2bt_id from ndbinfo.ndb$dict_obj_info where parent_obj_id = @t2_id and fq_name like 'test/def/NDB$BLOB%';
1647 Put some data in.
1648 Make it more interesting for joining
1649 update test.t1 set c=c+1, d=d+1;
1650 select count(distinct node_id) into @nodes
1651 from ndbinfo.ndb$membership where group_id < 0xFFFFFF00;
1652 Check replicas
1653 select count(distinct node_id)
1654 from ndbinfo.operations_per_fragment
1655 where fq_name='test/def/t1'
1656 and fragment_num=0;
1657 count(distinct node_id)
1658 2
1659 Check nodes with table data
1660 select count(distinct node_id)
1661 from ndbinfo.operations_per_fragment
1662 where fq_name='test/def/t1';
1663 count(distinct node_id)
1664 6
1665 Check UI replicas
1666 select count(distinct node_id)
1667 from ndbinfo.operations_per_fragment
1668 where table_id = @t1ui_id
1669 and fragment_num=0;
1670 count(distinct node_id)
1671 2
1672 Check nodes with UI data
1673 select count(distinct node_id)
1674 from ndbinfo.operations_per_fragment
1675 where table_id = @t1ui_id;
1676 count(distinct node_id)
1677 6
1678 Put some data in.
1679 insert into test.t2 select a,b,c,repeat('BJC',300) from test.t1;
1680 Check blob table replicas
1681 select count(distinct node_id)
1682 from ndbinfo.operations_per_fragment
1683 where table_id = @t2bt_id
1684 and fragment_num=0;
1685 count(distinct node_id)
1686 2
1687 Check nodes with blob data
1688 select count(distinct node_id)
1689 from ndbinfo.operations_per_fragment
1690 where table_id = @t2bt_id;
1691 count(distinct node_id)
1692 6
1693 set @partitions = 'PARTITION p6 VALUES IN (6)';
1694 set @partitions = concat(@partitions,',PARTITION p5 VALUES IN (5)');
1695 set @partitions = concat(@partitions,',PARTITION p4 VALUES IN (4)');
1696 set @partitions = concat(@partitions,',PARTITION p3 VALUES IN (3)');
1697 set @partitions = concat(@partitions,',PARTITION p2 VALUES IN (2)');
1698 set @partitions = concat(@partitions,',PARTITION p1 VALUES IN (1)');
1699 set @@new = 1;
1700 Warnings:
1701 Warning 1287 '@@new' is deprecated and will be removed in a future release.
1702 create table hints (i int primary key) engine=ndb partition by list(i) (PARTITION p6 VALUES IN (6),PARTITION p5 VALUES IN (5),PARTITION p4 VALUES IN (4),PARTITION p3 VALUES IN (3),PARTITION p2 VALUES IN (2),PARTITION p1 VALUES IN (1));
1703 insert into hints values (6);
1704 Hint node by 6 of 6
1705 Tests on T1 (!Blobs)
1706 Test some basic NdbApi
1707 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1708 from ndbinfo.operations_per_fragment
1709 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1710 (tot_key_reads > 0 or tot_frag_scans > 0)
1711 group by parent_fq_name, fq_name, type, node_id;
1712 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1713 NULL test/def/t1 User table 1 0 8
1714 NULL test/def/t1 User table 2 0 24
1715 NULL test/def/t1 User table 3 0 8
1716 NULL test/def/t1 User table 4 0 24
1717 NULL test/def/t1 User table 5 0 8
1718 NULL test/def/t1 User table 6 0 24
1719 PK lookups
1720 explain select * from test.t1 where t1.a=6;
1721 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1722 1 SIMPLE t1 # eq_ref PRIMARY,a PRIMARY 4 const # # NULL
1723 Warnings:
1724 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (`test`.`t1`.`a` = 6)
1725 select node_id, counter_name, sum(val) from ndbinfo.counters
1726 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
1727 group by node_id, counter_name;
1728 node_id counter_name sum(val)
1729 1 READS 75
1730 1 LOCAL_READS 41
1731 2 READS 1787
1732 2 LOCAL_READS 1760
1733 3 READS 21
1734 3 LOCAL_READS 0
1735 4 READS 239
1736 4 LOCAL_READS 171
1737 5 READS 23
1738 5 LOCAL_READS 0
1739 6 READS 119
1740 6 LOCAL_READS 0
1741 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1742 from ndbinfo.operations_per_fragment
1743 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1744 (tot_key_reads > 0 or tot_frag_scans > 0)
1745 group by parent_fq_name, fq_name, type, node_id;
1746 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1747 NULL test/def/t1 User table 1 0 8
1748 NULL test/def/t1 User table 2 11 24
1749 NULL test/def/t1 User table 3 0 8
1750 NULL test/def/t1 User table 4 16 24
1751 NULL test/def/t1 User table 5 0 8
1752 NULL test/def/t1 User table 6 13 24
1753 select node_id, counter_name, sum(val) from ndbinfo.counters
1754 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
1755 group by node_id, counter_name;
1756 node_id counter_name sum(val)
1757 1 READS 75
1758 1 LOCAL_READS 41
1759 2 READS 1798
1760 2 LOCAL_READS 1771
1761 3 READS 21
1762 3 LOCAL_READS 0
1763 4 READS 255
1764 4 LOCAL_READS 187
1765 5 READS 23
1766 5 LOCAL_READS 0
1767 6 READS 132
1768 6 LOCAL_READS 13
1769 UI lookups
1770 explain select * from test.t1 where t1.b=6;
1771 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1772 1 SIMPLE t1 # ref b b 5 const # # NULL
1773 Warnings:
1774 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (`test`.`t1`.`b` = 6)
1775 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1776 from ndbinfo.operations_per_fragment
1777 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1778 (tot_key_reads > 0 or tot_frag_scans > 0)
1779 group by parent_fq_name, fq_name, type, node_id;
1780 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1781 test/def/t1 sys/def/11/b$unique Unique hash index 2 11 0
1782 test/def/t1 sys/def/11/b$unique Unique hash index 4 16 0
1783 test/def/t1 sys/def/11/b$unique Unique hash index 6 13 0
1784 NULL test/def/t1 User table 1 0 8
1785 NULL test/def/t1 User table 2 22 24
1786 NULL test/def/t1 User table 3 0 8
1787 NULL test/def/t1 User table 4 32 24
1788 NULL test/def/t1 User table 5 0 8
1789 NULL test/def/t1 User table 6 26 24
1790 select node_id, counter_name, sum(val) from ndbinfo.counters
1791 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
1792 group by node_id, counter_name;
1793 node_id counter_name sum(val)
1794 1 READS 75
1795 1 LOCAL_READS 41
1796 2 READS 1820
1797 2 LOCAL_READS 1793
1798 3 READS 21
1799 3 LOCAL_READS 0
1800 4 READS 287
1801 4 LOCAL_READS 219
1802 5 READS 23
1803 5 LOCAL_READS 0
1804 6 READS 158
1805 6 LOCAL_READS 39
1806 OI scan
1807 explain select * from test.t1 where c > 6;
1808 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1809 1 SIMPLE t1 # range c c 5 NULL # # Using pushed condition (`test`.`t1`.`c` > 6); Using MRR
1810 Warnings:
1811 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (`test`.`t1`.`c` > 6)
1812 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1813 from ndbinfo.operations_per_fragment
1814 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1815 (tot_key_reads > 0 or tot_frag_scans > 0)
1816 group by parent_fq_name, fq_name, type, node_id;
1817 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1818 test/def/t1 sys/def/11/b$unique Unique hash index 2 11 0
1819 test/def/t1 sys/def/11/b$unique Unique hash index 4 16 0
1820 test/def/t1 sys/def/11/b$unique Unique hash index 6 13 0
1821 test/def/t1 sys/def/11/c Ordered index 2 0 8
1822 test/def/t1 sys/def/11/c Ordered index 4 0 8
1823 test/def/t1 sys/def/11/c Ordered index 6 0 8
1824 NULL test/def/t1 User table 1 0 8
1825 NULL test/def/t1 User table 2 22 24
1826 NULL test/def/t1 User table 3 0 8
1827 NULL test/def/t1 User table 4 32 24
1828 NULL test/def/t1 User table 5 0 8
1829 NULL test/def/t1 User table 6 26 24
1830 Table scan
1831 explain select * from test.t1;
1832 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1833 1 SIMPLE t1 # ALL NULL NULL NULL NULL # # NULL
1834 Warnings:
1835 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1`
1836 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1837 from ndbinfo.operations_per_fragment
1838 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1839 (tot_key_reads > 0 or tot_frag_scans > 0)
1840 group by parent_fq_name, fq_name, type, node_id;
1841 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1842 test/def/t1 sys/def/11/b$unique Unique hash index 2 11 0
1843 test/def/t1 sys/def/11/b$unique Unique hash index 4 16 0
1844 test/def/t1 sys/def/11/b$unique Unique hash index 6 13 0
1845 test/def/t1 sys/def/11/c Ordered index 2 0 8
1846 test/def/t1 sys/def/11/c Ordered index 4 0 8
1847 test/def/t1 sys/def/11/c Ordered index 6 0 8
1848 NULL test/def/t1 User table 1 0 8
1849 NULL test/def/t1 User table 2 22 32
1850 NULL test/def/t1 User table 3 0 8
1851 NULL test/def/t1 User table 4 32 32
1852 NULL test/def/t1 User table 5 0 8
1853 NULL test/def/t1 User table 6 26 32
1854 Test pushed queries (SPJ)
1855 Pushed pk->pk
1856 Lookup to get a.d, feeding pk lookup of b.a
1857 explain select a.a, a.d, b.d
1858 from
1859 test.t1 a
1860 join
1861 test.t1 b
1862 on
1863 a.d = b.a
1864 where a.a = 6;
1865 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1866 1 SIMPLE a # eq_ref PRIMARY,a PRIMARY 4 const # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
1867 1 SIMPLE b # eq_ref PRIMARY,a PRIMARY 4 test.a.d # # Child of 'a' in pushed join@1
1868 Warnings:
1869 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`a` = `test`.`a`.`d`) and (`test`.`a`.`a` = 6))
1870 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1871 from ndbinfo.operations_per_fragment
1872 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1873 (tot_key_reads > 0 or tot_frag_scans > 0)
1874 group by parent_fq_name, fq_name, type, node_id;
1875 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1876 test/def/t1 sys/def/11/b$unique Unique hash index 2 11 0
1877 test/def/t1 sys/def/11/b$unique Unique hash index 4 16 0
1878 test/def/t1 sys/def/11/b$unique Unique hash index 6 13 0
1879 test/def/t1 sys/def/11/c Ordered index 2 0 8
1880 test/def/t1 sys/def/11/c Ordered index 4 0 8
1881 test/def/t1 sys/def/11/c Ordered index 6 0 8
1882 NULL test/def/t1 User table 1 0 8
1883 NULL test/def/t1 User table 2 45 40
1884 NULL test/def/t1 User table 3 0 8
1885 NULL test/def/t1 User table 4 64 40
1886 NULL test/def/t1 User table 5 0 8
1887 NULL test/def/t1 User table 6 51 40
1888 Pushed uk->uk
1889 Lookup to get a.d, feeding uk lookup of b.b
1890 explain select a.a, a.d, b.d
1891 from
1892 test.t1 a
1893 join
1894 test.t1 b
1895 on
1896 a.d = b.b
1897 where a.b = 6;
1898 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1899 1 SIMPLE a # ref b b 5 const # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
1900 1 SIMPLE b # ref b b 5 test.a.d # # Child of 'a' in pushed join@1
1901 Warnings:
1902 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`b` = `test`.`a`.`d`) and (`test`.`a`.`b` = 6))
1903 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1904 from ndbinfo.operations_per_fragment
1905 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1906 (tot_key_reads > 0 or tot_frag_scans > 0)
1907 group by parent_fq_name, fq_name, type, node_id;
1908 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1909 test/def/t1 sys/def/11/b$unique Unique hash index 2 34 0
1910 test/def/t1 sys/def/11/b$unique Unique hash index 4 48 0
1911 test/def/t1 sys/def/11/b$unique Unique hash index 6 38 0
1912 test/def/t1 sys/def/11/c Ordered index 2 0 8
1913 test/def/t1 sys/def/11/c Ordered index 4 0 8
1914 test/def/t1 sys/def/11/c Ordered index 6 0 8
1915 NULL test/def/t1 User table 1 0 8
1916 NULL test/def/t1 User table 2 67 40
1917 NULL test/def/t1 User table 3 0 8
1918 NULL test/def/t1 User table 4 96 40
1919 NULL test/def/t1 User table 5 0 8
1920 NULL test/def/t1 User table 6 76 40
1921 Pushed uk->pk
1922 Lookup to get a.d, feeding pk lookup of b.a
1923 explain select a.a, a.d, b.d
1924 from
1925 test.t1 a
1926 join
1927 test.t1 b
1928 on
1929 a.d = b.a
1930 where a.b = 6;
1931 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1932 1 SIMPLE a # ref b b 5 const # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
1933 1 SIMPLE b # eq_ref PRIMARY,a PRIMARY 4 test.a.d # # Child of 'a' in pushed join@1
1934 Warnings:
1935 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`a` = `test`.`a`.`d`) and (`test`.`a`.`b` = 6))
1936 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1937 from ndbinfo.operations_per_fragment
1938 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1939 (tot_key_reads > 0 or tot_frag_scans > 0)
1940 group by parent_fq_name, fq_name, type, node_id;
1941 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1942 test/def/t1 sys/def/11/b$unique Unique hash index 2 45 0
1943 test/def/t1 sys/def/11/b$unique Unique hash index 4 64 0
1944 test/def/t1 sys/def/11/b$unique Unique hash index 6 51 0
1945 test/def/t1 sys/def/11/c Ordered index 2 0 8
1946 test/def/t1 sys/def/11/c Ordered index 4 0 8
1947 test/def/t1 sys/def/11/c Ordered index 6 0 8
1948 NULL test/def/t1 User table 1 0 8
1949 NULL test/def/t1 User table 2 90 40
1950 NULL test/def/t1 User table 3 0 8
1951 NULL test/def/t1 User table 4 128 40
1952 NULL test/def/t1 User table 5 0 8
1953 NULL test/def/t1 User table 6 101 40
1954 Pushed pk->uk
1955 Lookup to get a.d, feeding uk lookup of b.b
1956 explain select a.a, a.d, b.d
1957 from
1958 test.t1 a
1959 join
1960 test.t1 b
1961 on
1962 a.d = b.b
1963 where a.a = 6;
1964 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1965 1 SIMPLE a # eq_ref PRIMARY,a PRIMARY 4 const # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
1966 1 SIMPLE b # ref b b 5 test.a.d # # Child of 'a' in pushed join@1
1967 Warnings:
1968 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`b` = `test`.`a`.`d`) and (`test`.`a`.`a` = 6))
1969 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
1970 from ndbinfo.operations_per_fragment
1971 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
1972 (tot_key_reads > 0 or tot_frag_scans > 0)
1973 group by parent_fq_name, fq_name, type, node_id;
1974 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
1975 test/def/t1 sys/def/11/b$unique Unique hash index 2 57 0
1976 test/def/t1 sys/def/11/b$unique Unique hash index 4 80 0
1977 test/def/t1 sys/def/11/b$unique Unique hash index 6 63 0
1978 test/def/t1 sys/def/11/c Ordered index 2 0 8
1979 test/def/t1 sys/def/11/c Ordered index 4 0 8
1980 test/def/t1 sys/def/11/c Ordered index 6 0 8
1981 NULL test/def/t1 User table 1 0 8
1982 NULL test/def/t1 User table 2 112 40
1983 NULL test/def/t1 User table 3 0 8
1984 NULL test/def/t1 User table 4 160 40
1985 NULL test/def/t1 User table 5 0 8
1986 NULL test/def/t1 User table 6 126 40
1987 Pushed scan originating sub-lookups
1988 Table scan to get a.d, feeding pk lookups of b.a
1989 explain select a.a, a.d, b.d
1990 from
1991 test.t1 a
1992 join
1993 test.t1 b
1994 on
1995 a.d = b.a;
1996 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1997 1 SIMPLE a # ALL NULL NULL NULL NULL # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
1998 1 SIMPLE b # eq_ref PRIMARY,a PRIMARY 4 test.a.d # # Child of 'a' in pushed join@1
1999 Warnings:
2000 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where (`test`.`b`.`a` = `test`.`a`.`d`)
2001 select a.a, a.d, b.d
2002 from
2003 test.t1 a
2004 join
2005 test.t1 b
2006 on
2007 a.d = b.a;
2008 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
2009 from ndbinfo.operations_per_fragment
2010 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
2011 (tot_key_reads > 0 or tot_frag_scans > 0)
2012 group by parent_fq_name, fq_name, type, node_id;
2013 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
2014 test/def/t1 sys/def/11/b$unique Unique hash index 2 57 0
2015 test/def/t1 sys/def/11/b$unique Unique hash index 4 80 0
2016 test/def/t1 sys/def/11/b$unique Unique hash index 6 63 0
2017 test/def/t1 sys/def/11/c Ordered index 2 0 8
2018 test/def/t1 sys/def/11/c Ordered index 4 0 8
2019 test/def/t1 sys/def/11/c Ordered index 6 0 8
2020 NULL test/def/t1 User table 1 0 8
2021 NULL test/def/t1 User table 2 124 48
2022 NULL test/def/t1 User table 3 0 8
2023 NULL test/def/t1 User table 4 176 48
2024 NULL test/def/t1 User table 5 0 8
2025 NULL test/def/t1 User table 6 138 48
2026 Pushed scan originating sub-lookups
2027 Table scan to get a.d, feeding uk lookups of b.b
2028 explain select a.a, a.d, b.d
2029 from
2030 test.t1 a
2031 join
2032 test.t1 b
2033 on
2034 a.d = b.b;
2035 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2036 1 SIMPLE a # ALL NULL NULL NULL NULL # # Parent of 2 pushed join@1; Using pushed condition (`test`.`a`.`d` is not null)
2037 1 SIMPLE b # ref b b 5 test.a.d # # Child of 'a' in pushed join@1
2038 Warnings:
2039 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where (`test`.`b`.`b` = `test`.`a`.`d`)
2040 select a.a, a.d, b.d
2041 from
2042 test.t1 a
2043 join
2044 test.t1 b
2045 on
2046 a.d = b.b;
2047 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
2048 from ndbinfo.operations_per_fragment
2049 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
2050 (tot_key_reads > 0 or tot_frag_scans > 0)
2051 group by parent_fq_name, fq_name, type, node_id;
2052 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
2053 test/def/t1 sys/def/11/b$unique Unique hash index 2 69 0
2054 test/def/t1 sys/def/11/b$unique Unique hash index 4 96 0
2055 test/def/t1 sys/def/11/b$unique Unique hash index 6 75 0
2056 test/def/t1 sys/def/11/c Ordered index 2 0 8
2057 test/def/t1 sys/def/11/c Ordered index 4 0 8
2058 test/def/t1 sys/def/11/c Ordered index 6 0 8
2059 NULL test/def/t1 User table 1 0 8
2060 NULL test/def/t1 User table 2 135 56
2061 NULL test/def/t1 User table 3 0 8
2062 NULL test/def/t1 User table 4 192 56
2063 NULL test/def/t1 User table 5 0 8
2064 NULL test/def/t1 User table 6 150 56
2065 Pushed scan originating sub-scans
2066 Range scan on a.a > 10 getting a.d, feeding range scans on b.c = a.d
2067 explain select a.a, a.d, b.d
2068 from
2069 test.t1 a
2070 join
2071 test.t1 b
2072 on
2073 b.c=a.d
2074 where
2075 a.a>10;
2076 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2077 1 SIMPLE a # range PRIMARY,a PRIMARY 4 NULL # # Parent of 2 pushed join@1; Using pushed condition ((`test`.`a`.`a` > 10) and (`test`.`a`.`d` is not null)); Using MRR
2078 1 SIMPLE b # ref c c 5 test.a.d # # Child of 'a' in pushed join@1
2079 Warnings:
2080 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a`,`test`.`a`.`d` AS `d`,`test`.`b`.`d` AS `d` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`c` = `test`.`a`.`d`) and (`test`.`a`.`a` > 10))
2081 select a.a, a.d, b.d
2082 from
2083 test.t1 a
2084 join
2085 test.t1 b
2086 on
2087 b.c=a.d
2088 where
2089 a.a>10;
2090 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
2091 from ndbinfo.operations_per_fragment
2092 where (parent_fq_name='test/def/t1' OR fq_name='test/def/t1') and
2093 (tot_key_reads > 0 or tot_frag_scans > 0)
2094 group by parent_fq_name, fq_name, type, node_id;
2095 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
2096 test/def/t1 sys/def/11/b$unique Unique hash index 2 69 0
2097 test/def/t1 sys/def/11/b$unique Unique hash index 4 96 0
2098 test/def/t1 sys/def/11/b$unique Unique hash index 6 75 0
2099 test/def/t1 sys/def/11/c Ordered index 2 0 32
2100 test/def/t1 sys/def/11/c Ordered index 4 0 32
2101 test/def/t1 sys/def/11/c Ordered index 6 0 32
2102 test/def/t1 sys/def/11/PRIMARY Ordered index 2 0 8
2103 test/def/t1 sys/def/11/PRIMARY Ordered index 4 0 8
2104 test/def/t1 sys/def/11/PRIMARY Ordered index 6 0 8
2105 NULL test/def/t1 User table 1 0 8
2106 NULL test/def/t1 User table 2 135 56
2107 NULL test/def/t1 User table 3 0 8
2108 NULL test/def/t1 User table 4 192 56
2109 NULL test/def/t1 User table 5 0 8
2110 NULL test/def/t1 User table 6 150 56
2111 Tests on T2 (Blobs)
2112 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
2113 from ndbinfo.operations_per_fragment
2114 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
2115 (tot_key_reads > 0 or tot_frag_scans > 0)
2116 group by parent_fq_name, fq_name, type, node_id;
2117 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
2118 NULL test/def/t2 User table 1 0 4
2119 NULL test/def/t2 User table 2 0 12
2120 NULL test/def/t2 User table 3 0 4
2121 NULL test/def/t2 User table 4 0 12
2122 NULL test/def/t2 User table 5 0 4
2123 NULL test/def/t2 User table 6 0 12
2124 select node_id, counter_name, sum(val) from ndbinfo.counters
2125 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
2126 group by node_id, counter_name;
2127 node_id counter_name sum(val)
2128 1 READS 75
2129 1 LOCAL_READS 41
2130 2 READS 1864
2131 2 LOCAL_READS 1837
2132 3 READS 21
2133 3 LOCAL_READS 0
2134 4 READS 351
2135 4 LOCAL_READS 283
2136 5 READS 23
2137 5 LOCAL_READS 0
2138 6 READS 210
2139 6 LOCAL_READS 91
2140 Test some basic NdbApi
2141 explain select * from test.t2 where t2.a=6;
2142 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2143 1 SIMPLE t2 # eq_ref PRIMARY PRIMARY 4 const # # NULL
2144 Warnings:
2145 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`a` = 6)
2146 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
2147 from ndbinfo.operations_per_fragment
2148 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
2149 (tot_key_reads > 0 or tot_frag_scans > 0)
2150 group by parent_fq_name, fq_name, type, node_id;
2151 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
2152 test/def/t2 test/def/NDB$BLOB_16_3 User table 2 11 0
2153 test/def/t2 test/def/NDB$BLOB_16_3 User table 4 16 0
2154 test/def/t2 test/def/NDB$BLOB_16_3 User table 6 13 0
2155 NULL test/def/t2 User table 1 0 4
2156 NULL test/def/t2 User table 2 22 12
2157 NULL test/def/t2 User table 3 0 4
2158 NULL test/def/t2 User table 4 32 12
2159 NULL test/def/t2 User table 5 0 4
2160 NULL test/def/t2 User table 6 26 12
2161 select node_id, counter_name, sum(val) from ndbinfo.counters
2162 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
2163 group by node_id, counter_name;
2164 node_id counter_name sum(val)
2165 1 READS 75
2166 1 LOCAL_READS 41
2167 2 READS 1888
2168 2 LOCAL_READS 1860
2169 3 READS 21
2170 3 LOCAL_READS 0
2171 4 READS 383
2172 4 LOCAL_READS 315
2173 5 READS 23
2174 5 LOCAL_READS 0
2175 6 READS 236
2176 6 LOCAL_READS 117
2177 UI lookups
2178 explain select * from test.t2 where t2.b=6;
2179 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2180 1 SIMPLE t2 # ref b b 5 const # # NULL
2181 Warnings:
2182 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 6)
2183 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
2184 from ndbinfo.operations_per_fragment
2185 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
2186 (tot_key_reads > 0 or tot_frag_scans > 0)
2187 group by parent_fq_name, fq_name, type, node_id;
2188 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
2189 test/def/t2 test/def/NDB$BLOB_16_3 User table 2 22 0
2190 test/def/t2 test/def/NDB$BLOB_16_3 User table 4 32 0
2191 test/def/t2 test/def/NDB$BLOB_16_3 User table 6 26 0
2192 test/def/t2 sys/def/16/b$unique Unique hash index 2 22 0
2193 test/def/t2 sys/def/16/b$unique Unique hash index 4 32 0
2194 test/def/t2 sys/def/16/b$unique Unique hash index 6 26 0
2195 NULL test/def/t2 User table 1 0 4
2196 NULL test/def/t2 User table 2 44 12
2197 NULL test/def/t2 User table 3 0 4
2198 NULL test/def/t2 User table 4 64 12
2199 NULL test/def/t2 User table 5 0 4
2200 NULL test/def/t2 User table 6 52 12
2201 select node_id, counter_name, sum(val) from ndbinfo.counters
2202 where counter_name = 'READS' or counter_name = 'LOCAL_READS'
2203 group by node_id, counter_name;
2204 node_id counter_name sum(val)
2205 1 READS 75
2206 1 LOCAL_READS 41
2207 2 READS 1943
2208 2 LOCAL_READS 1915
2209 3 READS 21
2210 3 LOCAL_READS 0
2211 4 READS 463
2212 4 LOCAL_READS 395
2213 5 READS 23
2214 5 LOCAL_READS 0
2215 6 READS 301
2216 6 LOCAL_READS 182
2217 OI scan
2218 explain select * from test.t2 where c > 6;
2219 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2220 1 SIMPLE t2 # range c c 5 NULL # # Using pushed condition (`test`.`t2`.`c` > 6)
2221 Warnings:
2222 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`c` > 6)
2223 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
2224 from ndbinfo.operations_per_fragment
2225 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
2226 (tot_key_reads > 0 or tot_frag_scans > 0)
2227 group by parent_fq_name, fq_name, type, node_id;
2228 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
2229 test/def/t2 test/def/NDB$BLOB_16_3 User table 2 32 0
2230 test/def/t2 test/def/NDB$BLOB_16_3 User table 4 45 0
2231 test/def/t2 test/def/NDB$BLOB_16_3 User table 6 38 0
2232 test/def/t2 sys/def/16/b$unique Unique hash index 2 22 0
2233 test/def/t2 sys/def/16/b$unique Unique hash index 4 32 0
2234 test/def/t2 sys/def/16/b$unique Unique hash index 6 26 0
2235 test/def/t2 sys/def/16/c Ordered index 2 0 8
2236 test/def/t2 sys/def/16/c Ordered index 4 0 8
2237 test/def/t2 sys/def/16/c Ordered index 6 0 8
2238 NULL test/def/t2 User table 1 0 4
2239 NULL test/def/t2 User table 2 44 12
2240 NULL test/def/t2 User table 3 0 4
2241 NULL test/def/t2 User table 4 64 12
2242 NULL test/def/t2 User table 5 0 4
2243 NULL test/def/t2 User table 6 52 12
2244 Table scan
2245 explain select * from test.t2;
2246 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2247 1 SIMPLE t2 # ALL NULL NULL NULL NULL # # NULL
2248 Warnings:
2249 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`
2250 select parent_fq_name, fq_name, type, node_id, sum(tot_key_reads), sum(tot_frag_scans)
2251 from ndbinfo.operations_per_fragment
2252 where (parent_fq_name='test/def/t2' OR fq_name='test/def/t2') and
2253 (tot_key_reads > 0 or tot_frag_scans > 0)
2254 group by parent_fq_name, fq_name, type, node_id;
2255 parent_fq_name fq_name type node_id sum(tot_key_reads) sum(tot_frag_scans)
2256 test/def/t2 test/def/NDB$BLOB_16_3 User table 2 43 0
2257 test/def/t2 test/def/NDB$BLOB_16_3 User table 4 61 0
2258 test/def/t2 test/def/NDB$BLOB_16_3 User table 6 51 0
2259 test/def/t2 sys/def/16/b$unique Unique hash index 2 22 0
2260 test/def/t2 sys/def/16/b$unique Unique hash index 4 32 0
2261 test/def/t2 sys/def/16/b$unique Unique hash index 6 26 0
2262 test/def/t2 sys/def/16/c Ordered index 2 0 8
2263 test/def/t2 sys/def/16/c Ordered index 4 0 8
2264 test/def/t2 sys/def/16/c Ordered index 6 0 8
2265 NULL test/def/t2 User table 1 0 4
2266 NULL test/def/t2 User table 2 44 20
2267 NULL test/def/t2 User table 3 0 4
2268 NULL test/def/t2 User table 4 64 20
2269 NULL test/def/t2 User table 5 0 4
2270 NULL test/def/t2 User table 6 52 20
2271 drop table hints;
2272 delete from t1;
2273 delete from t2;
2274 drop table test.t1;
2275 drop table test.t2;