Quick script to sort mysql database/tables based on table size

Mysql can show the table status with the row and size for each table but doesn’t do sorting. We can however store each row in an array and sort the array.

I am too lazy to write a script for it. 2 x for loops does the trick. This script is from adamyoung.net. Just run the script in command line. Dump it into csv and you can do wonders in excel.

<?php
# MySQL Table Size Ranker
# Usage: php tablesize.php > tablesizes.csv
# http://adamyoung.net
# Simple modification from http://azhowto.com

mysql_connect(‘localhost’,’root’,’your_pass’);
$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’], ‘records’ => $row[‘Rows’],
‘indexes’ =>  $row[‘Index_length’], ‘data’ => ($row[‘Data_length’] + $row[‘Index_length’]));
}
}
usort($totals, ‘sort_total_desc’);

echo “Database,Table,Records,Indexes,Data\n”;
foreach ($totals as $total) {
echo “{$total[‘database’]},{$total[‘table’]},{$total[‘records’]},{$total[‘indexes’]},{$total[‘data’]}\n”;
}

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

Author: bpeh

Bernard Peh is a great passioner of web technologies and one of the co-founder of Sitecritic.net Website Design and Reviews. He works with experienced web designers and developers everyday, developing and designing commercial websites. He specialises mainly in SEO and PHP programming.