How to fix wp_options when Scalability Pro gives me the Performance Warning of too many options

Firstly, you need to find out where the extra wp_options are coming from. Run this SQL command:

select left(option_name,15) option_name, count( * ) total
from wp_options where autoload = 'yes'
group by left(option_name,15)
order by total desc;

To run SQL you can use the SQL Executioner plugin.

Once you’ve run the above, it’ll give you an indication of the source of the wp_options that are set to autoload. For example, you might find that the top 20 entries are all from a particular plugin. Each plugin will tend to prefix the option_name with the name of their plugin. Then you know where you can delete them or switch them to autoload = ‘no’.

Now you want to either delete these excessive options or stop them autoloading.

To delete them, run:

delete from wp_options where autoload = 'yes' and option_name like 'transient%';

You’ll need to replace ‘transient%’ with whatever the beginning of the autloaded options is. You might need to run multiple commands to eliminate swathes of poorly set options.

Or you can just switch autoload off by using:

update wp_options set autoload = 'wpi' where option_name like 'transient%' and autoload = 'yes';

Note: You can set the autoload value to anything other than ‘yes’ and they will not be autoloaded, but still be available if a plugin really does need them. You can use ‘no’ but if you use ‘wpi’ or some other value it lets you revert your changes if you need to.

This second option (updating instead of deleting) is technically safer, because the options will remain in your database and be available for the relevant plugin, they just won’t be autoloaded on every page.

To revert changes back:

update wp_options set autoload = 'yes' where autoload = 'wpi';

So – basically, run the SQL command at the top to identify which option_names are incorrectly set – you need to use some thinking power to figure out if they’re needed or not. Often you should know based on the plugin name whether it needs options loaded on every single page or if it is only used occasionally.

Then update the autoload value to anything other than ‘yes’ and they will no longer be autoloaded.

If you have over 1,000 options set to autoload, this typically adds 0.5 seconds to your page load (TTFB). So, if you have 5000 or more then you will have a really slow site on every page. Fixing this fixes every page.