How to clean up wp_options tables and automatically loaded data

Let’s take a look at the wp_ options table in the WordPress database. This is an area that is often overlooked when it comes to overall WordPress and database performance. Especially on older large websites, this may be the main culprit for slowing down the query time of the site due to the automatically loaded data left by third-party plug-ins and themes. Review the following tips on how to check, troubleshoot, and clean up the wp_ options table. What is the

wp_options tables? The

wp_ options table contains all kinds of information for your WordPress website, such as:

• site URL, home page URL, administrator email, default category, per page article, time format and other
• plug-ins, themes, widgets,
• temporarily cached data

wp_options table

this table contains the following fields We are more concerned with one of the fields in terms of performance: the

• option_id
• option_name
• option_value
• autoload

wp_options table automatically loads

to understand that one of the important things about the wp_ options table is the autoload field. This contains a yes or no value (flag). This basically controls whether it is loaded by the wp_load_alloptions () function. The data that is loaded automatically is the data that is loaded on each page of the WordPress site. Just as we showed you how to disable certain scripts from loading on a site-wide basis, the same idea applies here. By default, developers’ autoload property is set to “yes”, but not every plug-in should theoretically load their data on every page. The problem that

WordPress sites may encounter is that there is a large amount of automatically loaded data in the wp_ options table. This is usually caused by the following reasons: the

• data is loaded automatically by the plug-in, when in fact it should be set to “no”. A good example is the contact form plug-in. Does it need to load data on each page or only on the contact page?
• plug-ins or themes have been removed from the WordPress site, but their options remain in the wp_ options table. This may mean that unnecessary automatically loaded data is queried for each request.
• plug-ins and theme developers are loading data into wp_ options tables instead of using their own tables. This is controversial because some developers prefer plug-ins that do not create an amount of appearance. However, the wp_options table is not designed to hold thousands of rows. How much data is automatically loaded with too much

? This can change, of course, but ideally, you want it to be between 300KB and 1MB. Once you start approaching the 3-5MB range or more, you’ll probably be able to optimize or remove automatically loaded content. Anything that exceeds the 10MB should be resolved immediately. This doesn’t always mean it can cause problems, but it’s a good place to start.

troubleshoots automatically loaded data in the wp_ options table

if your WordPress site is slow, it may be due to queries left over by the old WordPress plug-in or automatic loading of data. Next we will show you how to check the automatically loaded size in the database, as well as delve into the data of the real-time site and share the work we have done to clean it up.

check the size of the data that is loaded automatically the first thing

does is check the size that is currently automatically loaded on the WordPress site. To do this, log in to phpMyAdmin. Click the database on the left, and then click the SQL tab. Then enter the following command and click “Go”.

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

if your WordPress site uses a prefix other than wp_, you may need to adjust the query above. The automatically loaded size query

autoload_size in

phpMyAdmin is returned in bytes. There are 1024 bytes in KB and 1024KB in MB. So in our example, 249025 bytes equals 0.25MB. So for this site, this is a good size! If you return anything lower than 1MB, you don’t have to worry. However, if the result is much larger, continue with this tutorial.

Auto load size

below is the site we are testing, which returns 137724715 bytes, or rather 137MB. This is a good example of a website where there must be something wrong, or something that needs to be optimized. You can also use longer queries for large autoload data

in the

wp_ options table, as shown below. This displays the size of the data that is loaded automatically, how many entries are in the table, and the top 10 entries arranged 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)

Advanced Auto-load data MySQL query

if you have access to New Relic, you can also use it to help resolve queries connected to the wp_ options table. The database tab indicates the type of table and query that takes the most time. If you select one of the entries in the list, you can see more details, including some sample queries. In the following example, you can see that the data points to the data that is automatically loaded in the wp_options table. Sure enough, a quick analysis of the site confirmed the near-250MB auto-loading data.

New Relic slow query-the wp_options table

sorts the data automatically loaded at the top

the next step is to quickly sort the top items using the automatically loaded data. This is a quick SQL command that can be used 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;

similarly, if your WordPress site uses a prefix other than wp_, you may need to adjust the query above. Top data that is automatically loaded in the

wp_options table

Mining a single automatically loaded data in wp_options the next step is to mine some of the most important automatically loaded data.

301 redirect

as we saw above, the autoload option at the top is 301_redirects. This may be directly related to the redirect plug-in or WordPress SEO plug-in on the site, which also has redirect capabilities. In this case, the best recommendation is to actually implement redirection at the server level.

, why? Because using free WordPress plug-ins to implement redirects can sometimes cause performance problems, because most of them use wp_redirect functions, which require additional code execution and resources. Of course, it also automatically loads data into the wp_ options table.

if you are a pagoda panel, you can easily add redirects at the server level using our 301 redirect configuration. Not only does this improve performance, but you may be missing a plug-in to worry about!

login to your pagoda panel, click the website menu, find the website you need to configure 301redirect, click “Settings” action item:

pagoda panel website management

and then in the pop-up window, select 301redirect, select the redirect type as the path, and then set the source address and redirect URL. (note: there may be differences between different pagoda panels.)

adds redirection rules to the pagoda panel.

