Breaking News
Home / Sys Admin / Mysql / How to compute Mysql Schema size using Php?

How to compute Mysql Schema size using Php?

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 script uses that above logic to compute the overall size of the database by computing the size of its individual tables. This can be used to understand why and where the database size is growing.

<!>

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

Check Also

How to find out MySQL database hostname in Godaddy Shared Hosting?

Locating Your Godady Database’s Host Do not use “localhost” as the hostname for your database-driven …

Advertisment ad adsense adlogger