How to Fix WordPress Database Bloat and Slow Queries (Autoload, Transients, Revisions)

Site Health warns “Autoloaded options could affect performance”, Query Monitor shows a growing list of slow queries, or your host tells you the database is several gigabytes for a site with a few hundred posts. Pages that are not cached take seconds to build, and wp-admin feels heavy everywhere.

Under the hood, WordPress asks the database for data on every uncached request. Some of that data is loaded on every single page whether it is needed or not, and some tables grow without limit because plugins write to them and never clean up. This guide shows you how to measure each kind of bloat precisely, decide what is safe to remove, and clean it up without breaking anything. It is written for site owners comfortable with a terminal and for developers who maintain client sites.

What is happening

A WordPress database is a set of tables. A few of them cause most performance trouble:

Table	What grows	Why it hurts
wp_options	Plugin settings, caches, transients	Rows marked to autoload are read into memory on every request. Megabytes here slow every page.
wp_posts	Revisions, auto-drafts, trashed items	Larger tables mean slower list screens, searches and backups.
wp_postmeta	Custom fields, builder data, orphaned rows	Meta queries filter on meta_value, which has no index by default.
wp_actionscheduler_actions and _logs	Background job history	Can reach millions of rows when jobs fail or are never cleaned.
Plugin tables	Logs, statistics, sessions, 404 trackers	Security and analytics plugins can write a row on every visit.

Autoload deserves special attention. When WordPress starts, it runs one query that loads every option flagged for autoload. Since WordPress 6.6 the autoload column can hold several values, and you need to know them to measure correctly:

Value	Loaded on every request?	Meaning
yes or on	Yes	Explicitly set to autoload (yes is the pre-6.6 form).
auto-on	Yes	WordPress decided dynamically that it should autoload.
auto	Yes, in current versions	Added without an explicit choice; the default behavior applies.
no, off, auto-off	No	Loaded only when requested. Since 6.6, options larger than 150 KB added without an explicit choice get auto-off.

Site Health raises its warning when the autoloaded total passes 800 KB. That is a sensible line: under it you are fine, well over it you are paying a cost on every uncached request.

Before you start

• Access: SSH with WP-CLI, or phpMyAdmin or Adminer from your hosting panel. SQL below can run either through wp db query "..." in a terminal or pasted into phpMyAdmin’s SQL tab.
• Table prefix: the queries use the default wp_ prefix. Find yours as $table_prefix in wp-config.php, or run wp db prefix, and replace wp_ everywhere if it differs.
• Running commands: SSH into the server and cd to the folder with wp-config.php. Use wp, or wp-cli on hosts that name it that way.
• Backup first, always. Every delete in this guide is permanent. Run the export below before anything else.

wp db export ~/db-backup-YYYY-MM-DD.sql
ls -lh ~/db-backup-YYYY-MM-DD.sql

Placeholder: YYYY-MM-DD is today’s date. ~ puts the file in your home folder, outside the public web root, so it cannot be downloaded by visitors. Good output: Success: Exported to ... and a file size that roughly matches your database size. Bad output: an error about access or disk space. Fix that before going further.

Step 1: Find where the size actually is

SELECT table_name,
  ROUND((data_length + index_length) / 1048576, 1) AS size_mb,
  table_rows, engine
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 15;

Where: phpMyAdmin SQL tab, or wrap it in wp db query "..." on the server. DATABASE() means the current database, so there is nothing to replace. table_rows is an estimate for InnoDB tables, close enough for this purpose. WP-CLI’s wp db size --tables gives a similar list.

Reading it: on a typical content site, wp_posts and wp_postmeta lead. Red flags: a log or statistics table bigger than your posts, Action Scheduler tables in the hundreds of megabytes, wp_options over a few tens of megabytes, or any table with engine MyISAM (see the Advanced section).

Step 2: Autoloaded options

Measure the total

SELECT COUNT(*) AS options,
  ROUND(SUM(LENGTH(option_value)) / 1024) AS autoload_kb
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto');

Good: under 800 KB, ideally a few hundred. Bad: several megabytes. On an older WordPress (before 6.6) only yes exists, and the query still works.

A WP-CLI equivalent is wp option list --autoload=on --format=total_bytes. Be aware that WP-CLI’s --autoload=on filter matches only the values on and yes. It does not count auto-on or auto, so on WordPress 6.6 and later it can under-report. The SQL above is the complete figure.

Find the largest offenders

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

Or with WP-CLI (same caveat about values):

wp option list --autoload=on --fields=option_name,size_bytes | sort -n -k 2 | tail -20