the next option to automatically load data is wpurp_custom_template_#. We can see that there are many different lines. In general, you should be able to find this option name and connect points by looking at your theme or plug-in folder. In this case, we execute the grep command from the server to see if it can be found. You can also conduct spot checks through SFTP.

wpurp_custom_template_

however, the above command did not return anything, so we went to Google and did a search. We soon discovered that it was related to the WordPress plug-in that is no longer installed on the site, called WP Ultimate Recipe. This is a classic example of leaving unnecessary auto-loading data. We have a lengthy tutorial on how to uninstall the WordPress plug-in (the right way). Appropriately, what we mean is to actually clean up what is left behind. The next option for

grep -Ri "wpurp_custom_template_"

to automatically load data is um_cache_userdata_#. We can see that there are many different lines. Since this is at the bottom, we quickly modified our MySQL command to display the first 40 automatically loaded data:

wpurp_custom_template_

um_cache_userdata_

or add all the values with this prefix:

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

. We can see more um_cache_userdata_# entries in the wp_options table. We run the grep command again to check our plug-ins and theme folders.

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_%"

then we can quickly determine that this is related to the Ultimate Member plug-in. Another quick Google search returns some good solutions to this problem (see support article). Never underestimate the power of Google search! It turns out that there are several different options in the plug-in that can solve this problem.

grep -Ri "um_cache_userdata_"

Ultimate Member & gt; Dashboard & gt; User Cache & gt; Clear Cache.

• Ultimate Member-& gt; Settings-& gt; Advanced-& gt; Stop caching user’s profile data (switch to ON), and then save the changes. Another option for
• to view the autoload option is to click the edit button, which can list a directory of plug-ins / themes, or a developer’s Web site.

Scheduled task

another common option we see in a large number of autoload data is cron. For this reason, it can be anything related to cron. So what you can do is click the “edit” button to see what caused it. Here is an example where “do_pings” is clearly causing the problem. Third, a quick Google search reveals a quick fix for cleaning up do_pings.

cron-do_pings

cleans up the wp_options table

if you see a lot of what we mentioned above, it may be time to clean up all the automatically loaded data in the wp_options table. It is also recommended that you try to keep the number of rows on the wp_ options table to a minimum. Always make a backup before deleting the data in the database.

as we did before, you need to log in to phpMyAdmin. Click the database on the left, and then click the SQL tab. Then enter the following command and click “Go”.

if your WordPress site uses a prefix other than wp_, you may need to adjust the query above. This displays all the data in the wp_options table that is set to load automatically.

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

looks in wp_options for automatically loaded data

scrolls down rows, and we see various plug-ins that are no longer installed or used on the site. This is just an example we’re going to use, but in this case, we notice a bunch of Jetpack lines. Jetpack is no longer used on related websites.

‘s old autoload data

check plug-in developer’s documentation is always good, because sometimes they can choose to clean up legacy tables. In this case, sometimes it is safer and easier to simply install the plug-in again, check its auto-cleanup options, and then remove the plug-in correctly. However, we will show you how to clean up the table manually.

therefore, in this case, we run the following query to find the automatically loaded data from the wp_ options table of the Jetpack plug-in. To modify the query with your own query, simply replace% jetpack%.

then you can select all rows and click Delete.

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

deletes the automatically loaded table

or you can run the following command:

deletes the automatically loaded data

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

in the wp_options table, and then you can clean and repeat other automatically loaded data left by plug-ins and themes in the wp_ options table.

Clean up transient

unless you use object caching, WordPress stores temporary records in the wp_ options table. Usually these have an expiration time and should disappear over time. However, this is not always the case. We have seen thousands of old temporary records in some databases. It is also important to note that transient does not automatically load by default. You can use the following query to see if there is any automatically loaded transient data.

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

, however, a better and safer option is to use a free plug-in like Transient Cleaner, which can only clear expired transients from the wp_ options table. Another common problem we see with

cleaning WordPress session

is that sometimes cron jobs are out of sync or are not triggered correctly, so the session is not cleaned up. You may

to get a large number of rows in the database. In the following example, the site in question has more than 3 million rows in its_wp_session_table. The size of the table has exceeded that of 600MB.wp_optionshas a multimillion-row wp_options table

you can use a query like the following to see if you are experiencing this problem: in most cases, you can safely delete these (as a cron job should have) with the following command: after

cleans up all the remaining

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

tables, the table has fewer than 1000 rows, reducing the size to 11MB. The

WP session cleaned the

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

and it also fixed spikes in the site in MySQL._wp_session_ rowsMySQL network transaction

add indexes to automatically load

if cleaning up the wp_ options table is not enough, you can try adding “index” to the autoload field. This basically helps it search more effectively. 10up’s excellent team performed some test scenarios on the wp_ options table that included a typical number of autoload records to show how adding an autoload index to a wp_options query could improve performance.

wp_options query time (Img src: 10up)

We also recommend that you view these two additional resources from WP Bullet: how

adds the MySQL index to the wp_options table

uses WP-CLI to clean up the wp_options table

for more optimization tips, please make sure you check out our in-depth guide: how to accelerate your WordPress website (ultimate guide)

• 如何将MySQL索引添加到wp_options表
• 使用WP-CLI清理wp_options表

有关更多优化技巧，请确保您查看我们的深入指南： 如何加速您的WordPress网站（终极指南）