Overview

We’ll be looking at the WP options table in your WordPress database today. When it comes to overall WordPress and database performance, this is one aspect that is sometimes underestimated. This can be the cause of slow query speeds on your site, especially on older and larger sites, due to autoloaded data left behind by third-party plugins and themes. Check, debug, and clear up your wp options table using the instructions below.

What is the wp options table?

The wp options table holds a variety of information for your WordPress site, including:

URL of the site, home URL, admin email, default category, number of posts per page, time format, and so on

Plugins, themes, and widgets settings

Data that has been cached for the time being

table wp options

The table comprises the fields below, one of which is more important in terms of performance:

option id

option name

option value

autoload

autoload wp options table

The autoload column is one of the most critical aspects of the wp options database to comprehend. This has a yes or no value in it (flag). This effectively affects whether the wp load alloptions() method loads it or not. Data that is loaded on every page of your WordPress site is known as autoloaded data. The same concept applies here as it did when we showed you how to prevent particular scripts from loading sitewide. For developers, the autoload parameter is set to “yes,” but not every plugin should potentially load their data on every page.

When there is a lot of autoloaded data in the wp options table, WordPress sites can get into trouble. This is usually caused by the following:

A plugin is causing data to be autoloaded when it should be set to “no.” A contact form plugin is a nice illustration of this. Is data need to be loaded on all pages or only the contact page?

Although plugins and themes have been removed from the WordPress site, their choices remain in the wp options table. This could indicate that each request is querying superfluous autoloaded data.

Rather than using their own tables, plugin and theme authors are importing data into the wp options table. Some developers prefer plugins that don’t produce additional tables, thus there are arguments on both sides. The wp options table, on the other hand, was not built to handle thousands of records.

How much autoloaded info is too much? Of course, this can vary, but ideally, it should be between 300 and 1MB. When you get into the 3-5 MB level or more, there are probably certain things that can be improved or deleted from autoloading. Anything more than 10 MB should be dealt with immediately away. This does not always imply that it will cause a problem, but it is an excellent place to start.

Troubleshooting Autoloaded Data in wp options table

If your WordPress site is slowing down, it could be due to a query or autoloaded data left behind from an outdated WordPress plugin. We’ll show you how to verify your database’s autoloaded size, as well as dive into a live site’s data and reveal what we did to clean it up, in the sections below.

Check the size of the autoloaded data

The first step is to examine your WordPress site’s current autoloaded size. Log into phpMyAdmin to do so. On the left-hand side, select your database, then the SQL tab. Then type the following command and press the “Go” button.

FROM wp options, SELECT SUM(LENGTH(option value)) as autoload size WHERE autoload=’yes’; WHERE autoload=’no’; WHERE autoload=

If your WordPress site uses a prefix other than wp_, you may need to change the query above.

phpmyadmin autoload size inquiry

phpMyAdmin query for autoload size

The value returned by autoload size is in bytes. A KB is made up of 1024 bytes, and an MB is made up of 1024 KBs. 249,025 bytes is 0.25 MB in our situation. This is an excellent size for this site! You shouldn’t be concerned if your response is less than 1 MB. Continue with this tutorial if the outcome was significantly larger.

Size of autoload

The following is a site we tested that returned 137,724,715 bytes, or 137 megabytes. This is a good example of a website where something is clearly wrong, or where things could be improved.

In the wp options table, there is a lot of autoloaded data.

A longer query, such as the one below, could also be used. This will display the size of the autoloaded data, the number of items in the table, and the first 10 entries in order of size.

FROM wp options, SELECT ‘autoloaded data in KiB’ as name, ROUND(SUM(LENGTH(option value)/ 1024) as value WHERE autoload=’yes’

UNION

FROM wp options SELECT ‘autoloaded data count’, count(*) WHERE autoload=’yes’

UNION

(SELECT option name FROM wp options, length(option value)) ORDER BY length(option value) DESC LIMIT 10) WHERE autoload=’yes’

MySQL query with advanced autoloaded data