--fields=option_name,size_bytes adds the size column. sort -n -k 2 sorts numerically by the second column and tail -20 shows the 20 largest.

Reading it: the option name prefix almost always names the plugin (wpseo_, elementor_, woocommerce_, and so on). Common culprits are options belonging to plugins you deleted long ago, cached data stored as an option (a remote feed or a CSS bundle), and transients that were mistakenly set to autoload. Core options such as rewrite_rules, cron and active_plugins must stay, though a huge cron option is itself a sign of a scheduling problem.

Fix it

• Option from a plugin that is no longer installed: check that the plugin is really gone, then wp option delete OPTION_NAME.
• Option the plugin needs, but not on every page: stop it autoloading with wp option set-autoload OPTION_NAME off. Accepted values are on, off, yes and no. Load a few pages and the relevant admin screen afterward to confirm nothing broke; the plugin will still read it when it needs it, just with one extra query.
• Option that keeps growing back: the plugin writes it on purpose. Look for a setting to disable that cache or log, or report it to the developer.

Placeholder: OPTION_NAME is the exact name from the query results.

Step 3: Expired transients

Transients are temporary cached values with an expiry time. Each one is two rows in wp_options: _transient_NAME and _transient_timeout_NAME. WordPress removes expired ones lazily and during a daily cleanup, but plugins that create unique transient names per visitor or per URL can create them faster than they are cleared. If your site uses a persistent object cache such as Redis, transients live there instead and this table stays small.

SELECT COUNT(*) AS expired
FROM wp_options
WHERE option_name LIKE '\_transient\_timeout\_%'
  AND option_value < UNIX_TIMESTAMP();

The backslashes make the underscores literal; in SQL LIKE, a bare underscore matches any single character. UNIX_TIMESTAMP() is the current time, so this counts timeouts in the past. For multisite network transients, run it again with '\_site\_transient\_timeout\_%'. Good: a few dozen. Bad: thousands or more.

wp transient delete --expired

This safely deletes expired transients through WordPress itself, including both rows of each pair. A transient is by definition disposable, so deleting all of them with wp transient delete --all is also safe, though some pages will be slower once while caches rebuild.

Step 4: Post revisions and auto-drafts

SELECT post_type, post_status, COUNT(*) AS total
FROM wp_posts
GROUP BY post_type, post_status
ORDER BY total DESC
LIMIT 20;

This shows what fills wp_posts. If revision rows outnumber your real posts and pages many times over, revisions are your bloat. Page builders that save full layouts on every update make this worse, because each revision copies its meta too.

Limit future revisions in wp-config.php, above the “stop editing” line:

define( 'WP_POST_REVISIONS', 10 );

The number is the maximum revisions kept per post; false disables them entirely, which is not recommended because revisions are your undo history. WordPress trims old revisions for a post the next time that post is saved; it does not clean existing ones on its own.

To remove existing revisions through WordPress (which also removes their meta and term links), run on the server:

wp post list --post_type=revision --post_status=inherit --format=count
wp post list --post_type=revision --post_status=inherit --format=ids | xargs -n 200 wp post delete --force

The first command is the preview: it prints how many will go. The second lists their IDs and passes them to wp post delete in groups of 200 (xargs -n 200) so the command line never gets too long. --force deletes permanently instead of using the trash. Revisions use the status inherit. Good output: a stream of Success: Deleted post ... lines. Avoid raw DELETE FROM wp_posts WHERE post_type = 'revision': it leaves their postmeta behind as orphans.

Step 5: Orphaned postmeta

Orphaned meta rows point to a post that no longer exists, usually left by direct SQL deletes or badly written plugins.

SELECT COUNT(*) AS orphans
FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

The LEFT JOIN keeps every meta row and fills the post columns with NULL when no matching post exists, so p.ID IS NULL finds the orphans. Good: zero or a small number. Bad: tens of thousands. After your backup, delete them:

DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

Run the count again afterward; it should return 0. To see which keys dominate your meta table, which often names a plugin, run SELECT meta_key, COUNT(*) AS n FROM wp_postmeta GROUP BY meta_key ORDER BY n DESC LIMIT 20;.

Step 6: Action Scheduler tables

SELECT status, COUNT(*) AS n
FROM wp_actionscheduler_actions
GROUP BY status;

Statuses are pending, in-progress, complete, failed and canceled. Action Scheduler normally purges completed and canceled actions older than 31 days, but only if its queue runner is running. Large failed counts mean a plugin keeps erroring; fix that first or the table refills. Clean old finished actions through the tool itself, which also removes their log rows:

