Optimize MySQL tables

If you have a database driven site and you want to optimize MySQL tables then this small PHP script is perfect. It goes through all the tables in all MySQL databases the user has access to and does table optimization on each one using the MySQL Optimize Table syntax.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

#!/usr/bin/php
<?php
$server = "localhost";
$user = "mysql-user";
$pwd = "password";
 
$link = mysql_connect($server, $user, $pwd);
if (!$link) {
  die('Could not connect: ' . mysql_error());
}
 
$q= mysql_query("SHOW DATABASES") or die(mysql_error());
 while ($dbName = mysql_fetch_array($q)) {
   if ($dbName[0] != "information_schema") {
     echo " + Selecting " . $dbName[0] . "\n";
     $db_selected = mysql_select_db($dbName[0], $link);
        if (!$db_selected) {
        die ('Can\'t use $dbName[0] : ' . mysql_error());
     }
 
     $alltables = mysql_query("SHOW TABLES") or die(mysql_error());
 
     while ($tableName = mysql_fetch_array($alltables)) {
        echo "   - Optimizing " . $tableName[0] . "\n";
        mysql_query("OPTIMIZE TABLE `".$tableName[0]."`") or die(mysql_error());
     }
   } else {
      echo " + Skipping " . $dbName[0] . "\n";
   }
}
 
mysql_close($link);
 
?>

You can set a daily cronjob to launch this script automatically. Note that the databases are locked while being optimized!

Remember to set restricitve read permissions to this script (chmod 700 mysql-opt.php) as it contains sensible data!