Auto-Optimize WordPress Database without a Plugin

These horses are somehow not cool. Speeding up your blog is.

I am working on a WordPress project that has a pretty heavy database, and I want to be able to auto-optimize the WordPress database. Even though they are integrating this functionality into WordPress 3.0, I want it now, and without having to use a plugin (I have had some issues with WP-DBManager configuring properly on a few sites).

If you add the following code to your functions.php file, it will automatically optimize your WordPress database every 6 hours, keeping it squeaky clean.

Add to your theme’s functions.php

function kws_optimize_tables() {
	$debug = false;

	// Check to see if the lastOptimized option has been set (it's just a timestamp)
	$lastOptimized = get_option('lastOptimized');

	// If it's been longer than 1 week since it was last optimized, do it up!
	if(!$lastOptimized || $lastOptimized < strtotime("-1 week", time())) { 

		// Update the lastOptimized option to the current time
		update_option('lastOptimized', time());

		// Do some MySQLing to optimize each table in the WordPress DB
		$query = 'SHOW TABLES FROM '. DB_NAME;
		$result = mysql_query($query);
		if (!$result && $debug) {
			print('Invalid query: ' . mysql_error());
		}
		$num_rows = mysql_num_rows($result);
		if ($num_rows){
			 while ($row = mysql_fetch_row($result)){
		         $query = 'OPTIMIZE TABLE '.$row[0];
				 $result = mysql_query($query);
				 if (!$result && $debug) {
				    print('Invalid query: ' . mysql_error());
				}
			}
		}
	}
}
add_action('init', 'kws_optimize_tables');

Just wanted to share some quick code. If you want to change the frequency of the optimization, change “1 week” to whatever PHP strtotime() variable you want. Make sure to keep the minus sign in there though!