result_format: 2 use ndbinfo; ## Look for "Extra: Select tables optimized away" in the following ## ``EXPLAIN SELECT COUNT(*)'' from compiled-in ndbinfo tables ## ## ndb$tables select count(*) from ndb$tables; count(*) 53 explain select count(*) from ndb$tables; id 1 select_type SIMPLE table NULL partitions NULL type NULL possible_keys NULL key NULL key_len NULL ref NULL rows NULL filtered NULL Extra Select tables optimized away Warnings: Level Note Code 1003 Message /* select#1 */ select count(0) AS `count(*)` from `ndbinfo`.`ndb$tables` ## dict_obj_types select count(*) from dict_obj_types; count(*) 20 Select tables optimized away ## error_messages Select tables optimized away ## config_params Select tables optimized away ## blocks Select tables optimized away ## backup_id Select tables optimized away ## `EXPLAIN SELECT COUNT(*)'' from a normal ndbinfo table. Extra will be NULL. NULL ## Information schema reveals row counts as seen by optimizer ## (but filter out cpu-related tables where results are not predictable) set ndbinfo_show_hidden=1; SELECT table_name, table_rows, avg_row_length FROM information_schema.tables WHERE table_schema='ndbinfo' AND table_type = 'BASE TABLE' AND table_name not like '%cpu%' ORDER BY table_name; TABLE_NAME TABLE_ROWS AVG_ROW_LENGTH blobs 10 84 dictionary_columns 200 84 dictionary_tables 40 192 events 40 48 foreign_keys 10 124 index_columns 20 80 ndb$acc_operations 15 64 ndb$backup_id 1 20 ndb$blocks 29 20 ndb$columns 576 44 ndb$config_nodes 34 28 ndb$config_params 168 120 ndb$config_values 332 24 ndb$counters 200 24 ndb$dblqh_tcconnect_state 19 52 ndb$dbtc_apiconnect_state 25 52 ndb$dict_obj_info 43 40 ndb$dict_obj_types 20 20 ndb$disk_write_speed_aggregate 8 120 ndb$disk_write_speed_base 488 48 ndb$diskpagebuffer 10 64 ndb$diskstat 10 48 ndb$diskstats_1sec 200 52 ndb$error_messages 793 52 ndb$frag_locks 344 96 ndb$frag_mem_use 344 100 ndb$frag_operations 344 192 ndb$hwinfo 2 44 ndb$index_stats 64 20 ndb$logbuffers 8 40 ndb$logspaces 8 40 ndb$membership 2 88 ndb$nodes 2 24 ndb$operations 10 48 ndb$pgman_time_track_stats 200 48 ndb$pools 384 84 ndb$processes 34 68 ndb$resources 18 28 ndb$restart_info 2 100 ndb$stored_tables 43 80 ndb$table_distribution_status 43 52 ndb$table_distribution_status_all 86 52 ndb$table_fragments 344 60 ndb$table_fragments_all 344 60 ndb$table_replicas 344 64 ndb$table_replicas_all 344 64 ndb$tables 53 40 ndb$tc_time_track_stats 384 104 ndb$test 8000 24 ndb$threadblock_details 124 24 ndb$threadblocks 124 16 ndb$threads 26 40 ndb$threadstat 22 144 ndb$transactions 5 44 ndb$transactions_full 5 44 ndb$transporter_activity 10240 40 ndb$transporter_details 32 124 ndb$transporters 32 64 ## List the tables where estimated size equals actual size. CALL populate_sizes(); SELECT count(*) from rowcounts WHERE est_rows = actual_rows; count(*) 29 SELECT table_name from rowcounts WHERE est_rows = actual_rows ORDER BY table_name; table_name ndb$backup_id ndb$blocks ndb$columns ndb$config_nodes ndb$config_params ndb$config_values ndb$counters ndb$dblqh_tcconnect_state ndb$dbtc_apiconnect_state ndb$dict_obj_types ndb$disk_write_speed_aggregate ndb$disk_write_speed_base ndb$diskpagebuffer ndb$diskstat ndb$diskstats_1sec ndb$error_messages ndb$hwinfo ndb$logbuffers ndb$logspaces ndb$membership ndb$nodes ndb$pgman_time_track_stats ndb$pools ndb$resources ndb$tables ndb$test ndb$threads ndb$threadstat ndb$transporters ## Check that no tables have zero estimates SELECT table_name from rowcounts WHERE est_rows = 0; table_name DROP TEMPORARY TABLE rowcounts; DROP PROCEDURE populate_sizes; ## Show the indexes on virtual tables SELECT table_name, constraint_name, column_name, ordinal_position FROM information_schema.key_column_usage WHERE table_schema = 'ndbinfo' ORDER BY table_name; TABLE_NAME CONSTRAINT_NAME COLUMN_NAME ORDINAL_POSITION dictionary_tables PRIMARY table_id 1 events PRIMARY event_id 1 foreign_keys PRIMARY object_id 1 ndb$blocks PRIMARY block_number 1 ndb$config_params PRIMARY param_number 1 ndb$dblqh_tcconnect_state PRIMARY state_int_value 1 ndb$dbtc_apiconnect_state PRIMARY state_int_value 1 ndb$dict_obj_types PRIMARY type_id 1 SELECT * FROM blocks ORDER BY block_number DESC; block_number block_name 272 QRESTORE 271 QBACKUP 270 DBQTUX 269 DBQTUP 268 DBQACC 267 DBQLQH 266 TRPMAN 265 THRMAN 264 DBSPJ 263 DBINFO 262 RESTORE 261 PGMAN 260 LGMAN 259 TSMAN 258 DBTUX 257 SUMA 256 DBUTIL 255 TRIX 254 CMVMI 253 NDBFS 252 QMGR 251 NDBCNTR 250 DBDICT 249 DBTUP 248 DBACC 247 DBLQH 246 DBDIH 245 DBTC 244 BACKUP ## Query results validate that index seek operations are correct, ## and plans show use of the index select * from blocks where block_number=245; block_number 245 block_name DBTC PRIMARY select * from blocks where block_number in (250,251); block_number 250 block_name DBDICT block_number 251 block_name NDBCNTR explain format=tree select * from blocks where block_number in (250,251); EXPLAIN -> Filter: (ndb$blocks.block_number in (250,251)) (rows=2) -> Index range scan on ndb$blocks using PRIMARY over (block_number = 250) OR (block_number = 251) (rows=2) explain format=tree select * from blocks order by block_number; EXPLAIN -> Index scan on ndb$blocks using PRIMARY (rows=29) explain format=tree select * from blocks where block_number < 250; EXPLAIN -> Filter: (ndb$blocks.block_number < 250) (rows=10) -> Index range scan on ndb$blocks using PRIMARY over (block_number < 250) (rows=10) ## Can scan backwards: explain format=tree select * from blocks where block_number > 250 order by block_number desc; EXPLAIN -> Filter: (ndb$blocks.block_number > 250) (rows=10) -> Index range scan on ndb$blocks using PRIMARY over (250 < block_number) (reverse) (rows=10) select * from dict_obj_types where type_id = 6; type_id 6 type_name Ordered index ## No row at 7: select * from dict_obj_types where type_id = 7; select * from dict_obj_types where type_id in (1,2,6); type_id 1 type_name System table type_id 2 type_name User table type_id 6 type_name Ordered index PRIMARY select state_int_value, state_name from ndb$dblqh_tcconnect_state where state_int_value = 9; state_int_value 9 state_name LOG_COMMIT_QUEUED_WAIT_SIGNAL select param_number, param_name from ndb$config_params where param_number = 161; param_number 161 param_name StringMemory PRIMARY ## No row: select param_name from ndb$config_params where param_number = 1; select param_name from ndb$config_params where param_number = 40000; ## The plan for counters explain format=tree select * from counters; EXPLAIN -> Nested loop left join (rows=200) -> Table scan on c (rows=200) -> Single-row index lookup on b using PRIMARY (block_number=c.block_number) (rows=1) ## The plan for memory_per_fragment explain format=tree select * from memory_per_fragment; EXPLAIN -> Left hash join (parent_name.`type` = `name`.parent_obj_type), (parent_name.id = `name`.parent_obj_id) (rows=21200) -> Inner hash join (space.table_id = `name`.id) (rows=493) -> Table scan on space (rows=344) -> Hash -> Nested loop inner join (rows=14.3) -> Filter: ((`name`.`type` <= 6) and (`name`.`type` is not null)) (rows=14.3) -> Table scan on name (rows=43) -> Single-row index lookup on types using PRIMARY (type_id=`name`.`type`) (rows=1) -> Hash -> Table scan on parent_name (rows=43) ## WL#11968 tables and views ## SELECT e.name, table_name FROM events e JOIN dictionary_tables t using(table_id) WHERE event_id IN () ORDER BY table_name; name table_name REPL$mysql/ndb_apply_status ndb_apply_status ndb_index_stat_head_event ndb_index_stat_head REPL$mysql/ndb_schema ndb_schema REPL$mysql/ndb_schema_result ndb_schema_result ## Query uses primary keys on both tables: EXPLAIN SELECT event_id, e.name, table_name FROM events e JOIN dictionary_tables t using(table_id) WHERE event_id IN () ORDER BY event_id; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE e NULL range PRIMARY PRIMARY 4 NULL 4 100.00 Using where 1 SIMPLE t NULL eq_ref PRIMARY PRIMARY 4 ndbinfo.e.table_id 1 100.00 NULL Warnings: 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 ())) order by `ndbinfo`.`e`.`event_id` SELECT * from events where event_id = -1; event_id name table_id reporting columns table_event SELECT table_event from events where event_id = 1; table_event ALL SELECT table_name from dictionary_tables where table_id = -1; table_name SELECT table_name from dictionary_tables where table_id = 9990; table_name SELECT table_name from dictionary_tables where table_id = 5; table_name NDB$BLOB_4_3 SELECT table_name from dictionary_tables where table_id = 6; table_name ndb_schema_result select * from blobs; table_id database_name table_name column_id column_name inline_size part_size stripe_size blob_table_name 4 mysql ndb_schema 3 query 256 2000 0 NDB$BLOB_4_3 select * from index_columns order by index_object_id; table_id database_name table_name index_object_id index_name index_type status columns 8 mysql ndb_index_stat_sample 9 ndb_index_stat_sample_x1 6 retrieved index_id,index_version,sample_version 11 mysql ndb_sql_metadata 12 PRIMARY 6 retrieved type,name,seq select * from hash_maps order by id limit 1; id version state fq_name 1 1 4 DEFAULT-HASHMAP-3840-8 ## Create an all-MyISAM version of memory_per_fragment for comparison ## (now with a primary key in the MyISAM version as well) ## and explain the same query explain format=tree select * from memory_per_fragment; EXPLAIN -> Inner hash join (space.table_id = `name`.id) (rows=1150) -> Table scan on space (rows=176) -> Hash -> Left hash join (parent_name.`type` = `name`.parent_obj_type), (parent_name.id = `name`.parent_obj_id) (rows=65.3) -> Nested loop inner join (rows=4.67) -> Filter: ((`name`.`type` <= 6) and (`name`.`type` is not null)) (rows=4.67) -> Table scan on name (rows=14) -> Single-row index lookup on types using PRIMARY (type_id=`name`.`type`) (rows=1) -> Hash -> Table scan on parent_name (rows=14) ## CLEANUP