Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

up vote 5 down vote accepted

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?

share|improve this answer
    
I'd strongly recommend memcached/incr() for this instead of potentially massive amounts of db writes. –  Denis de Bernardy Oct 22 '10 at 18:24
2  
@Denis - Since memcached requires advanced server setup which isn't even possible on a shared server where most WordPress sites are hosted, and since this is an one-time or occasional diagnostic routine I don't see why you'd emphasize the need for memcached for this use-case? Maybe I'm missing something, can you detail why someone who'd have to move to a VPS or dedicated server and get learn how to or get someone to configure memcached should do so just to avoid a large number of database hits for a rarely used maintenance routine? –  MikeSchinkel Oct 22 '10 at 19:56

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.

share|improve this answer

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!

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.