TechniTip.Net

MySQL Optimize Script

A nice small PHP script which simple connects to a given MySQL server and optimizes all databases and included tables. Only the internal table “information_schema” is skipped because it will show an error.

Working with MySQL version 5.0.32 and PHP version 5.2.0 on Debian Etch.

Script is designed to run from command line “php optimize.php” maybe from a cron job. Be careful: avoid running this script during your server “rush hour”, it may slow down your server.

<?php
/***********************************************************
 optimimze.php - optimizes all databases and tables of the
                 given mysql host.
 2008 - technitip.net
 ***********************************************************/
$mysqlhost = "localhost"; // enter MySQL host
$mysqluser = "user";      // enter MySQL user
$mysqlpwd  = "password";  // enter password
###########################################
$connection = mysql_connect($mysqlhost, $mysqluser, $mysqlpwd);
if (mysql_error())
{
  echo "Could not connect to database server! " . mysql_error() . "\n";
  exit;
}
$db_list = mysql_list_dbs();
$i = 0;
$cnt = mysql_num_rows($db_list);
while ($i < $cnt)
{
  $db = mysql_db_name($db_list, $i);
  ###########################################
  mysql_select_db($db, $connection);
  $result = mysql_list_tables($db);
  while ($row = mysql_fetch_row($result))
  {
    if ( $db == "information_schema" )
      continue;
    echo $db . " : `" . $row[0] . "`";
    $sql = "OPTIMIZE TABLE `".$row[0]."`";
    $erg = mysql_query($sql, $connection) or die(mysql_error());
    $data= mysql_fetch_array($erg, MYSQL_ASSOC);
    if($data)
    {
      echo " - " . $data['Msg_text'] . "\n";
    }
  }
  ###########################################
  $i++;
}
?>