I have a wordpress site with some serious wp_options table bloating. This is causing slow page loads and memory bloat. After investigation, there appears to be thousands of orphaned entries.
If you know for a fact these options are for plugins that are no longer in use, then yes it’s safe to delete them. If that is the case, delete them and stop reading here
If you aren’t sure that these options are for plugins no longer active, then you might need to be wary, and will need to manually check.
Additionlly, you can have a truly massive options table that doesn’t cause these issues, the culprit here is autoloading options.
In order to avoid lots of little options table queries, there’s a column in the options table to autoload options. WP will then load all the options with this flag at once. It appears your site has a lot of these.
So you can turn this flag off via SQL, but, you don’t want to do this for all options, afterall it was done for a reason. However, the worst case scenario is reduced performance from having to making lots of little queries to fetch individual options. It can always be undone if you take a backup
Is the process of deleting these rows and then optimizing the table afterwards safe to do?
If you know these are for a theme that’s been changed, or a plugin that’s no longer installed/active, then yes, it’s safe. I don’t see how the deleted code could come back from the grave to wreak havoc.
Otherwise, you’ll have to manually check.
Are There Options That Are Always Safe To Delete?
Transients!! By deleting them WP will have to regenerate and recreate the ones it’s using, but, if your options table is full of transients, deleting them rarely creates issues. When it does create issues it’s usually due to a very poorly built plugin.
Similarly, plugins may choose to cache things in options. Your mileage may vary, and some breakage may occur, so take a backup, otherwise it’s likely just a performance hit
General Options Table Bloat Debugging
The problem with options table bloat is that people call it options table bloat and expect a general solution, but it’s really just a sign of a deeper more specific set of problems. Similar to how a HTTP 500 error is just an indicator something went wrong, you don’t fix 500 errors, you fix PHP Fatals, syntax errors, server configs etc.
So, you need to do the following:
- Identify what it is that’s bloated the options table, actually look at the table, if it’s full of stuff, what is it full of?
- Consider, of those options being autoloaded, do they really need to be autoloaded? Which code is responsible for it?
- When you identify options that are very large, or when there are 10k tiny options for the same thing, empty options etc, is this code you can change? Or are you going to need to ask the plugin authors?
- Be wary of options cleaner plugins, afterall how do they know which options are to be cleaned and which are needed? Other than transients there’s not much that can be figured out without manual checking
- Use WP CLI’s
wp option listsubcommand to test options and see which ones are the biggest, there names, etc https://developer.wordpress.org/cli/commands/option/list/
As for optimising the table, you’d have to be more specific about what you mean by that. However, MySQL table optimisation has little effect on WP performance unless you’ve made major changes. The best database related improvements are related to the queries themselves and the quantity and type of information loaded. In this case there’s just a lot of stuff being autoloaded.
Widgets
These are an edge case, since widgets are stored in options. If you have a lot of sidebars registered, and i mean a lot ( enough for several hundred or thousand widgets ), or widgets that store a lot of stuff in the database but are put in the inactive area ( e.g. an entire book, files, not the normal kind of stuff ), then widgets might cause a problem.
Usually widgets are a problem because object caches store an all options field to make options loading super fast, and some object caches cap out at 1MB for each field. Lots of widgets usually bumps into that barrier.
However the chances that this is your problem are very unlikely, and testable using WP CLI and SQL