How to optimize all databases and tabels on mysql server?

Optimization of database tables in mysql is a process which has to be carried out regularly to make your database in good health.

According to mysql manual,

OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. After extensive changes to a table, this statement may also improve performance of statements that use the table, sometimes significantly.

In phpmyadmin you can see data similar to following screen if your database tables are not optimized. Entries in Overhead column indicates size of data space which can be reclaimed after optimization.

A major challenge comes in optimization when you have many databasesm thus many tables on my mysql server. It is hard to do optimization manually for each and every table. So we have created this script which can optimize each and every table of each database on your mysql server. You can run this script as your cron script also so you can do your optimization activities automatically. We have also executed repair statement which will repair any table error.


$con=mysql_connect(DATABASE_HOST,DATABASE_USER,DATABASE_PWD);
$db_list = mysql_list_dbs($con); $dbtoProcess=array();
while ($row = mysql_fetch_object($db_list))
{
$dbtoProcess[]=$row->Database;
}

foreach ($dbtoProcess as $dbname) {
$db=mysql_select_db($dbname);
$sql = "SHOW TABLES FROM `$dbname`";
$result = mysql_query($sql);

$AllTablesinDB=array();
while ($row = mysql_fetch_row($result)) {
$AllTablesinDB=array_merge($AllTablesinDB,$row);
}

foreach($AllTablesinDB as $value)
{
mysql_query("optimize table $value");
mysql_query("repair table $value");
//print $value ." has been optimized and repaired";

}
}

Execution of this script may take some time depending on the size of databases and tables.