Edited at

MySQLでDBとテーブルのサイズを確認するSQL

メモ。たまにコピペして使います。


DBのサイズ

SELECT 

table_schema, sum(data_length) /1024/1024 AS mb
FROM
information_schema.tables
GROUP BY
table_schema
ORDER BY
sum(data_length+index_length) DESC;


Tableのサイズ

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=database()
ORDER BY
(data_length+index_length) DESC;