WordPress database cleanup: what is safe to do

What "a slow database" means

WordPress keeps posts, comments, users and settings in a database. Every page that is not served from a cache runs a number of queries. The WordPress Advanced Administration Handbook says WordPress repeats many queries on every request, which is why a persistent object cache and a page cache matter. See WordPress caching.

So the database matters in three situations:

• Pages that are not cached. Logged-in visitors, carts, checkouts, search results and the admin all hit the database.
• Data that every request reads. The autoloaded options are loaded at the start of every request, cached or not.
• Queries that search large tables. A plugin that searches or sorts a table with hundreds of thousands of rows can take a long time.

A cached page for a logged-out visitor skips most of this. That is why database cleanup is not the first thing to try, and why it often disappoints when it is.

Step 0: back up, and work on a copy

Take a full backup of the files and the database, and keep it until the site has run well for a while. Do the first clean-up on a staging copy. A cleanup that removes the wrong rows can break a plugin or a page, and the damage may not show on the page you test first.

Step 1: measure what is there

Autoloaded options. Open Tools, then Site Health, then the Status tab, and look for Autoloaded options. This test was added in WordPress 6.6. According to the core source, it reports a problem when the total size of the autoloaded options reaches 800,000 bytes. The Handbook gives the same guideline: keep autoloaded options under 800 KB. The screen also shows how many there are and their total size.

Server response time on pages that are not cached. Test a page that your cache skips, such as a search result page or a page while you are logged in. If it is much slower than a cached page, the work behind it is worth a look. The server response time guide shows how to measure it.

What the queries are. WordPress can record every query when SAVEQUERIES is set to true, which the handbook's wp-config page describes. Tools can show the function behind each query. Use this on a copy, because it adds work to every request.

Step 2: autoloaded options

An option is a saved setting, stored in the wp_options table. Each option has an autoload value. When it is on, WordPress loads the option at the start of every request. The Handbook says each plugin and theme defines its own options and which ones autoload, and that too many can slow down the site.

Core already guards against the worst case. Since WordPress 6.6, an option saved without an explicit autoload value is set not to autoload if its value is larger than 150,000 bytes. A plugin that sets the value itself can still override that.

Find the largest

Run this query in your host's database tool. Your table prefix may differ from wp_:

SELECT option_name, LENGTH(option_value) AS bytes, autoload
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto', 'auto-on')
ORDER BY bytes DESC
LIMIT 20;

These four values are the ones core treats as autoloading. The names usually start with the plugin or theme that wrote them. A large entry from a plugin you removed years ago is a common find.

What to do about a large one

1. Identify the owner. Search for the option name, or look at its prefix. Do not touch anything you cannot identify.
2. If the plugin is gone and the option is not needed, delete it, after the backup.
3. If the plugin is in use, ask whether the option has to load on every page. If it is only used on one screen, set it not to autoload. In code, wp_set_option_autoload( 'option_name', false ) does this. Since WordPress 6.7 the values yes and no are deprecated in favor of on and off.
4. Check the result. Reload the Site Health screen and the pages that use the plugin. If an option that every page needs is set to not autoload, WordPress has to fetch it separately, which can be slower. Change one option at a time and test.

Step 3: revisions

Every time you save a post, WordPress can keep the earlier version. By default it keeps all of them. The core default for WP_POST_REVISIONS is true, which means no limit, and the editor also autosaves every 60 seconds by default.

The handbook shows how to limit this in wp-config.php:

define( 'WP_POST_REVISIONS', 3 );

A number keeps that many revisions for each post, and false turns revisions off. The limit applies as posts are saved. It does not by itself delete the revisions that already exist. A cleanup plugin or a developer can remove the old ones in bulk.

Revisions make the posts table larger and backups slower. They matter less for the speed of a page that is cached. Keep a few, so that editors can still go back.

Step 4: trash, spam and drafts

WordPress empties the trash after 30 days by default. The setting is EMPTY_TRASH_DAYS, and it covers posts, pages, attachments and comments. Core also has scheduled tasks that delete old automatic drafts. You can empty the trash yourself, and clear spam comments, from the admin screens.

A large number of spam comments in a busy table can slow some queries. Clearing them is safe.

Step 5: transients

A transient is a value that a plugin saves for a limited time. The handbook explains where it goes. With a persistent object cache, transients can live in the cache. Without one, WordPress writes them to the database, typically in wp_options. Heavy use of transients without a persistent cache therefore adds reads and writes and makes the options table grow.

Two details matter for cleanup:

• Transients with an expiry are not loaded on every request. They add rows, but not autoload weight. Core has a scheduled task, delete_expired_transients, that removes the expired ones. With an external object cache, that function does nothing unless forced.
• Transients without an expiry are autoloaded. If you see many large ones in the autoload query above, they are part of the problem.

Transients are meant to be replaceable. Deleting expired ones is safe. Deleting all of them is usually safe too, since plugins rebuild them, but a few plugins keep short-lived state in a transient, and the site may be slower for a while. Do it at a quiet hour and test.

A persistent object cache moves transients out of the database and is the better fix for a site that uses many of them.

Step 6: tables and storage

• Tables left by removed plugins. Some plugins leave tables behind. Do not delete a table that you cannot identify. The plugin's own documentation or a developer can tell you.
• The storage engine. The handbook says to use InnoDB, which is the default for MySQL, and to convert older MyISAM tables to it. Your host can check this.
• Repair. The handbook describes an automatic repair setting, WP_ALLOW_REPAIR, for a corrupted database. It lets anyone reach the repair script without logging in, so turn it on only while you need it, and off again afterward.

Step 7: check the result

1. Reload the Autoloaded options test in Site Health and compare the size.
2. Test the uncached pages again and compare the server response time with your notes.
3. Look at the size and the time of your next backup.
4. Use the site as a visitor and as an editor. Check forms, the cart, search and the admin.

Do not expect a change on cached pages. The gain is on uncached pages, on the admin, and on backups.

Mistakes to avoid

• No backup. The most expensive mistake on this list.
• Cleanup plugins with every box ticked. Choose the categories one by one, and read what each does.
• Deleting what you cannot identify. If you do not know what an option or table is for, leave it.
• Turning off autoload for something every page needs. It can make the site slower.
• Fixing the database when the problem is hosting or caching. Look at WordPress caching and hosting first.

Check your own page

Run the free audit and look at Document request latency. If the server response line fails on a page that should be cached, fix the cache first. If cached pages are fast but the admin, the cart or search is slow, this guide is the next step.

Sources

• Optimization and Editing wp-config.php in the WordPress Advanced Administration Handbook: autoloaded options, revisions, trash, transients, InnoDB, repair and SAVEQUERIES.
• WordPress core source: option.php (autoload values, the 150,000 byte rule, wp_set_option_autoload, delete_expired_transients), class-wp-site-health.php (the autoloaded options test), default-constants.php (revisions, trash and autosave defaults) and default-filters.php (the scheduled cleanup tasks).