In this post I discussed an example where wp_options table had 900k rows of plugin-based data in it, causing the wp-admin dashboard to load slowly. As noted there, good coding practice is for each plugin to create its own database tables and use those instead of default WP tables.
While the WordPress community actively improves wp_options table and indexing, for large WordPress sites there are a few things you should consider that will impact your site’s performance regarding the wp_options table size:
Store CSS inside the Child theme
WordPress has a nice option to add custom CSS inside the Customizer
Customize > Additional CSS allows you to add custom CSS to WordPress, however, the code added here is theme-specific, so if you switch themes, you’ll lose your CSS. Another reason why you should not add custom CSS code this way is because this data is stored inside the wp_options table.
Instead, I suggest creating a Child theme and adding the code to a custom style.css file.
Use Custom Post types for large Widget content
Widgets are also stored in wp_options table so be careful not to store too much data in those HTML or Text Widgets. Instead, use a custom post type for that kind of data.
Use as less WordPress plugins as possible
Plugins are great, I use more than 30 plugins on this site, but they can easily cause the wp_options database table to grow a few GBs in size.
The autoload parameter is set to “yes” by default for plugin developers, and although every plugin should ideally load its data on every page, that is not the case. Because wp_options table wasn’t designed to handle thousands of rows, WordPress sites may experience issues if the wp_options table has a lot of autoloaded data.
To check the size of autoloaded data inside the wp_options table, run:
SELECT SUM(LENGTH(option_value)) as autoload_size FROM wp_options WHERE autoload='yes';
Size is displayed in KB – if the autoload size is more than 2MB, you should optimize the database.
Additionally, some plugins don’t clear up their autoloaded options when they are deactivated or removed from WordPress, and some plugins don’t actually need all of their settings to be autoloaded.
Here is a quick guide on How to remove inactive plugin data from the wp_options table.
Use Object Cache for WordPress
As mentioned earlier, by default WordPress will fetch options from the database on each page load, but by using an object caching (e.g. Memcached, Redis or APCu), WordPress will cache wp_options values with autoload set to “yes” as an array called “alloptions”.
Here is a great guide from LiteSpeed on setting Object Cache in WordPress: https://blog.litespeedtech.com/2018/02/07/object-cache-support-in-lscache/
Only store the bare minimum amount of data in wp_options
