nazly.me

Weblog of Nazly Ahmed

Nazly Ahmed

Nazly Ahmed

Web Developer. PHP Addict. Wordpress Hacker. FOSS Enthusiast. (Micro)Blogger. Photo Hobbyist. Cricket Fanatic. Husband. Dad.

If you are running a WordPress Website or a Blog where the content is updated and/or deleted frequently, you will need to optimize the MySQL tables more often than not so that you don’t run into database issues and down times. This also helps to maintain the average response time of MySQL queries.

When executed, the following PHP script will optimize the tables which requires optimization in your WordPress database. Place this file in your WordPress’s root directory.

You can run this manually or the ideal scenario would be to setup a cron job to execute it in a given time interval based on your requirement..

<?php            
include("wp-config.php");
mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
mysql_select_db(DB_NAME);
$selQuery = "SHOW TABLE STATUS FROM `".DB_NAME."`";
$resSel = mysql_query($selQuery) or die(mysql_error());
while($row = mysql_fetch_assoc($resSel)){
    if($row["Data_free"] > 0){    
        echo date("Y-m-d H:i:s")." - Optimized : "
                .$row["Name"]." ( ".$row["Data_free"]." )\n";
        $optQuery = "OPTIMIZE TABLE `".$row["Name"]."`";
        mysql_query($optQuery) or die(mysql_error());
    }
}
echo "---------------\n\n";
?>
Posted on 4th February 2011 14:44:31