Clean up wp_options table (autoloaded data)

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 webhosting control panel (cPanel, Plesk, etc) and follow the commands below!

NOTES:

• Backup your database before trying any of these optimizations.
• If your database has a prefix (e.g. “wp_123abc_” instead of only “wp_”), then retype SQL commands below using the prefix “wp_123abc_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 30;

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 change the DESC LIMIT 30 to a higher number like 50 or 60 if you want to see more items. Usually the first 10-30 items make up the bulk of your autoloaded data anyway. It’s usually only a few plugins that are creating all the bulk anyway.

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 “Full Velocity Minify” plugin might be listed with the string “fvm” in the database.