mysql database size analysis
From thelinuxwiki
report on database size
SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(FORMAT(SXSize/ POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1), 'B') "Total Size" FROM (SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize, SUM(XSize) SXSize,SUM(TSize) STSize FROM (SELECT table_schema DB, data_length DSize,index_length XSize,data_length+index_length TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema')) AAA GROUP BY DB WITH ROLLUP) AA, (SELECT 3 pw) BB ORDER BY (SDSize+SXSize);
sample output
+------------------------+----------------------+----------------------+----------------------+ | DBName | Data Size | Index Size | Total Size | +------------------------+----------------------+----------------------+----------------------+ | onlinebookmarks | 0.000 GB | 0.000 GB | 0.000 GB | | mw_bjjwiki | 0.094 GB | 0.047 GB | 0.140 GB | | All Databases | 0.348 GB | 0.101 GB | 0.449 GB | +------------------------+----------------------+----------------------+----------------------+