How can I delete orphan keys in WordPress database tables?

by

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?

Leave a Comment