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!

Related posts:

1. How to tell if your WordPress Plugin or Widget is Activated in WordPress
2. Get Adjacent Images – More WordPress Functions
3. Mad Mimi Plugin for WordPress
4. WordPress Digg Shortcode Function – As Seen on WordPress.com
5. WordPress Debt Calculator

Tagged as: Database, Functions.php, load time, Optimization, Speed, WordPress Development, WordPress Functions, WP-DBManager