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!

Author: Zack Katz

Zack Katz is the President of Katz Web Services and the developer of WordPress plugins with over 700,000 downloads. He lives in Southwest Colorado with his wife and two cats.

6 thoughts on “Auto-Optimize WordPress Database without a Plugin”

  1. Buddy, the code above isn’t complete, what happened?

    it says:
    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
    add_action('init', 'kws_optimize_tables');

    Isn’t there something missing after bold part? 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *