View source for mysql database size analysis

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

[[category:mysql]]