How to Clean up Your wp_options Table’s Autoload, Transient and Session Data

Your autoload data is often an area which gets overlooked when it comes to your general website and database speed. Autoload data can get left behind from old plugins that are no longer used, and is often the reason for slow database query times, especially if you have a large database, or an older website that has changed over the years.

Here is the process I use for checking a websites autoload data, and cleaning up old unused data to speed up websites and databases:

What is the wp_options table?

The wp_options table stores lots of different crucial website data, including the site URL, admin email and settings for themes and plugins including which are activated.

Each row in the table contains an"autoload" field which is set to either yes or no. If set to yes, this data is loaded on every page throughout the website, which can cause websites to slow down.

I’d recommend keeping autoload data below 1MB (if possible), and I’d consider anything above 5MB an issue which needs to be addressed.

How do I check autoload data size?

You can check your autoload data in phpMyAdmin which you’ll find in your hosting control panel.

Once you’ve logged into phpMyAdmin, click on your database and then the SQL query tab at the top – then enter this query:

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)

You may need to change the table prefix if you’re using anything other than wp_, but this query will display the autoload data size, how many autoload data entries you have, and the top 10 ordered by size. You can change this number (LIMIT 10) to any number you prefer to display more or less entries.

How do I cleanup autoload data?

Once you’ve checked your autoload data with the above query, you’ll have a list of the heaviest autoload options.

It is important that you don’t just start deleting the biggest options, because some of these will probably be required for the functionality of your website, and deleting them can cause issues.

Occasionally you’ll see old plugins which you have previously removed, but they’ve left this autoload data behind that you no longer need. One of the most popular plugins I’ve noticed which does this is"Jetpack".

Once you’ve found these old plugins, you can search for the data with the following query, and drop those rows.

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

How do I cleanup transients and sessions in the wp_options table?

Transients are a way of temporarily storing data to improve overall performance. As they are temporary, they’re usually given an expiration date/time and will be removed automatically – but this is not always the case.

I frequently come across databases with tens of thousands of old transient records which have not been deleted.

As transients are autoloaded by default, we can use the same script we used previously to remove all transient data from the wp_options table. This is generally safe to use, as data which is needed will be rebuilt and automatically put back in.

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

Another common issue is built-up session data, which usually occurs when cronjobs are out of sync and not firing properly, the session data they leave behind doesn’t get cleaned up effectively.

Like transients, in most cases, these are all safe to remove. This is the sort of thing my care plans handle each month.

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