Debian Codegraph @

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

raw
1 result_format: 2
2 use ndbinfo;
3 ## Look for "Extra: Select tables optimized away" in the following
4 ## ``EXPLAIN SELECT COUNT(*)'' from compiled-in ndbinfo tables
5 ##
6 ## ndb$tables
7 select count(*) from ndb$tables;
8 count(*) 53
9 explain select count(*) from ndb$tables;
10 id 1
11 select_type SIMPLE
12 table NULL
13 partitions NULL
14 type NULL
15 possible_keys NULL
16 key NULL
17 key_len NULL
18 ref NULL
19 rows NULL
20 filtered NULL
21 Extra Select tables optimized away
22 Warnings:
23 Level Note
24 Code 1003
25 Message /* select#1 */ select count(0) AS `count(*)` from `ndbinfo`.`ndb$tables`
26
27 ## dict_obj_types
28 select count(*) from dict_obj_types;
29 count(*) 20
30 Select tables optimized away
31
32 ## error_messages
33 Select tables optimized away
34
35 ## config_params
36 Select tables optimized away
37
38 ## blocks
39 Select tables optimized away
40
41 ## backup_id
42 Select tables optimized away
43
44 ## `EXPLAIN SELECT COUNT(*)'' from a normal ndbinfo table. Extra will be NULL.
45 NULL
46
47 ## Information schema reveals row counts as seen by optimizer
48 ## (but filter out cpu-related tables where results are not predictable)
49 set ndbinfo_show_hidden=1;
50 SELECT table_name, table_rows, avg_row_length
51 FROM information_schema.tables
52 WHERE table_schema='ndbinfo' AND table_type = 'BASE TABLE'
53 AND table_name not like '%cpu%'
54 ORDER BY table_name;
55 TABLE_NAME TABLE_ROWS AVG_ROW_LENGTH
56 blobs 10 84
57 dictionary_columns 200 84
58 dictionary_tables 40 192
59 events 40 48
60 foreign_keys 10 124
61 index_columns 20 80
62 ndb$acc_operations 15 64
63 ndb$backup_id 1 20
64 ndb$blocks 29 20
65 ndb$columns 576 44
66 ndb$config_nodes 34 28
67 ndb$config_params 168 120
68 ndb$config_values 332 24
69 ndb$counters 200 24
70 ndb$dblqh_tcconnect_state 19 52
71 ndb$dbtc_apiconnect_state 25 52
72 ndb$dict_obj_info 43 40
73 ndb$dict_obj_types 20 20
74 ndb$disk_write_speed_aggregate 8 120
75 ndb$disk_write_speed_base 488 48
76 ndb$diskpagebuffer 10 64
77 ndb$diskstat 10 48
78 ndb$diskstats_1sec 200 52
79 ndb$error_messages 793 52
80 ndb$frag_locks 344 96
81 ndb$frag_mem_use 344 100
82 ndb$frag_operations 344 192
83 ndb$hwinfo 2 44
84 ndb$index_stats 64 20
85 ndb$logbuffers 8 40
86 ndb$logspaces 8 40
87 ndb$membership 2 88
88 ndb$nodes 2 24
89 ndb$operations 10 48
90 ndb$pgman_time_track_stats 200 48
91 ndb$pools 384 84
92 ndb$processes 34 68
93 ndb$resources 18 28
94 ndb$restart_info 2 100
95 ndb$stored_tables 43 80
96 ndb$table_distribution_status 43 52
97 ndb$table_distribution_status_all 86 52
98 ndb$table_fragments 344 60
99 ndb$table_fragments_all 344 60
100 ndb$table_replicas 344 64
101 ndb$table_replicas_all 344 64
102 ndb$tables 53 40
103 ndb$tc_time_track_stats 384 104
104 ndb$test 8000 24
105 ndb$threadblock_details 124 24
106 ndb$threadblocks 124 16
107 ndb$threads 26 40
108 ndb$threadstat 22 144
109 ndb$transactions 5 44
110 ndb$transactions_full 5 44
111 ndb$transporter_activity 10240 40
112 ndb$transporter_details 32 124
113 ndb$transporters 32 64
114
115 ## List the tables where estimated size equals actual size.
116 CALL populate_sizes();
117 SELECT count(*) from rowcounts WHERE est_rows = actual_rows;
118 count(*)
119 29
120 SELECT table_name from rowcounts WHERE est_rows = actual_rows
121 ORDER BY table_name;
122 table_name
123 ndb$backup_id
124 ndb$blocks
125 ndb$columns
126 ndb$config_nodes
127 ndb$config_params
128 ndb$config_values
129 ndb$counters
130 ndb$dblqh_tcconnect_state
131 ndb$dbtc_apiconnect_state
132 ndb$dict_obj_types
133 ndb$disk_write_speed_aggregate
134 ndb$disk_write_speed_base
135 ndb$diskpagebuffer
136 ndb$diskstat
137 ndb$diskstats_1sec
138 ndb$error_messages
139 ndb$hwinfo
140 ndb$logbuffers
141 ndb$logspaces
142 ndb$membership
143 ndb$nodes
144 ndb$pgman_time_track_stats
145 ndb$pools
146 ndb$resources
147 ndb$tables
148 ndb$test
149 ndb$threads
150 ndb$threadstat
151 ndb$transporters
152
153 ## Check that no tables have zero estimates
154 SELECT table_name from rowcounts WHERE est_rows = 0;
155 table_name
156
157 DROP TEMPORARY TABLE rowcounts;
158 DROP PROCEDURE populate_sizes;
159
160 ## Show the indexes on virtual tables
161 SELECT table_name, constraint_name, column_name, ordinal_position
162 FROM information_schema.key_column_usage
163 WHERE table_schema = 'ndbinfo'
164 ORDER BY table_name;
165 TABLE_NAME CONSTRAINT_NAME COLUMN_NAME ORDINAL_POSITION
166 dictionary_tables PRIMARY table_id 1
167 events PRIMARY event_id 1
168 foreign_keys PRIMARY object_id 1
169 ndb$blocks PRIMARY block_number 1
170 ndb$config_params PRIMARY param_number 1
171 ndb$dblqh_tcconnect_state PRIMARY state_int_value 1
172 ndb$dbtc_apiconnect_state PRIMARY state_int_value 1
173 ndb$dict_obj_types PRIMARY type_id 1
174
175 SELECT * FROM blocks ORDER BY block_number DESC;
176 block_number block_name
177 272 QRESTORE
178 271 QBACKUP
179 270 DBQTUX
180 269 DBQTUP
181 268 DBQACC
182 267 DBQLQH
183 266 TRPMAN
184 265 THRMAN
185 264 DBSPJ
186 263 DBINFO
187 262 RESTORE
188 261 PGMAN
189 260 LGMAN
190 259 TSMAN
191 258 DBTUX
192 257 SUMA
193 256 DBUTIL
194 255 TRIX
195 254 CMVMI
196 253 NDBFS
197 252 QMGR
198 251 NDBCNTR
199 250 DBDICT
200 249 DBTUP
201 248 DBACC
202 247 DBLQH
203 246 DBDIH
204 245 DBTC
205 244 BACKUP
206
207 ## Query results validate that index seek operations are correct,
208 ## and plans show use of the index
209 select * from blocks where block_number=245;
210 block_number 245
211 block_name DBTC
212 PRIMARY
213
214 select * from blocks where block_number in (250,251);
215 block_number 250
216 block_name DBDICT
217 block_number 251
218 block_name NDBCNTR
219
220 explain format=tree select * from blocks where block_number in (250,251);
221 EXPLAIN -> Filter: (ndb$blocks.block_number in (250,251)) (rows=2)
222 -> Index range scan on ndb$blocks using PRIMARY over (block_number = 250) OR (block_number = 251) (rows=2)
223
224
225 explain format=tree select * from blocks order by block_number;
226 EXPLAIN -> Index scan on ndb$blocks using PRIMARY (rows=29)
227
228
229 explain format=tree select * from blocks where block_number < 250;
230 EXPLAIN -> Filter: (ndb$blocks.block_number < 250) (rows=10)
231 -> Index range scan on ndb$blocks using PRIMARY over (block_number < 250) (rows=10)
232
233
234 ## Can scan backwards:
235 explain format=tree select * from blocks where block_number > 250
236 order by block_number desc;
237 EXPLAIN -> Filter: (ndb$blocks.block_number > 250) (rows=10)
238 -> Index range scan on ndb$blocks using PRIMARY over (250 < block_number) (reverse) (rows=10)
239
240
241 select * from dict_obj_types where type_id = 6;
242 type_id 6
243 type_name Ordered index
244
245 ## No row at 7:
246 select * from dict_obj_types where type_id = 7;
247
248 select * from dict_obj_types where type_id in (1,2,6);
249 type_id 1
250 type_name System table
251 type_id 2
252 type_name User table
253 type_id 6
254 type_name Ordered index
255
256 PRIMARY
257
258 select state_int_value, state_name from ndb$dblqh_tcconnect_state where state_int_value = 9;
259 state_int_value 9
260 state_name LOG_COMMIT_QUEUED_WAIT_SIGNAL
261
262 select param_number, param_name from ndb$config_params where param_number = 161;
263 param_number 161
264 param_name StringMemory
265 PRIMARY
266
267 ## No row:
268 select param_name from ndb$config_params where param_number = 1;
269 select param_name from ndb$config_params where param_number = 40000;
270
271 ## The plan for counters
272 explain format=tree select * from counters;
273 EXPLAIN -> Nested loop left join (rows=200)
274 -> Table scan on c (rows=200)
275 -> Single-row index lookup on b using PRIMARY (block_number=c.block_number) (rows=1)
276
277
278 ## The plan for memory_per_fragment
279 explain format=tree select * from memory_per_fragment;
280 EXPLAIN -> Left hash join (parent_name.`type` = `name`.parent_obj_type), (parent_name.id = `name`.parent_obj_id) (rows=21200)
281 -> Inner hash join (space.table_id = `name`.id) (rows=493)
282 -> Table scan on space (rows=344)
283 -> Hash
284 -> Nested loop inner join (rows=14.3)
285 -> Filter: ((`name`.`type` <= 6) and (`name`.`type` is not null)) (rows=14.3)
286 -> Table scan on name (rows=43)
287 -> Single-row index lookup on types using PRIMARY (type_id=`name`.`type`) (rows=1)
288 -> Hash
289 -> Table scan on parent_name (rows=43)
290
291
292 ## WL#11968 tables and views
293 ##
294 SELECT e.name, table_name
295 FROM events e JOIN dictionary_tables t using(table_id)
296 WHERE event_id IN (<event_ids>) ORDER BY table_name;
297 name table_name
298 REPL$mysql/ndb_apply_status ndb_apply_status
299 ndb_index_stat_head_event ndb_index_stat_head
300 REPL$mysql/ndb_schema ndb_schema
301 REPL$mysql/ndb_schema_result ndb_schema_result
302
303 ## Query uses primary keys on both tables:
304 EXPLAIN SELECT event_id, e.name, table_name FROM events e
305 JOIN dictionary_tables t using(table_id)
306 WHERE event_id IN (<event_ids>) ORDER BY event_id;
307 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
308 1 SIMPLE e NULL range PRIMARY PRIMARY 4 NULL 4 100.00 Using where
309 1 SIMPLE t NULL eq_ref PRIMARY PRIMARY 4 ndbinfo.e.table_id 1 100.00 NULL
310 Warnings:
311 Note 1003 /* select#1 */ select `ndbinfo`.`e`.`event_id` AS `event_id`,`ndbinfo`.`e`.`name` AS `name`,`ndbinfo`.`t`.`table_name` AS `table_name` from `ndbinfo`.`events` `e` join `ndbinfo`.`dictionary_tables` `t` where ((`ndbinfo`.`t`.`table_id` = `ndbinfo`.`e`.`table_id`) and (`ndbinfo`.`e`.`event_id` in (<event_ids>))) order by `ndbinfo`.`e`.`event_id`
312
313 SELECT * from events where event_id = -1;
314 event_id name table_id reporting columns table_event
315 SELECT table_event from events where event_id = 1;
316 table_event
317 ALL
318 SELECT table_name from dictionary_tables where table_id = -1;
319 table_name
320 SELECT table_name from dictionary_tables where table_id = 9990;
321 table_name
322 SELECT table_name from dictionary_tables where table_id = 5;
323 table_name
324 NDB$BLOB_4_3
325 SELECT table_name from dictionary_tables where table_id = 6;
326 table_name
327 ndb_schema_result
328
329 select * from blobs;
330 table_id database_name table_name column_id column_name inline_size part_size stripe_size blob_table_name
331 4 mysql ndb_schema 3 query 256 2000 0 NDB$BLOB_4_3
332 select * from index_columns order by index_object_id;
333 table_id database_name table_name index_object_id index_name index_type status columns
334 8 mysql ndb_index_stat_sample 9 ndb_index_stat_sample_x1 6 retrieved index_id,index_version,sample_version
335 11 mysql ndb_sql_metadata 12 PRIMARY 6 retrieved type,name,seq
336 select * from hash_maps order by id limit 1;
337 id version state fq_name
338 1 1 4 DEFAULT-HASHMAP-3840-8
339
340 ## Create an all-MyISAM version of memory_per_fragment for comparison
341 ## (now with a primary key in the MyISAM version as well)
342 ## and explain the same query
343 explain format=tree select * from memory_per_fragment;
344 EXPLAIN -> Inner hash join (space.table_id = `name`.id) (rows=1150)
345 -> Table scan on space (rows=176)
346 -> Hash
347 -> Left hash join (parent_name.`type` = `name`.parent_obj_type), (parent_name.id = `name`.parent_obj_id) (rows=65.3)
348 -> Nested loop inner join (rows=4.67)
349 -> Filter: ((`name`.`type` <= 6) and (`name`.`type` is not null)) (rows=4.67)
350 -> Table scan on name (rows=14)
351 -> Single-row index lookup on types using PRIMARY (type_id=`name`.`type`) (rows=1)
352 -> Hash
353 -> Table scan on parent_name (rows=14)
354
355
356 ## CLEANUP