Compute size of Mysql database with it's tables

Posted February 5th, 2008 by administrator

<< Recursively Convert Gif Images To Jpg | Find the Calling Class >>

Basically a database is a collection of tables.
So the size of the database is sum total of size of its individual tables.

Size of the table = Size of its Data + Size of its Indexes.
Size of database = Sum of {Individual Table Size}

The following snippet uses that above logic to compute the overall size of the database by computing the size of its individual tables.

<?php
// Script to compute total size of a mysql database.
$host = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

// connect to the mysql server
mysql_connect($host,$username,$password);

// select the database
mysql_select_db($dbname);

// execute the query
$result = mysql_query("show table status");

$db_data_size = 0;
$db_index_size = 0;
$out = "";

// loop through the resultset (each table)
while($row = mysql_fetch_array($result)) {
$table_data_size = $row["Data_length"];
$table_index_size = $row["Index_length"];

// table size
$table_size = $table_data_size + $table_index_size;

// increment the over all size
$db_data_size += $table_data_size;
$db_index_size += $table_index_size;

$table_stats = $row["Name"];
$table_stats = $table_stats .": Data(" .round(($table_data_size/1024)/1024, 2) ."MB)";
$table_stats = $table_stats .": Index(" .round(($table_index_size/1024)/1024, 2) ."MB)";
$table_stats = $table_stats .": Total(" .round(($table_size/1024)/1024, 2) ."MB)";
$out = $out . $table_stats . "\n";
}

// convert to MB and round it of
$db_data_size_mb = round(($db_data_size/1024)/1024, 2);
$db_index_size_mb = round(($db_index_size/1024)/1024, 2);
$db_size_mb = $db_data_size_mb + $db_index_size_mb;

$out = $out ."DB Size : Data(". $db_data_size_mb ."MB)";
$out = $out .": Index(". $db_index_size_mb ."MB)";
$out = $out .": Total(". $db_size_mb ."MB)\n";
echo $out;
?>