How to Clean up Your wp_options Table and Autoloaded Data

Today we’re going to take a look at the wp_options table in your WordPress database. This is one area which often gets overlooked when it comes to overall WordPress and database performance. Especially on older and large sites, this can be the culprit for slow query times on your site due to autoloaded data that is left behind from third-party plugins and themes. Check out these tips below on how to check, troubleshoot, and clean up your wp_options table.

What is the wp_options table?

The wp_options table contains all sorts of data for your WordPress site such as:

• Site URL, home URL, admin email, default category, posts per page, time format, etc
• Settings for plugins, themes, widgets
• Temporarily cached data

The table contains the following fields, one of which we care about more when it comes to performance:

• option_id
• option_name
• option_value
• autoload

One of the important things to understand about the wp_options table is the autoload field. This contains a yes or a no value (flag). This essentially controls whether or not it is loaded by the wp_load_alloptions() function. Autoloaded data is data that is loaded on every page of your WordPress site. Just like we showed you how to disable certain scripts from loading sitewide, the same idea applies here. The autoload attribute is set to “yes” by default for developers, but not every plugin should theoretically load their data on every page.

The problem WordPress sites can run into is when there is a large amount of autoloaded data in the wp_options table. This is typically a result of the following:

• Data is being autoloaded by a plugin when really it should be set to “no.” A good example of this would be a contact form plugin. Does it need to load data on every page or just the contact page?
• Plugins or themes have been removed from the WordPress site, but their options are still left behind in the wp_options table. This could mean unnecessary autoloaded data is getting queried on each request.
• Plugin and theme developers are loading data into the wp_options table instead of utilizing their own tables. There are arguments to both sides of this, as some developers prefer plugins that don’t create additional tables. However, the wp_options table also wasn’t designed to hold thousands of rows.

How much is too much-autoloaded data? This can vary of course, but ideally, you want this to be between 300 KB to 1MB. Once you start approaching the 3-5 MB range or more, there are most likely things that can be optimized or removed from being autoloaded. And anything above 10 MB should be addressed right away. This doesn’t always mean it’s going to cause an issue, but it’s a good place to start.

Troubleshooting Autoloaded Data in wp_options table

If you are experiencing slowness on your WordPress site, it might be due to a query or autoloaded data left behind from an old WordPress plugin. Below we’ll show you how to check the autoloaded size in your database, as well as dive into a live site’s data and share what we did to clean it up.

Check Autoloaded Data Size

The first thing to do is check the current autoloaded size on your WordPress site. To do this, log in to phpMyAdmin. Click on your database on the left-hand side, and then on the SQL tab. Then input the following command and hit “Go.”

SELECT SUM(LENGTH(option_value)) as autoload_size FROM wp_options WHERE autoload='yes';

You might have to tweak the query above if your WordPress site is using a different prefix other than wp_.

The autoload_size will return in bytes. There are 1024 bytes in a KB and 1024 KBs in a MB. So in our case, 249,025 bytes equals 0.25 MB. So for this site, this is a good size! If you return anything below 1 MB you shouldn’t be worried. However, if the result was much larger, continue on with this tutorial.

Below is a site we were testing in which 137,724,715 bytes were returned or rather 137 MB. This is a good example of a site where something is definitely wrong, or rather there are things to optimize.

You could also use a longer query such as the following. This will show you the autoloaded data size, how many entries are in the table, and the first 10 entries by size.

SELECT 'autoloaded data in KiB' as name, ROUND(SUM(LENGTH(option_value))/ 1024) as value FROM wp_options WHERE autoload='yes'
UNION
SELECT 'autoloaded data count', count(*) FROM wp_options WHERE autoload='yes'
UNION
(SELECT option_name, length(option_value) FROM wp_options WHERE autoload='yes' ORDER BY length(option_value) DESC LIMIT 10)

If you have access to New Relic (license required), you could also use it to help troubleshoot queries connected to the wp_options table. The databases tab will point out the table and type of query consuming the most time. If you select one of the entries in the list you can see more detail including some sample queries. In the example below, you can see that the data points a finger at autoloaded data in the wp_options table. Sure enough, a quick analysis of the site in question confirmed nearly 250 MB of autoloaded data.

Sort Top Autoloaded Data

The next step would be to quickly sort the top items with autoloaded data. Here is a quick SQL command you can use to list the top 10:

SELECT option_name, length(option_value) AS option_value_length FROM wp_options WHERE autoload='yes' ORDER BY option_value_length DESC LIMIT 10;

Again, you might have to tweak the query above if your WordPress site is using a different prefix other than wp_.

Digging Into Individual Autoloaded Data in wp_options

The next step was to dig into some of the top autoloaded data.

301_redirects

As we can see above the top autoloaded option is 301_redirects. This is probably directly related to a redirection plugin on the site or the WordPress SEO plugin, which also has a redirect function. In this instance, the best recommendation is to actually implement the redirects at a server-level.

Why? Because using free WordPress plugins to implement redirects can sometimes cause performance issues as most of them utilize the wp_redirect function, which requires additional code execution and resources. And of course, it is also autoloading data into the wp_options table.

If you are a Kinsta client, you can easily add redirects at the server-level using the redirect rules tool within your MyKinsta dashboard. Not only is this better for performance, but you also have one less plugin to worry about!

wpurp_custom_template_

The next top autoloaded data option was wpurp_custom_template_#. We can see there are quite a few different rows for this. Typically you should be able to find this option name and connect the dots by looking in your themes or plugins folder. In this case, we did a grep command from the server to see if we could find it. You could also spot check it via SFTP.

grep -Ri "wpurp_custom_template_"

