Auto Optimize Your MySQL Tables Script

To make MySQL driven websites running fast, I’ve pieced together a script and cron job that will save you some support calls.

Step 1: Create a PHP script:

<?php
// Change vars as needed here
$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 all tables in the selected DB
$alltables = mysql_query("SHOW TABLES");

// Process all tables.
while ($table = mysql_fetch_assoc($alltables))
{
   foreach ($table as $db => $tablename)
   {
         // Optimize them!
         mysql_query("OPTIMIZE TABLE '".$tablename."'") or die(mysql_error());
   } // end foreach
} // end while

mysql_close($link);
?>

Step 2: Add this script into your daily cron jobs
Login as root and follow these steps to add your new website optimization script to your daily cron directory, thus never having to worry about manually optimizating again!

cd /etc/cron.daily
echo '#!/bin/sh' > mysql_optimize; echo '/path/to/your/script.php' >> mysql_optimize; chmod 755 mysql_optimize;

Now your all set! A quick and easy way to keep your high volume MySQL driven websites optimized!
It is most effective on tables that get updated a lot (with deletions and inserts).



Leave a Reply

You must be logged in to post a comment.