FAQですね。
information_schema.tables とは
mysql> desc information_schema.tables; +-----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | TABLE_TYPE | varchar(64) | NO | | | | | ENGINE | varchar(64) | YES | | NULL | | | VERSION | bigint(21) unsigned | YES | | NULL | | | ROW_FORMAT | varchar(10) | YES | | NULL | | | TABLE_ROWS | bigint(21) unsigned | YES | | NULL | | | AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_FREE | bigint(21) unsigned | YES | | NULL | | | AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | | | CREATE_TIME | datetime | YES | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | TABLE_COLLATION | varchar(32) | YES | | NULL | | | CHECKSUM | bigint(21) unsigned | YES | | NULL | | | CREATE_OPTIONS | varchar(255) | YES | | NULL | | | TABLE_COMMENT | varchar(80) | NO | | | | +-----------------+---------------------+------+-----+---------+-------+
information_schema.tablesによる table sizeの表示
mysql> select
-> table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,
-> floor((data_length+index_length)/1024/1024) as allMB,
-> floor((data_length)/1024/1024) as dMB,
-> floor((index_length)/1024/1024) as iMB
-> from information_schema.tables
-> where table_schema='$db_name'
-> order by (data_length+index_length) desc;
+-------------------------------+--------+----------+--------+-------+------+------+
| table_name | engine | tbl_rows | rlen | allMB | dMB | iMB |
+-------------------------------+--------+----------+--------+-------+------+------+
| h_juchuu_meisai | InnoDB | 1192379 | 166 | 189 | 189 | 0 |
| juchuu_meisai | InnoDB | 832510 | 167 | 132 | 132 | 0 |
| osaka_tanka | InnoDB | 463869 | 153 | 67 | 67 | 0 |
| hachuu_meisai | InnoDB | 357524 | 160 | 54 | 54 | 0 |
| h_hachuu_meisai | InnoDB | 286451 | 198 | 54 | 54 | 0 |
| tokyo_tanka | InnoDB | 324704 | 154 | 47 | 47 | 0 |
| juchuu | InnoDB | 106038 | 430 | 47 | 43 | 3 |
| h_juchuu | InnoDB | 100298 | 413 | 43 | 39 | 3 |
| heim_tei_info_2 | InnoDB | 101526 | 325 | 31 | 31 | 0 |
| juchuu_summary | InnoDB | 98031 | 326 | 30 | 30 | 0 |
| hachuu | InnoDB | 129771 | 166 | 24 | 20 | 3 |
| : | : | : | : | : | : | : |
