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