http://www.thelinuxwiki.com/index.php?action=history&feed=atom&title=mysql_database_size_analysis 2026-04-29T01:27:27Z Revision history for this page on the wiki MediaWiki 1.21.5 http://www.thelinuxwiki.com/index.php?title=mysql_database_size_analysis&diff=985&oldid=prev 2016-05-09T03:01:40Z

<p>Created page with &quot;report on database size SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,&#039; &#039;),&#039; &#039;, SUBSTR(&#039; KMGTP&#039;,pw+1,1),&#039;B&#039;) &quot;Data Size&quot;,CONCAT(LPAD(FORMAT(SXSize/ POWER(10...&quot;</p> <p><b>New page</b></p><div>report on database size<br /> <br /> SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',<br /> SUBSTR(' KMGTP',pw+1,1),'B') &quot;Data Size&quot;,CONCAT(LPAD(FORMAT(SXSize/<br /> POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') &quot;Index Size&quot;,<br /> CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),<br /> 'B') &quot;Total Size&quot; FROM (SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,<br /> SUM(XSize) SXSize,SUM(TSize) STSize FROM (SELECT table_schema DB,<br /> data_length DSize,index_length XSize,data_length+index_length TSize<br /> FROM information_schema.tables WHERE table_schema NOT IN<br /> ('mysql','information_schema')) AAA GROUP BY DB WITH ROLLUP) AA,<br /> (SELECT 3 pw) BB ORDER BY (SDSize+SXSize);<br /> <br /> <br /> sample output<br /> <br /> +------------------------+----------------------+----------------------+----------------------+<br /> | DBName | Data Size | Index Size | Total Size |<br /> +------------------------+----------------------+----------------------+----------------------+<br /> | onlinebookmarks | 0.000 GB | 0.000 GB | 0.000 GB |<br /> | mw_bjjwiki | 0.094 GB | 0.047 GB | 0.140 GB |<br /> | All Databases | 0.348 GB | 0.101 GB | 0.449 GB |<br /> +------------------------+----------------------+----------------------+----------------------+<br /> <br /> [[category:mysql]]</div>

Nighthawk