The above command, however, didn’t return anything and so we went over to Google and performed a search. We quickly discovered that it was related to a WordPress plugin that was no longer installed on the site, known as WP Ultimate Recipe. This is a classic example of unnecessary autoloaded data left behind. We have a lengthy tutorial on how to uninstall WordPress plugins (the proper way). And by proper, we mean actually cleaning up what is left behind.

um_cache_userdata_

The next top autoloaded data option was um_cache_userdata_#. We can see there are quite a few different rows for this. Since this was towards the bottom, we quickly modified our MySQL command to show the top 40 autoloaded data:

SELECT option_name, length(option_value) AS option_value_length FROM wp_options WHERE autoload='yes' ORDER BY option_value_length DESC LIMIT 40;

Or sum all values with that prefix:

SELECT 'sum size in KiB', ROUND(SUM(length(option_value))/1024,0) FROM wp_options WHERE autoload='yes' AND option_name like "um_cache_userdata_%"

We could see that there were a lot more entries for um_cache_userdata_# in the wp_options table. We again ran a grep command to check our plugins and themes folders.

grep -Ri "um_cache_userdata_"

We were then able to quickly identify this as being related to the Ultimate Member plugin. Another quick Google search returned a few good solutions to this problem (see the support article). Never underestimate the power of a Google Search! It turns out there were a few different options available in the plugin to solve this issue.

• Ultimate Member > Dashboard > User Cache > Clear Cache.
• Ultimate Member -> Settings -> Advanced -> Stop caching user’s profile data (switch to ON), then Save Changes.

Another option for seeing what an autoloaded option is is by hitting the edit button, and this can list the directory of the plugin/theme, or list the developer’s website.

Cron Jobs

Another frequent option that we see with a high amount of autoloaded data is cron. For this, it could be anything cron related. So what you can do is hit the “edit” button to see what’s causing it. Here’s an example below in which it was apparent that “do_pings” was causing the issue. Again, a quick Google search revealed a quick fix to clean up do_pings.

Cleanup wp_options Table

If you are seeing a lot of what we mentioned above, then it is probably time for a cleanup of all the autoloaded data in your wp_options table. It is also recommended that you try and keep the number of rows on your wp_options table to a minimum. Please always take backups before deleting data in your database. If you’re not comfortable doing this yourself, we always recommend hiring a WordPress developer. This is also a good scenario where a staging environment can come in handy.

Like we did earlier, you’ll need to log in to phpMyAdmin. Click on your database on the left-hand side, and then on the SQL tab. Then input the following command and hit “Go.”

SELECT * FROM `wp_options` WHERE `autoload` = 'yes'

You might have to tweak the query above if your WordPress site is using a different prefix other than wp_. This will show you all of the data in the wp_options table that is set to autoload.

Scrolling down through the rows we see all sorts of plugins that are no longer installed or utilized by the site. This is just an example we are going to use, but in this case, we noticed a bunch of Jetpack rows. Jetpack was no longer being used on the site in question.

It’s always good to check the plugin developer’s documentation as sometimes they have an option to clean up their left behind tables. In which case, sometimes it’s safer and easier to simply install the plugin again, check their automated cleanup option, and then remove the plugin correctly. However, we will show you how to clean up the tables manually.

So, in this case, we run the following query to find the autoloaded data in the wp_options table from the Jetpack plugin. To modify the query with your own, simply replace %jetpack%.

SELECT * 
FROM `wp_options` 
WHERE `autoload` = 'yes'
AND `option_name` LIKE '%jetpack%'

You can then select all of the rows and click on “Delete.”

Or you could run the following command:

DELETE
FROM `wp_options` 
WHERE `autoload` = 'yes'
AND `option_name` LIKE '%jetpack%'

You can then rinse and repeat for additional autoloaded data left behind from plugins and themes in your wp_options table.

Clean up Transients

Unless you’re using an object cache, WordPress stores transient records in the wp_options table. Typically these are given an expiration time and should disappear over time. However, that is not always the case. We have seen some databases where there are thousands of old transient records. It’s also important to note that transients are not to autoloaded by default. You could use a query like the below to see if there are any autoloaded transient data.

SELECT * 
FROM `wp_options` 
WHERE `autoload` = 'yes'
AND `option_name` LIKE '%transient%'

However, a better and safer option would be to utilize a free plugin like Transient Cleaner which can clean up only the expired transients from your wp_options table.

Clean up WordPress Sessions

Another common issue we’ve seen is sometimes cron jobs get out of sync or don’t fire properly and therefore sessions don’t get cleaned up. You can wind up getting tons of _wp_session_ rows in your database. In this example below the site in question wound up with over 3 million rows in their wp_options table. And the table had grown to over 600 MB in size.

You could use a query like the one below to see if you’re running into this issue:

SELECT * 
FROM `wp_options` 
WHERE `option_name` LIKE '_wp_session_%'

In most cases you can then safely delete these (as a cron job should have) with the following command:

DELETE FROM `wp_options` 
WHERE `option_name` LIKE '_wp_session_%'

After cleaning up all the leftover _wp_session_ rows the table had less than 1,000 rows and was reduced to 11 MB in size.

It also fixed spikes the site was getting in MySQL.

Add an Index to Autoload

And if cleaning up your wp_options table wasn’t enough, you could try adding an “index” to the autoload field. This essentially can help it to be searched more efficiently. The awesome team over at 10up performed some test scenarios on a wp_options table with a typical number of autoloaded records to show how adding an autoload index to wp_options queries can boost performance.

We also recommend checking out these two additional resources from WP Bullet:

• How to Add MySQL Index to wp_options table
• Cleaning up the wp_options table using WP-CLI

For even more optimization tips make sure you take a look at our in-depth guide: How to Speed up Your WordPress Site (Ultimate Guide)