If you experience WordPress backend panel (wp-admin) running very slow or witnessing slow query times, then it is advised to clean up the wp_options Table and Autoloaded Data.
The wp_options table contains all sorts of data for your WordPress site such as –
- Site URL, home URL, admin email, posts per page, time format, default category etc
- Settings for themes, plugins, widgets
- Temporarily cached data
Autoloaded data is an important thing to understand about the wp_options table that is loaded on every page of your WordPress site. The autoloaded data may contain large amount of data in the wp_options table such as –
- Autoloaded data by a plugin
- autoloaded data that is left behind from third-party plugins and themes which have been removed from the WordPress site.
- Plugin and theme developers are flooding data into the wp_options table instead of utilizing their own tables.
- Autoload data limit
- Ideal autoload data limit = 300 KB to 1MB.
- optimized or removal required = 3-5 MB
- Addressed right away = <10 MB
Check, troubleshoot, and clean up
Check out these tips below on how to check, troubleshoot, and clean up your wp_options table
1.The wp_options table has no index on autoload, so the query ends up doing a full table scan.Adding an index might solve the problem.
First, do this query to see what the distribution looks like: SELECT COUNT(*), autoload FROM wp_options GROUP BY autoload;
If a large majority of them are set to 'no', you can solve the problem for now by adding an index on autoload. ALTER TABLE wp_options ADD INDEX (`autoload`);
2.Transient options inserted into the table but hadn’t been automatically deleted.
Query the wp_options table for option names that contained "transient" SELECT * FROM wp_options WHERE option_name LIKE '%transient%';
Check the fields that contain massive amounts of data in the option_value field. The solution is to delete all the “transient” rows which will not hurt the server since “transient” rows will automatically repopulate (if they are supposed to be there).
Delete the rows DELETE from wp_options where option_name like '%transient%';