Optimize all your mysql databases automatically (See related posts)

// description of your code here
Use this script preferably with a cronjob to automatically optimize all the tables in your mysql databases. Under root privilleges the script will search for all the databases in your server and will optimize them all. Hope you will like it as it took me more than 3 hours to write it

   1  
   2  <?php
   3  echo '<pre>' . "\n\n";
   4  set_time_limit( 100 );
   5  
   6  $time = microtime();
   7  $time = explode(' ', $time);
   8  $time = $time[1] + $time[0];
   9  $start = $time;
  10  
  11  //Connection variables :
  12  $h = 'localhost';
  13  $u = 'root';
  14  $p = 'password';
  15  
  16  $dummy_db = 'mysql';//The php->mysql API needs to connect to a database even when executing scripts like this. If you got an error from this(permissions), just replace this with the name of your database
  17  
  18  $db_link = mysql_connect($h,$u,$p);
  19  
  20  $res = mysql_db_query($dummy_db, 'SHOW DATABASES', $db_link) or die('Could not connect: ' . mysql_error());
  21  echo 'Found '. mysql_num_rows( $res ) . ' databases' . "\n";
  22  $dbs = array();
  23  while ( $rec = mysql_fetch_array($res) )
  24  {
  25  $dbs [] = $rec [0];
  26  }
  27  
  28  foreach ( $dbs as $db_name )
  29  {
  30  echo "Database : $db_name \n\n";
  31  $res = mysql_db_query($dummy_db, "SHOW TABLE STATUS FROM `" . $db_name . "`", $db_link) or die('Query : ' . mysql_error());
  32  $to_optimize = array();
  33  while ( $rec = mysql_fetch_array($res) )
  34  {
  35  if ( $rec['Data_free'] > 0 )
  36  {
  37  $to_optimize [] = $rec['Name'];
  38  echo $rec['Name'] . ' needs optimization' . "\n";
  39  }
  40  }
  41  if ( count ( $to_optimize ) > 0 )
  42  {
  43  foreach ( $to_optimize as $tbl )
  44  {
  45  mysql_db_query($db_name, "OPTIMIZE TABLE `" . $tbl ."`", $db_link );
  46  }
  47  }
  48  }
  49  
  50  $time = microtime();
  51  $time = explode(' ', $time);
  52  $time = $time[1] + $time[0];
  53  $finish = $time;
  54  $total_time = round(($finish - $start), 6);
  55  echo 'Parsed in ' . $total_time . ' secs' . "\n\n";
  56  ?>