snippetsmania.com

MySQL table optimize table cron job

<?php
/**
 * Runs the query "OPTIMIZE TABLE" on each database and table
 * 
 * @author Marc Steinert <marc@bithub.net>
 * @version $Id$
 * @link http://bithub.net/
 */
 
// MySQL Data
define('SQL_HOST', '127.0.0.1');
define('SQL_USER', 'root');
define('SQL_PASS', 'xxxxxx');
 
 
$link = mysql_connect(SQL_HOST, SQL_USER, SQL_PASS);
$allDbsResult = mysql_query("SHOW DATABASES", $link);
 
while(($databaseRow = mysql_fetch_assoc($allDbsResult))) {
	// Select the found database
	mysql_select_db($databaseRow['Database'], $link);
 
	$alltablesResult = mysql_query("SHOW TABLES", $link);
 
	while (($table = mysql_fetch_assoc($alltablesResult))) {
		foreach ($table as $db => $tablename) {
 
			echo "OPTIMIZE TABLE '".$tablename."'";
 
			if (mysql_query("OPTIMIZE TABLE ".$tablename.""))
			{
				echo " - done'n";
			}
			else
			{
				echo " - failed'n";
			}
	   }
	}
}
die;
 

This script connects to a MySQL server and runs the command "optimize table" on each database and table on the MySQL server .

Tags