wp action-scheduler clean --status=complete,canceled,failed --before='30 days ago' --batch-size=1000

--status takes a comma separated list with no spaces, --before any date string PHP understands, and --batch-size the rows deleted per batch (default 20, which is slow on huge tables). Diagnosing why the queue backs up is covered in the slow wp-admin guide (FG-33).

Step 7: Use the slow query log and EXPLAIN

Turn on the slow query log

If you run your own server or VPS, MySQL and MariaDB can record every query slower than a threshold. As a database admin user:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SHOW VARIABLES LIKE 'slow_query_log_file';

long_query_time is in seconds and applies to new connections. The last line shows where the log is written. These settings reset when the database restarts; to make them permanent, put slow_query_log = 1 and long_query_time = 1 in the [mysqld] section of the server’s config file. On shared hosting you do not have permission for this; use Query Monitor’s Slow Queries panel instead, or ask your host for the log.

mysqldumpslow -s t -t 10 SLOW_LOG_PATH

Placeholder: SLOW_LOG_PATH is the file shown by the query above. -s t sorts by total time and -t 10 shows the top ten query patterns, with numbers abstracted so similar queries group together. A pattern with a high count and high total time is your target.

Read EXPLAIN

Put EXPLAIN in front of a slow SELECT to see how the database plans to run it:

EXPLAIN SELECT post_id FROM wp_postmeta
WHERE meta_key = 'META_KEY' AND meta_value = 'META_VALUE';

Placeholders: use a real key and value from the slow query you found. The columns that matter:

• type: ALL means a full table scan, the worst case. ref, eq_ref or const mean an index is used.
• key: the index actually chosen. NULL means none.
• rows: how many rows the database expects to examine. Hundreds of thousands for a query that returns one row is a problem.
• Extra: Using filesort or Using temporary on large row counts signal expensive sorting.

For the query above on a stock install you will typically see the meta_key index used but a large rows estimate, because meta_value itself is not indexed. That is the most common missing index in WordPress.

Advanced: Indexes, the InnoDB buffer pool and storage engines

Adding indexes

WordPress core indexes wp_postmeta on post_id and a prefix of meta_key. Sites that filter heavily on meta values, such as WooCommerce stores or directory sites, can benefit from a composite index. The free plugin Index WP MySQL For Speed adds a tested set of keys and can revert them. If you add one by hand, do it on staging, after a backup, at a quiet time:

ALTER TABLE wp_postmeta ADD INDEX meta_key_value (meta_key(191), meta_value(100));

The numbers are prefix lengths in characters, needed because meta_value is a long text column. Re-run your EXPLAIN: key should now show meta_key_value and rows should drop sharply. Index changes on big tables can take minutes and use disk space; check free space first.

Buffer pool

InnoDB keeps table and index pages in memory in the buffer pool. If your working data does not fit, queries read from disk and slow down. On a VPS you control:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Compare Innodb_buffer_pool_reads (reads that had to go to disk) with Innodb_buffer_pool_read_requests (all logical reads). The first should be a tiny fraction of the second. If it is not, and the pool is smaller than your total data plus index size from Step 1, raise innodb_buffer_pool_size in the server config. The MySQL default is 128 MB, which is small for a busy store. On a server that also runs PHP, leave enough memory for PHP workers.

MyISAM tables

Old sites sometimes still have MyISAM tables, which lock the whole table on every write. Convert them after a backup with ALTER TABLE TABLE_NAME ENGINE=InnoDB;, where TABLE_NAME comes from the engine column in Step 1.

Reclaiming disk space

Deleting rows does not shrink InnoDB files by itself. OPTIMIZE TABLE wp_options; rebuilds a table and returns space when each table has its own file (the modern default). It needs free disk roughly equal to the table size, and it takes time on large tables.

A safe cleanup order

1. Export a backup and confirm the file size.
2. Run each count query and write the numbers down.
3. Clean expired transients, then revisions, then orphaned meta, then Action Scheduler history.
4. Fix the top autoloaded options one at a time, loading the site after each change.
5. Optimize the tables you trimmed most.
6. Re-run the measurements and compare with your notes. Load the home page, a product or post, checkout if you have one, and wp-admin.

When to contact your host

Open a ticket if you need the slow query log, a buffer pool change, or help with a table too large to alter on shared hosting. Send the table size list from Step 1, your autoload total, the slow queries with their EXPLAIN output, UTC times of slow page loads, and what you have already cleaned. Mention any recent imports or plugin changes.

Useful outside checks are on our diagnostic tools page. If the database is not slow but refusing connections entirely, that is a different emergency: start at the Emergency Room.