The options table in the WordPress database often becomes a performance bottleneck, particularly on older and possibly more complex sites. In this article, I show you some tips to help you take control of the wp_options table .
The wp_options table is an area that is often overlooked when it comes to overall WordPress performance. Especially on larger sites this table can cause slow loading times due to autoloading data left by third party plugins and themes.
What is the wp_options table for?
The wp_options table contains several types of data such as:
- Site URL
- Homepage URL
- administrator email
- default category
- number of posts per page
- date format
- settings for plugins, themes, widgets, …
- data temporarily cached
- etc, …
The table contains the following fields (columns), one of which is of particular interest to us when it comes to performance:
- option_id
- option_name
- option_value
- autoload
An important thing to know about the wp_options table is the function of the autoload field. This field contains a value that can be set as yes or not . The value essentially controls whether the option should be loaded by the functionwp_load_alloptions()or not.
Autoload data is the data that is loaded into every page of the WordPress site . The autoload attribute is set to “yes” by default for developers, but not all plugins should theoretically load their data on every page.
The problem arises when the autoload data becomes many and heavy to load. This is typically caused when:
- The data of a plugin is loaded on all pages of the site when it would not be needed. A good example is the contact form plugins. Does the plugin have to load data on each page or just the contact page?
- The plugins or themes were removed from the WordPress site but their options remained in the wp_options table. This forgetfulness could load unnecessary data for each request, slowing down the page execution.
- Plugin and theme developers save data in the wp_options table instead of using their own tables. There are advocates for both ways of saving options, in fact several developers prefer plugins that do not create additional tables in the database. However, the wp_options table was not designed to hold thousands of rows.
What is a good size for autoload options and when is too much? Of course this can vary, but as a general guideline it is considered a good value if it is less than 1MB. When autoload data weighs more than 3MB there are probably things that can be optimized or removed from autoloading. However, situations with autoloads greater than 10 MB should be resolved as soon as possible.
Check the size of the autoload data
If you are noticing slowdowns on your WordPress site, the problem could be due to an autoloaded query or data left by an old plugin. Below I show you how to control the size of autoload data in your database. To do this, log in to phpMyAdmin. Click the database to analyze in the left column, then click the SQL tab in the horizontal menu at the top. Then enter the following command and press “Go”.
You may need to change the query above if your WordPress site uses a prefix other than wp_ .
The autoload_size will return the value in bytes. There are 1000 bytes in a KB and 1000 KB in a MB. So in our case, 193,441 bytes equals 0.19 MB. For this site it’s a good size! If you get values below 1MB you shouldn’t worry. However, if the result is much larger, continue with this tutorial.
Sort the data by autoload size
The next step is to sort the elements in autoload, from heaviest to lightest. Here is a quick SQL command you can use to list the top 20 items:
Again, you may need to change the query if your WordPress site uses a prefix other than wp_.
Decide what to delete
The last step is to decide what to leave and what to delete.
If you see in the newly generated TOP 20 options with names you don’t recognize, search on Google, it will be easy to trace the plugin that generated that field.
Delete any option and theme fields that you are no longer using. I run this check once a year, and while I’m not in the habit of testing new plugins on the production site, I always find something to get rid of.
Let me know in the comments if cleaning up the wp_options table has benefited you!