You may also utilize New Relic to troubleshoot queries related to the wp options table if you have access to it. The databases tab will show you which table and type of query are taking the longest. If you click on one of the entries in the list, you’ll be sent to a page with further information, including some sample queries. You can see in the example below that the data points to autoloaded data in the wp options table. A cursory examination of the website in question revealed approximately 250 MB of autoloaded data.

You can get a feel of what you need to look for in the database by studying the sluggish query details.

The wp options table is a sluggish query in New Relic.

Sort by Autoloaded Data at the Top

The next step is to sort the top things rapidly using autoloaded data. Here’s a fast SQL statement to get a list of the top ten:

SELECT option name, length(option value) FROM option value FROM wp options AS option value length ORDER BY option value length WHERE autoload=’yes’ DESC LIMIT 10; DESC LIMIT 10; DESC LIMIT 10; D

If your WordPress site uses a prefix other than wp_, you may need to make changes to the query above.

The wp options table’s top autoloaded data

In wp options, looking at individual autoloaded data

The following stage was to investigate some of the most popular autoloaded data.

301 redirects

The top autoloaded option, as seen above, is 301 redirects. This is most likely due to a redirection plugin installed on the site or the WordPress SEO plugin, which also includes a redirect feature. The best recommendation in this case is to apply the redirects at the server level.

Why? Because most free WordPress plugins use the wp redirect function, which requires additional code execution and resources, utilizing them to implement redirects can sometimes cause performance concerns. It’s also autoloading data into the wp options table, of course.

If you’re a Kinsta customer, you can use our redirect rules tool to effortlessly set redirects at the server level. This is not only better for performance, but it also means you’ll have one less plugin to worry about!

In MyKinsta, create a redirect rule.

wpurp custom template_

wpurp custom template_# was the next most popular autoloaded data option. There are a lot of alternative rows for this, as we can see. If you search in your themes or plugins folder, you should be able to discover this option name and connect the dots. In this situation, we used the server’s grep command to see if we could discover it. You may also use SFTP to perform a spot check.

“wpurp custom template_” grep -Ri

However, the above command yielded no results, so we turned to Google and conducted a search. We quickly determined that it was tied to WP Ultimate Recipe, a WordPress plugin that was no longer installed on the site. This is a classic case of autoloaded data being left behind. How to uninstall WordPress plugins is covered in detail in this tutorial (the proper way). And when we say proper, we mean truly cleaning up after ourselves.

wpurp custom template_

um cache userdata_

um cache userdata_# was the next most popular autoloaded data option. There are a lot of alternative rows for this, as we can see. We quickly updated our MySQL function to show the top 40 autoloaded data because this was near the bottom:

SELECT option name, length(option value) FROM option value FROM wp options AS option value length ORDER BY option value length WHERE autoload=’yes’ DESC LIMIT 40; DESC LIMIT 40; DESC LIMIT 40; D

Alternatively, add all values with that prefix:

Do you want to discover how we grew our traffic by over 1000%?

Join the 20,000+ people who receive our weekly email with WordPress insider secrets!

Now is the time to subscribe.

FROM wp options SELECT’sum size in KiB’, ROUND(SUM(length(option value))/1024,0) WHERE autoload=’yes’ AND option name is something like “um cache userdata_ percent”

We could observe that the wp options table had a lot more entries for um cache userdata_#. To verify our plugins and themes folders, we used the grep tool once more.

“um cache userdata_” grep -Ri

We were able to rapidly determine that this was a problem with the Ultimate Member plugin. A simple Google search yielded a couple viable solutions to this issue (see the support article). Never undervalue the value of a Google search! It turned out that the plugin had a couple alternative options for resolving the problem.

User Cache > Clear Cache > Ultimate Member > Dashboard > User Cache > Clear Cache

Ultimate Member -> Advanced -> Advanced -> Advanced -> Advanced -> Advanced -> Advanced -> Advanced -> Advanced Change the setting to ON to stop caching the user’s profile data, then Save Changes.

