How can I delete orphan keys in Wordpress database tables?

Question

In particular in table wp_options. After almost 2 years of blog production it seems increased a lot, and I don't know how many crap is in there.

Do you know a plugin that works with Wordpress 3.0 or safe query to run to search orphan keys/rows?

Answer 1

There is no query that will be 100% certain to delete everything unused and not delete those things because any theme or plugin can add options to the wp_options table. Still, with a little effort you can get a pretty good idea of what's not in use and then manually decide which of those things to delete and which not to.

You can put the following code temporarily into your theme's functions.php file and then visit every (type of) page on your public-facing site and more importantly all the admin pages in the admin console. Once you've done that you can open your wp_options table and look at the field use_count (added by the code below) to see which options have a use_count equals to zero (the use count is mostly meaningless other than anything greater than 1 has been read or updated at least once since you added this code.)

global $wpdb;
header('Content-Type:text/plain');
$results = $wpdb->get_results("SHOW COLUMNS FROM wp_options WHERE Field='use_count'");
if (count($results)==0) {
    $wpdb->query("ALTER TABLE {$wpdb->options} ADD COLUMN use_count int UNSIGNED NOT NULL DEFAULT '0' AFTER autoload");
}

add_action('all','monitor_get_option_usage');
function monitor_get_option_usage($filter){
    if (preg_match('#^option_(.*)$#',$filter)) {
        increment_option_use_count(substr($filter,7));
    }
}
add_action('updated_option','monitor_update_option_usage');
function monitor_update_option_usage($option){
    increment_option_use_count($option);
}
function increment_option_use_count($option) {
    global $wpdb;
    $wpdb->query("UPDATE {$wpdb->options} SET use_count = use_count + 1 WHERE option_name = '$option'");
}

With this you'll probably be able to identify options that are associated with long gone plugins, former themes and even options of your own you added early on but no longer use. Export them all to a backup (just in case) and then delete the ones you are comfortable deleting. Once you are done you can remove the use_count field (if you want to, doesn't hurt for it to be there) and also remove the code above from your functions.php file too.

Although this is still not perfect it's much better than nothing. Hope it helps?

Answer 2

The plugin Clean Options has worked well for me. The author's description of the plugin seems to fit what you need: "Finds orphaned options and allows for their removal from the wp_options table."

I haven't tried WP-Optimize yet personally, but that one looks promising too. And it says it supports WP 2.7 (whereas Clean Options only mentions definite support for WP 2.3), nice!

Answer 3

This won't necessarily remove issues with wp_options but I've used WP-Optimize to fix a lot of database sizing issues on my 3.0 sites. It clears out unneeded post revisions, spam comments, and can automatically fix a lot of issues. On my main blog, the DB was reduced from 30MB to just under 6MB and runs much more smoothly now.

Answer 4

I run both Clean Options and WP_Optimize on my site, and the combo does a terrific job of keeping the database in great shape.