MySQL Table Size Ranker

Out of curiosity today I wanted to see how much database space different production applications I wrote are using. So, I whipped up the table size ranker script below to find out. Since I have over 100 databases and 3400 tables on a single server, I decided a csv output would be best.

Here's the script: (download MySQL Table Size Ranker)

<?php
# MySQL Table Size Ranker
# Usage: php tablesize.php > tablesizes.csv
# https://adamyoung.net

mysql_connect('localhost','root','');
$result = mysql_query('SHOW DATABASES');
$databases = array();
while ($row = mysql_fetch_array($result)) {
        $databases[] = $row['Database']; 
}

$totals = array();
foreach ($databases as $database) {
        $result = mysql_query("SHOW TABLE STATUS FROM {$database}");
        if (!$result) continue;
        while ($row = mysql_fetch_array($result)) {
                $totals[] = array('database' => $database, 'table' => $row['Name'], 'Data_Length' => $row['Data_length'], 
                                'Index_Length' =>  $row['Index_length'], 'Total' => ($row['Data_length'] + $row['Index_length'])); 
        }
}
usort($totals, 'sort_total_desc');

echo "Database,Table,Data Length,Index Length,Total\n";
foreach ($totals as $total) {
        echo "{$total['database']},{$total['table']},{$total['Data_Length']},{$total['Index_Length']},{$total['Total']}\n";
}

function sort_total_desc($a, $b) {
        if ($a['Total'] == $b['Total']) return 0;
        return ($a['Total'] > $b['Total']) ? -1 : 1;
}

What I found out:
To my surprise, none of my applications were using as much as a popular helpdesk app. Keeping Kayako's mail parser log enabled is a BAD idea. The swparserlogdata and swparserlogs tables were HUGE. Around 14GB worth of crap. See ya worthless data:
TRUNCATE TABLE swparserlogs;
TRUNCATE TABLE swparserlogdata;

Comments

Recently I tried to install this soft. First of all I should say that it took me not so much time as I expected, but I failed to do it to the end because the light was switched off! In addition I'd like to say that the author of this post is so beautifully tells us about installing this soft, that I needn't any help and coped with it by myself.So when I finish installing it I hope it'll really lighten the work of my computer.I saw something similar on <a href=http://bytesland.com> Bytesland </a> search engine and thought that was good too.I bet it will become very popular in the near future.

I had never thought about seeing how much space my applications were using. I would have thought that the apps would have used more thank a popular helpdesk app. I am going to have to use your method to see how much my applications are using. I wonder how much database space a typical <a href="http://www.mutualmobile.com">ipad app development</a> uses? Great idea!