Optimize MySQL tables simple

Want to speed up your MySQL performance in a simple but effective way?

Speed in MySQL is all about how fast the database can find the information you request. To help MySQL clean up and get all the best possibilities to gather your information, you can perform the command “Optimize” on your tables.

Because you probably will add and delete tables from time to time, you want a solution that works no matter how your database looks like. For this I will give you a PHP script that finds all your tables, and then perform Optimize on every single one. A good idea can be to do this every night (or whenever your server is least accessed) with “cron” because you don’t want to delay your surfers to much.

Here is the script (really simple actually):
Make sure you modify the four variables at the top of the script so they match your configuration.

Good luck with your optimizing :-)

// Change these four variables to match your configuration.
// You will probably only need to change “user”, “pwd” and “dbName”.
$server = ”localhost”;
$user = ”mysql_user”;
$pwd = ”mysql_password”;
$dbName = ”mysql_dbName”;

$link = mysql_connect($server, $user, $pwd);
if (!$link) {
die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db($dbName, $link);
if (!$db_selected) {
die ('Can\'t use $dbName : ' . mysql_error());
}

// Find the names of all tables in the selected db.
$alltables = mysql_query("SHOW TABLES");

// Go through all tables.
while ($table = mysql_fetch_assoc($alltables))
{
foreach ($table as $db => $tablename)
{
// Perform the magic “OPTIMIZE” on every table in the db.
mysql_query("OPTIMIZE TABLE '".$tablename."'")
or die(mysql_error());
}

}

mysql_close($link);
?>