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

Like it.? Share it:
Tags:

Comments are closed.