In this article you will learn how to clean up your wp_options table and autoloaded data and significantly improve your database performance. Autoloaded data might slow down and affect your website performance.
There is a significant chance that the autoloaded settings on an older and large WordPress site are far larger than necessary. This is also typical of websites that have previously either used a variety of plugins and themes or have only been tested.
Your WordPress site may be taking longer to load than usual because of a query or autoloaded data. When we are enhancing database performance, we often neglect this most important i.e., to clean up your wp_options and autoloaded data.
Below you will learn what is basically wp_option table, how much data your site should contain and how you can clean up the wp_options table and autoloaded data.
What is wp_options table
For your WordPress website, the wp_options database stores a variety of data, including:
- Site URL, home URL, default category, admin email, time format and posts per page
- Settings for themes, widgets, plugins
- Temporarily cached data and files etc.
The following fields are included in the table
The autoload field in the WP OPTIONS table is the most important concept to know. This field can have a value of YES or NO, and ROWS with YES will automatically load the data on all web pages that are loaded using the wp load all options and eventually slowing down your site.
Get exclusive access to all things tech-savvy, and be the first to receive
the latest updates directly in your inbox.
What is a Autoloaded data?
As the name suggests Autoloaded data is the data that loads automatically on each page of your WordPress website page load. WordPress sites may experience issues if the wp_options database has a significant volume of autoloaded data.
Although every plugin should ideally load its data on every page, the autoload parameter is set to “yes” by default for developers. WordPress sites may experience issues if the wp_options database has a lot of autoloaded data.
How much data is excessive autoloaded data?
This should be between 300KB – 1MB in size. There are probably things that can be optimized or taken out of autoloading if you reach the 3-5 MB range or beyond. And anything larger than 10 MB needs to be dealt with straight away.
What causes autoloaded data
A plugin or theme author loads data into the wp_options table instead of using their own tables. Some developers like plugins that do not create new tables. WP options, however, aren’t designed to handle thousands of rows.
The wp options table still contains the options from plugins or themes that have been deleted from the WordPress website. This can result in the querying of unneeded autoloaded data. Although autoloaded options may have benefits, they can also be harmful. 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.
If you have more than recommended auto loaded data then a cleansing of all the automatically loaded data in your wp_options table is definitely needed. Additionally, it is advised that you make an effort to reduce the number of records in your wp_options table. Please always create backups of your database before deleting any data. We usually advise hiring a WordPress developer if you don’t feel confident doing this yourself.
Data is being autoloaded by a plugin when it actually should be set to “NO.”
How to check Autoloaded Data in wp_options table
- Open your CyberPanel dashboard
- From right hand side click on WordPress
- Select list WordPress
- After clicking on the WordPress list, you will see your WordPress websites you have deployed. Select the site for which you want to optimize the database.
- Once you have selected your WordPress site, click on Database tab,
- See your database name, now this is the database for your selected WordPress site. We will access this database through PHPMyAdmin and optimize the wp_options table.
After seeing your database name
- Click on Database from left hand side
- Select PHPMYADMIN
- From left hand side click on your database name
- Select wp_options from left side menu
- Select SQL from top menu
- Add the following SQL query and click GO
SELECT SUM(LENGTH(option_value)) as autoload_size FROM wp_options WHERE autoload='yes';
Here you will see your total autoload size in KBs, if its more then 3000KB ( it means above 2MB ), then its about time you look for database optimizations.
You can also add a longer query to check the first ten 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)
Above query results show the top keys as you can see in the image.
Here’s the SQL command you can use to list the top 10 values by their length:
SELECT option_name, length(option_value) AS option_value_length FROM wp_options WHERE autoload='yes' ORDER BY option_value_length DESC LIMIT 1
As you can see that the option_name rewrite_rules have the biggest length of 9153 it can be checked to see if this value can be optimized or removed.
How to cleanup wp_options Table
Like we did previously, 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'If your WordPress site uses a prefix other than wp_, you might need to modify the query above. The wp options table’s entire set of autoloading data will be displayed to you in this manner.
Always read the plugin developer’s documentation because there may be a way to clean up any leftover tables. In that scenario, reinstalling the plugin, selecting its automated cleanup option, and then properly removing it might sometimes be safer and simpler. We will demonstrate how to manually clear the tables, though.
As we scroll through the rows, we notice a variety of plugins that the site no longer uses or has installed. We’re just going to use this as an example, but in this instance, we saw a lot of status rows. On the questioned website, status was no longer in use.
Use this command
SELECT *
FROM `wp_options`
WHERE `autoload` = 'yes'
AND `option_name` LIKE '%status%'- You can change the end word with file name e.g., status into email to clear it
Clear up Transients
The wp_options table is where WordPress keeps temporary records unless you’re using an object cache. These often have an expiration date and should vanish over time. That isn’t always the case, though. Thousands of outdated transitory records have been found in several databases that we have seen. It’s also vital to keep in mind that transients are not always automatically loaded. To determine if there is any autoloaded transitory data, you may perform a query similar to the one below.
SELECT *
FROM `wp_options`
WHERE `autoload` = 'yes'
AND `option_name` LIKE '%transient%'Similary you can use delete query to clear up transient that are no longer needed.
Check the Site
Moreover, try to create a backup of your complete database before deleting entries from a table. Testing the website in the front end is equally crucial after deleting database content. Verify that your website operates well and does not interfere with any plugin or theme functionalities. Depending on how many entries you have removed from the wp_options table, the pace at which the site actually loads will vary. Even though you should delete a few hundred records for cleaning purposes, it might not make much of a difference. However, millions of entries being removed will significantly slow down website loading.
Conclusion
Your WordPress site may be taking longer to load than usual because of a query or autoloaded data. There were different methods and commands we used to clear up extra or autoloaded data. When you clear up your wp_tables and autoloaded files you will be able to enhancing your database performance.
