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;
}
?>
Related Articles