WordPress works with PHP and MySQL. The core PHP files will pull data and parameters from the database to assemble the page. However, it happens dynamically each time when a page loads on the browser. This way of working helps you to define custom functions for any single page and load it differently compared to other pages on the same site. Wp_options table in WordPress stores the auto loading site settings thus decides the overall behavior of page loading. Hence, it is important to understand the options stored in this table and cleanup wp_options table in WordPress.
Learn WordPress: Check out 500+ free WordPress tutorials.
Why to Cleanup wp_options Table?
WordPress uses few core database tables to store and retrieve content. Wp_posts, wp_postmeta, wp_users, wp_usermeta, wp_commentmeta and wp_options are some of the core tables to distribute the data. Posts and meta tables are used to store post content and related meta data respectively. However, options table does not store content. It stores site wide settings to decide what parameters need to be loaded on the page load. All settings you see in the WordPress admin panel are stored in wp_options table. WordPress uses options and settings APIs to write the site settings into options table.
Site URL, admin username, admin email are some of the entries you can find in options table. The problems comes when your theme and plugins add additional parameters in this table thus increasing number of loading parameters. As it is obvious, more number of parameters will drastically affect the loading speed of your site.
There are two major reasons for you to look into the options table:
- Check the number of entries and size to find whether it is optimal for your site.
- Find which plugin creates large entries so that you can consider alternatives. If the plugin is not used, you can simply delete the entries as part of maintenance activities.
Hence, it is important to check and cleanup wp_options table especially after prolonged usage.
Accessing Database Tables
First step is to learn how to access database tables in WordPress.
- Login to your hosting account and then go to cPanel section. Most hosting companies offer integrated cPanel so that you do not need to login again. Otherwise, you may need to login to your cPanel separately.
- Search phpMyAdmin app in cPanel and open it. It will show all databases on your hosting account and locate the database used on the site you want to cleanup.
- Expand the required database and click on the wp_options table to view its content.
You can see the number of entries when clicking on the table.
Structure of wp_options Table
Wp_options table has the following structure:
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| option_id | bigint(20) unsigned | PRI | auto_increment | ||
| option_name | varchar(64) | UNI | |||
| option_value | longtext | ||||
| autoload | varchar(20) | yes |
You can also view the content of the options table directly from your WordPress admin panel by going to “Settings” or “Widgets” sections. For example, the general settings section URL should be “yoursite.com/wp-admin/options-general.php”.
Our focus for this article is to find the settings with “autoload = yes” value.
Checking Entries with Autoload = Yes
The parameters with autoload = yes will load on all the pages, hence you have to look into whether it is required or not. For example, you might have deleted a plugin but the residual entries left over in the table still loads without any use. Being said that, plugins often add entries in the tables, however will not delete the entries when you deactivate and delete them. This will help the plugin to restore the settings when you reinstall it. However if you are not reinstalling the plugin anymore, table entries are redundant affecting your site’s speed.
Copy the following query, paste it under “SQL” tab and hit go button.
SELECT * FROM `wp_options` WHERE `autoload` =’yes’
You can get the number of entries that auto loads from wp_options table like below.
The above screenshot shows less number of entries as it is on the fresh demo installation. You should see large number of entries on live WordPress site that is running for years. In addition, we use “wp_” as table prefix on all our code examples. However, your table prefix may be different. As you can see in the screenshot, our demo tables have “wpeg_” as a prefix.
Checking Size of Autoloading Entries
If you want to check the size of the autoloading settings from wp_options, table then use the following query.
SELECT SUM(LENGTH(option_value)) as autoload_size FROM wp_options WHERE autoload=’yes’
This will show the size of the autoloading parameters in KB.
Other useful analysis is to find the top loading entries from options table. You can use the following query to find the top 10 settings with high size from options table.
SELECT option_name, length(option_value) AS option_value_length FROM wp_options WHERE autoload=’yes’ ORDER BY option_value_length DESC LIMIT 10
You can change the limit from 10 to your desired value like 20 or 30. You will get results like below showing the option names with large number of entries. You should be careful in analyzing these data as some entries could be from WordPress core. For example, “rewrite_rules” is the standard parameter, you should never delete.
You have to focus on finding the entries created by plugins. AS you can see, “siteground_optimizer_whats_new” and “sg_cachepress” are the entries from SG Optimizer plugin.
Note, all the above queries are for simply selecting and showing the result and will not have any impact.
Deleting Unused Autoloading Parameters
Remember, there are no hard rules about how many number of entries you should keep in the table. As a common sense, you should delete all unused entries and find the plugin that creates thousands on entries. After checking the number of entries, size and top entries, you should have an idea of what could go wrong with options table.
One of the actions is required based on your analysis:
- You have to delete specific plugin and find alternate that does not add large options table entries.
- Delete unused entries by already deleted plugins.
You can also make the setting to autoload=no if you do not want to load on all pages. However, it is not recommended to keep unnecessary entries in the table. When you decided to delete the entries, first step is to find all entries available with that parameter. Use the below query and replace the “optimizer” with your own value to find the number of entries created by that plugin.
SELECT * FROM `wp_options` WHERE `autoload` = ‘yes’ AND `option_name` LIKE ‘%optimizer%’
You will get the results like below. Now you can simply select all and delete or use the following query to delete the entries:
Alternatively, you use the below query to delete the entries with specific value in the name.
DELETE FROM `wp_options` WHERE `autoload` =’yes’ AND `option_name` LIKE’ %optimizer%’
Follow the similar approach for finding all unused plugin entries and delete them from your database.
Related: Take your WordPress site to next level with these 100+ free WordPress tips and tricks.
Checking the Site
Before deleting entries from table, ensure to take a backup of your entire database. After you have deleted database content, it is also essential to test the site in the frontend. Make sure your site works properly and not breaking any plugin or theme’s functions. The tangible site loading speed depends on the number of entries you have deleted from wp_options table. Deleting few hundred entries may not make big difference though you should do it for cleaning purpose. However, removing millions of entries will make a huge difference in page loading speed.
Leave a Reply
Your email is safe with us.