How to Clean up Your wp_options Table and Autoloaded Data

Today we’re going to take a look at the wp_options table in your WordPress database. This is one area that often gets overlooked when it comes to overall WordPress and database performance. Especially on older and large sites, this can be the culprit for slow query times on your site due to autoloaded data that is left behind from third-party plugins and themes. Check out these tips below on how to check, troubleshoot, and clean up your wp_options table.

My favorite commands for cleaning up autoloaded data from your wp_options table.

Why should you remove autoloaded data? It’s because this type of data is loaded on every page load and often contains data that is no longer used (left behind by already deleted themes/plugins) or left behind because WP-cron wasn’t working and some plugins didn’t clean up after themselves.

Get into your PHPMyAdmin tool from the Webhosting control panel (cPanel, Plesk, etc) and follow the commands below!

How much difference can autoloads make?

HUGE! Freaken huge! I’ve seen awful bloated sites with many MB of autoloaded data. Cleared it all and the whole site felt so much lighter, both on frontend and back end. Keep in mind y’all, the backend can’t be cached. Cleaning autoloads definitely has a measurable impact on massively bloated sites and is one of the advanced tasks that separate pros from non-pros.

NOTES:

• Backup your database before trying any of these optimizations.
• If your database has a prefix (e.g. “wp_abcd_” instead of only “wp_”), then retype SQL commands below using the prefix “wp_abcd_options” instead of “wp_option”.

1. Check autoloaded data size

SELECT SUM(LENGTH(option_value)) as autoload_size FROM wp_options WHERE autoload='yes';

This one shows you how big the autoloaded table is. Anything above 1MB really badly needs to be cleaned up; I’ve seen sites with even 40MB (no wonder they crashed!). I try to stay below 500kb if possible (although even 1MB is considered OK). If you have 500kb or less, you can stop here!

2. List top autoloaded data entries

SELECT option_name, length(option_value) AS option_value_length FROM wp_options WHERE autoload='yes' ORDER BY option_value_length DESC LIMIT 200;

This will list the top 30 autoloaded data entries in the table. Delete the ones you know aren’t being used anymore. You can also increase the DESC LIMIT 200 to a higher number like 300 or 500 if you want to see more items. Usually, the first 10-50 items make up the bulk of your autoloaded data anyway. And it’s usually only a few plugins that are creating most of the bloat. (Although some really old sites may have tons of stuff left over from deleted themes and plugins.)

3. Find specific autoloaded data

SELECT * 
FROM `wp_options` 
WHERE `autoload` = 'yes'
AND `option_name` LIKE '%jetpack%'

This command is useful for targeting specific plugins that you KNOW for certain you aren’t using any longer. This is great for cleaning up remnants left from old themes and plugins. Simply replace the string “jetpack” with anything else you like. You’ll also notice that many plugins don’t use their full name. For example, items related to the “Full Velocity Minify” plugin might be listed with the string “fvm” in the database.

4. Tracking down mystery autoloads

Did you see some giant autoloads but you’re not sure whether or not you can delete them? Don’t worry, I have a few handy tricks up my sleeve:

• Click on edit and look at the data inside. Sometimes they give you a clue about what it’s used for.
• Search the option name in Google in quotations. It might also help if you type the word “WordPress” or “plugin” or “theme” before it.
• You can also try using step #3 above, but search only the first prefix of the option name. For example, if the full name is “wds_service_results” then you can do step #3 but replace “jetpack” with “wds_”. Sometimes, you’ll find the other option names with more helpful data to track down which plugin it is.
• Last but not least, you can simply change the autoload value to “no”. (Then change it back if anything breaks, or delete it after a month if all is well.)

WP Lovers personal autoload removal list

A list of the biggest autoload offenders that I often run into. If you see any autoloads you aren’t familiar with. Google around to see what they might be related to. Perhaps an old theme or plugin you haven’t used in a while. (Obviously, you should not delete any autoloads for active plugins!)

Plugins with high autoloads

• BackupBuddy
• Mobi by Phpbits
• Revolution Slider (of course!)
• Thrive Architect/Leads
• cherry_customiser_fonts_google – probably came from some Google fonts plugin
• transients – some people don’t realize they have 40MB of transients sitting there! (YIKES!)
• SchemaPro
• BeRocket
• Jetpack
• WPMU DEV (and their many plugins)
• Pegasus Accelerator WP
• Redirect plugins
• Redux framework (any theme using this)
• Security Ninja

There are hundreds more plugins with awful autoload…find them all! (Feel free to report in the comments and I’ll add them here.)

Cron Jobs

Another frequent option that we see with a high amount of autoloaded data is cron. For this, it could be anything cron related. So what you can do is hit the “edit” button to see what’s causing it. Here’s an example below in which it was apparent that “do_pings” was causing the issue. Again, a quick Google search revealed a quick fix to clean up do_pings.