Cleaning wp_options in WordPress

Considering donating if you found my post helpful 😊

There is a feature in WordPress where data from database are auto-loaded in every page request. This feature is called autoload. This can be a great thing, but also can be misused. On a busy website, autoload can slow it down due to large data being loaded in every page request.

Why we need to clean them up? Because many themes and plugins don’t set the setting properly. Many audoloaded data are not required to be loaded on every page request, and also many of them don’t delete the data after being uninstalled.

This guide teaches some basic idea on how to execute the task. you might need to use preferable software or plugins to get your job done. I will be using mysql CLI and Adminer.

Check the size of autoloaded data

You can execute this SQL command to see how big is the data that being loaded on every page request

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

My database is showing 34915783 Bytes of data. That is around 33 MiB. It is recommend to have not more than 1MiB of autoload data. Since I am over 32MiB of recommended size, I should be worried.

You can also get more detailed info by using this SQL command

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);

Rank top biggest autoloaded data

You can see top 10 largest autoloaded data in detailed info above. Or you can execute this SQL command

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

Now you have the list of largest data in your autoload settings. What you need to do now is to identify which every of them belongs to.

Let us see some of them:

• _transient_dirsize_cache – Something within WordPress. There is actually a bug report regarding this row here. It seems to disable autoload for this option.
• fbrfg_favicon_non_interactive_api_request – Belong to Favicon by RealFaviconGenerator. There is also a ticket being opened here, but not response from the developer. Also seems safe to turn off.

Disable autoload for the rows

Now you have identified biggest data in your autoload. You have 2 choices to make, either disable autoload or take the extreme way by deleting them which might break your site.

Disable autoload

Can be achieved just by set autoload column in the row to no

Delete row

This is dangerous if you don’t know what you are doing. Only delete if you are sure that data is no longer used, like in case where a plugin was uninstalled, but the data stayed.

Just disable autoload if you are not sure.

Extras

In some cases, like mine, the issue is too many rows being extracted in the autoload process, not because of large data in certain rows. They added up to a total of 31MiB after the cleaning I have done.

One option would be deleting unused autoload-enabled rows from wp_options.

SELECT COUNT(*)
FROM wp_options
WHERE autoload = 'yes'
AND option_name LIKE '%transient%';

As you can see, I have over 11k rows of transients which is enabled for autoload. Since I am using object cache, these data are not required anymore, so I can delete them.

What are transients anyway? Transients are way of caching data in the database. The data will be given a custom name and expiry. They are unfortunately are not deleted automatically unless the site is trying to access them. You can clean them up with with plugins such as Transient Cleaner.

I used WP-Optimize since it has few extra features like optimize database, delete orphan data, and remove all transient data.

After cleaning the autoload, my autoloaded data went from 33 MiB to 0.4 MiB.

Considering donating if you found my post helpful 😊