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
# http://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;