How to Clean Up Autoloaded Options in WordPress

What is the wp_options table in WordPress?

The wp_options table is a part of the WordPress database that stores important settings and configurations for your website. It’s like a big container that holds lots of information, such as:

• Your website’s URL
• The theme you’re using
• Plugin settings
• Other preferences and options

How is the wp_options table organized?

Each piece of information in the wp_options table has four main parts:

1. Option ID: a unique identifier for each option
2. Option Name: the name of the option
3. Option Value: the value of the option
4. Autoload: a setting that determines whether the option should be loaded automatically with every page request

What is the autoload setting in wp_options?

Autoload is a setting that decides whether an option should be loaded into memory every time a page is loaded. If an option is marked as “autoload”, it will be loaded automatically, which can affect your website’s performance if there are too many autoload options.

Think of it like a big box of tools. If you need to use a tool every time you work on a project, it makes sense to keep it easily accessible. But if you have too many tools out, it can clutter your workspace and slow you down. Similarly, too many autoload options can slow down your website.

How can the autoloaded options negatively affect the performance of WordPress?

Because the autoloaded options get reloaded every time anyone makes a database request, having too many of these can slow your site to a crawl. WordPress recommends keeping the total size of all autoloaded options under 800kb, however in my experience keeping it under 500kb will work even better for performance.

What values can the autoload column have in the wp_options table?

On WordPress, the autoload column in the wp_options table can have several values, including 'yes', 'no', 'on', 'off', and 'auto'.

The value 'auto' is not exactly the same as 'yes'. While both values indicate that the option should be autoloaded, there is a subtle difference between them.

When the autoload value is set to 'yes', it means that the option will be loaded on every page load, regardless of whether it’s actually needed or not.

On the other hand, when the autoload value is set to 'auto', it means that WordPress will automatically determine whether to load the option or not, based on the context in which it’s being used. This is known as “lazy loading” or “on-demand loading”.

In other words, when autoload is set to 'auto', WordPress will only load the option when it’s actually needed, rather than loading it on every page load. This can help improve performance by reducing the amount of data that needs to be loaded.

So, while both 'yes' and 'auto' indicate that the option should be autoloaded, the value 'auto' is a more nuanced and performance-friendly approach.

It’s worth noting that the value 'auto' is not as widely used as 'yes' or 'no', and it’s not always clear when it’s being used. However, if you’re looking to optimize your WordPress site’s performance, using 'auto' instead of 'yes' might be a good strategy.

To give you a better idea, here’s a rough breakdown of the different autoload values and their meanings:

• 'yes': Load the option on every page load.
• 'no': Don’t load the option unless it’s explicitly requested.
• 'on': Same as 'yes'.
• 'off': Same as 'no'.
• 'auto': Load the option only when it’s actually needed (lazy loading).

How can I check the size of the autoloaded options in the wp_options table?

To check the size of the autoloaded options in your wp_options table in WordPress, fire up phpMyAdmin and use this following SQL command:

SELECT SUM(LENGTH(option_value)) as autoloaded_options_size FROM wp_options WHERE autoload='yes' or autoload='on';

How can I get an overview of all the autoloaded options sorted by size?

To get an overview of all the autoloaded options in your wp_options table, sorted by size, use the following SQL command:

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

How can I clean up the autoloaded options in the wp_options table in WordPress?

After you get the list of autoloaded options as shown above, there are generally two types of options that can be safely removed without breaking anything on your website:

1. Options from removed plugins or themes

Oftentimes, when you uninstall a plugin or theme, it leaves behind unnecessary autoloaded options. These can be safely deleted, as they’re no longer needed.

2. Options that don’t need to be loaded on every page

Some plugins set all their options to autoload, which can slow down your website. You can disable these options, as they’re not necessary on every page. However, keep in mind that some plugins might reset these options to autoload after an update, so it’s essential to check periodically.

Important: backup your database before making changes

Before making any changes, make sure to back up your database. While these changes shouldn’t cause any issues, it’s always better to be safe than sorry. Unexpected problems can arise, so it’s crucial to have a backup in case something goes wrong. The easiest way to take a backup of your database is using a plugin.

How can change the autoload values from ‘on’ to ‘yes’?

Having two different values for autoload making things more complicated. This is true both for database cleaner plugins such as Database Cleaner, or when using SQL queries in phpMyAdmin. For this reason, I recommend you change all ‘on’ values to ‘yes’.

You can change the values from 'on' to 'yes' in the autoload column of the wp_options table using phpMyAdmin. However, it’s essential to exercise caution when modifying the database directly.

1. Log in to your phpMyAdmin interface.
2. Select the WordPress database from the list on the left.
3. Click on the wp_options table.
4. Click on the “SQL” tab.
5. Run the following SQL query:

UPDATE wp_options SET autoload = 'yes' WHERE autoload = 'on';

What are transient autoloaded options and can I safely delete them?

Rows in the wp_options table starting with _transient are used by WordPress to store temporary data, known as transients. Transients are used to cache data that is expensive to compute or retrieve, such as API responses, database queries, or other types of data that don’t need to be stored permanently.

Deleting rows starting with _transient can be safe in most cases, but it depends on the specific use case and the plugins or themes you’re using. Here are some things to consider:

Why it’s usually safe to delete transients:

1. Transients are temporary: By definition, transients are meant to be temporary and can be safely deleted.
2. WordPress will recreate them: If a plugin or theme needs a transient, WordPress will recreate it automatically.
3. Deleting transients can free up space: If you have many transients, deleting them can help free up space in your database.

Why you might not want to delete transients:

1. Some plugins might rely on them: Some plugins might use transients to store critical data, such as authentication tokens or other sensitive information. Deleting these transients could cause issues with the plugin.
2. Transients might be used for caching: Some plugins or themes might use transients to cache data that is expensive to compute or retrieve. Deleting these transients could cause performance issues.

How to safely delete transients:

1. Use the wp transient delete command: If you’re using WP-CLI, you can use the wp transient delete command to delete transients safely.
2. Use a plugin: There are several plugins available that can help you manage and delete transients safely, such as Transient Manager or Delete Expired Transients.
3. Delete them manually: If you’re comfortable with SQL, you can delete transients manually using a query like this:

DELETE FROM wp_options WHERE option_name LIKE '_transient_%';

However, be careful when deleting transients manually, as you might accidentally delete something important.

Best practice:

If you’re unsure about deleting transients, it’s always best to err on the side of caution. You can try deleting transients using a plugin or WP-CLI, and then monitor your site for any issues. If you notice any problems, you can always restore the transients from a backup.

In summary, deleting rows starting with _transient can be safe in most cases, but it’s essential to exercise caution and consider the potential impact on your site.

Note: the options table isn’t always named wp_options

On standard WordPress installations, the options table is named wp_options. However, some hosting providers use another prefix, instead on ‘wp_’. In that case, your options table will be named: [your prefix]_options instead.

Do you need help cleaning up your WordPress database?

If you don’t feel confident cleaning up your WordPress database on your own, or if you simply don’t have the time, just reach out to me and I’ll do it for you for a small fee.