Another way to figure out what an autoloaded option is is to click the edit button, which will display the plugin/directory theme’s or the developer’s website.

Jobs for Cron

With a large amount of autoloaded data, another popular method is cron. It may be anything cron-related for this. So you may use the “edit” button to figure out what’s causing it. Here’s an example where it was clear that “do pings” was the source of the problem. Again, a short Google search yielded a quick workaround for do pings cleanup.

do pings is a cron job that runs on a regular basis.

do pings – cron

wp options Table should be cleaned up.

If you’re seeing a lot of the things listed above, it’s definitely time to clean out your wp options table’s autoloaded data. It’s also a good idea to limit the amount of rows in your wp options table as low as possible. Always make a backup of your database before deleting data. If you don’t feel confident handling it yourself, we always suggest hiring a WordPress developer. This is another situation when a staging environment can be beneficial.

You’ll need to log into phpMyAdmin, just like we did before. On the left-hand side, select your database, then the SQL tab. Then type the following command and press the “Go” button.

FROM ‘wp options’ SELECT * IF ‘autoload’ is set to ‘yes’

If your WordPress site uses a prefix other than wp_, you may need to change the query above. This will display all of the data set to autoload in the wp options table.

In wp options, look for autoloaded data.

As we scroll down the rows, we notice a variety of plugins that are no longer installed or used by the site. We’re simply going to use this as an example, but we saw a handful of Jetpack rows in this scenario. On the site in question, Jetpack was no longer in use.

Do you have issues with downtime with WordPress? Kinsta is a web hosting service that saves you time! Take a look at our highlights.

Data from the past that was autoloaded

It’s usually a good idea to read the plugin developer’s documentation because they may provide an option to clean up any tables that have been left behind. In that instance, it’s sometimes safer and quicker to reinstall the plugin, enable the automated cleanup option, and then remove the plugin properly. We will, however, demonstrate how to manually tidy up the tables.

So, in this situation, to find the autoloaded data in the wp options table from the Jetpack plugin, we run the following query. Simply change percent jetpack percent with your own query to customize it.

* SELECT

SELECT ‘wp options’ FROM ‘wp options’

IF ‘autoload’ is set to ‘yes’

AND ‘option name’ EQUAL TO ‘percent jetpack%’

After that, pick all of the rows and press “Delete.”

Remove tables that have been autoloaded.

Alternatively, you might use the following command:

DELETE

SELECT ‘wp options’ FROM ‘wp options’

IF ‘autoload’ is set to ‘yes’

AND ‘option name’ EQUAL TO ‘percent jetpack%’

In the wp options table, delete the autoloaded data.

Rinse and repeat for any more autoloaded data in your wp options database left over from plugins and themes.

Transients should be cleaned up

WordPress stores transitory records in the wp options table unless you’re using an object cache. These are usually given an expiration date and should vanish over time. That isn’t always the case, though. We’ve seen databases with thousands of old transient records in them. It’s also worth noting that transients aren’t automatically loaded by default. To see if there is any autoloaded transient data, use a query like the one below.

* SELECT

SELECT ‘wp options’ FROM ‘wp options’

IF ‘autoload’ is set to ‘yes’

AND ‘option name’ IS THE SAME AS ‘percentage transient percent’

However, using a free plugin like Transient Cleaner to clean up only the expired transients from your wp options table is a better and safer option.

WordPress Sessions should be cleaned up

Another issue we’ve seen is that cron jobs can get out of sync or fail to fire properly, resulting in sessions not being cleaned up. Your database could end up with a lot of _wp session_ rows. In the example below, the wp options table on the site in question grew to over 3 million rows. And the table had grown in size to over 600 MB.

millions of rows in the wp options table

You can check if you’re having this problem with a query like this one:

* SELECT

SELECT ‘wp options’ FROM ‘wp options’

WHERE’option name’ IS THE SAME AS ‘_wp session_ percent’

rows from wp session

_wp session_ rows rows rows rows rows stadia rows dairies rows rows rows rows rows rows rows rows

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