How to Optimize a WordPress Database: 7 Ways to Clean Up Bloat and Speed Up Queries

A WordPress site rarely slows down because of one big problem. It slows down because of thousands of small ones stacked inside MySQL: post revisions nobody will ever open, transients that expired months ago, metadata pointing to posts that no longer exist, spam comments, and tables still running on a storage engine designed in the 1990s.

This guide shows you exactly how to optimize a WordPress database, the manual way with SQL queries in phpMyAdmin, and the fast way with plugins or WP-CLI. Most importantly, it shows you how to measure the gain before and after, so you know whether the cleanup actually did something or whether your bottleneck is somewhere else.

Why WordPress databases get slow in the first place

WordPress uses a flexible schema. That flexibility is what makes plugins possible, and it is also what makes the database grow sideways:

• Revisions: every autosave creates a row in wp_posts, plus rows in wp_postmeta and wp_term_relationships. A 300 post blog can easily carry 6,000 revision rows.
• Transients: cached API responses stored in wp_options. WordPress only cleans expired transients occasionally, and plugins that create them with no expiry never clean them at all.
• Autoloaded options: every single page load reads the autoloaded rows of wp_options. If that payload reaches several megabytes, every request pays the price.
• Orphaned metadata: you delete a post, a plugin forgets to delete its meta rows. Multiply by every plugin you ever removed.
• MyISAM tables: table level locking, no crash recovery, terrible under concurrent writes.
• Missing indexes: WordPress core indexes meta_key alone, not meta_key + meta_value. WooCommerce and custom field queries suffer badly from this.

The good news: all six problems are fixable in an afternoon, and the results are measurable.

Step 0: benchmark first, or you are just guessing

Do not skip this part. If you cannot show a before and after number, you cannot tell whether your cleanup helped or whether the improvement came from a cache warming up.

Measure the size of each table

Run this in phpMyAdmin (SQL tab) or through wp db query:

SELECT table_name,
       engine,
       table_rows,
       ROUND(data_length/1024/1024, 2) AS data_mb,
       ROUND(index_length/1024/1024, 2) AS index_mb,
       ROUND(data_free/1024/1024, 2) AS free_mb
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC;

Save that output. The free_mb column is the space you will reclaim later, and the engine column tells you immediately whether you still have MyISAM tables.

With WP-CLI over SSH, the one liner equivalent is:

wp db size --tables --human-readable

Check your autoload payload

Since WordPress 6.6, the autoload column can contain yes, on, auto, auto-on, off, auto-off or no, so the old query using autoload = 'yes' now under reports. Use this instead:

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

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

Target: stay under 800 KB of autoloaded data. Above 1 MB you will feel it on every request.

Time a real query

Pick a query that matters on your site. On a WooCommerce store, a meta based product query is a perfect stress test:

SELECT SQL_NO_CACHE p.ID, p.post_title
FROM wp_posts p
INNER JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE p.post_type = 'product'
  AND p.post_status = 'publish'
  AND pm.meta_key = '_stock_status'
  AND pm.meta_value = 'instock'
ORDER BY p.post_date DESC
LIMIT 20;

phpMyAdmin displays the execution time under the results. Run it three times and keep the median. Prefix the same query with EXPLAIN to see whether MySQL is scanning the whole wp_postmeta table (look for type: ALL and a huge rows value). That is your baseline. There is a practical rundown of it online.

On the WordPress side, install Query Monitor and note two numbers on your slowest page: total queries and total query time.

Back up. Every single time.

All the queries below are destructive. There is no undo button in phpMyAdmin. See https://wordpress.org.

# Via WP-CLI
wp db export backup-before-cleanup.sql

# Or via mysqldump
mysqldump -u DBUSER -p DBNAME > backup-before-cleanup.sql

On Kelio Host, you can also trigger a snapshot from the control panel and run the whole procedure on a staging copy first. Do that if your site earns money.

Note on prefixes: every query in this article uses the default wp_ prefix. If your installation uses something else, replace it everywhere.

1. Delete post revisions (and cap them for good)

Count them first:

SELECT COUNT(*) FROM wp_posts WHERE post_type = 'revision';

Delete revisions together with their orphan children in one statement:

DELETE a, b, c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision';

Prefer to keep recent revisions? Delete only the ones older than 90 days:

DELETE FROM wp_posts
WHERE post_type = 'revision'
  AND post_modified < DATE_SUB(NOW(), INTERVAL 90 DAY);

Then stop the problem at the source by adding this to wp-config.php, above the line that says “That’s all, stop editing”:

define('WP_POST_REVISIONS', 5);
define('AUTOSAVE_INTERVAL', 120);
define('EMPTY_TRASH_DAYS', 7);

Five revisions per post is plenty for editorial safety. The autosave interval moves from 60 to 120 seconds, which halves the write pressure on busy editorial sites.

2. Purge expired transients and shrink wp_options

Transients are cached values with an expiry timestamp stored in a second row. When the timeout row disappears but the value row survives, you get a permanent orphan.

Count what you have:

SELECT COUNT(*) FROM wp_options
WHERE option_name LIKE '\_transient\_%'
   OR option_name LIKE '\_site\_transient\_%';

Delete expired timeouts:

DELETE FROM wp_options
WHERE option_name LIKE '\_transient\_timeout\_%'
  AND option_value < UNIX_TIMESTAMP();

DELETE FROM wp_options
WHERE option_name LIKE '\_site\_transient\_timeout\_%'
  AND option_value < UNIX_TIMESTAMP();

Then delete the value rows whose timeout no longer exists:

DELETE a
FROM wp_options a
LEFT JOIN wp_options b
  ON b.option_name = CONCAT('_transient_timeout_', SUBSTRING(a.option_name, 12))
WHERE a.option_name LIKE '\_transient\_%'
  AND a.option_name NOT LIKE '\_transient\_timeout\_%'
  AND b.option_name IS NULL;

The WP-CLI version does the same job in two commands and is safer because it goes through the WordPress API:

wp transient delete --expired
wp transient delete --all   # aggressive, forces caches to rebuild

Fix heavy autoloaded options

Go back to your top 20 autoloaded options list. If you find a 2 MB row belonging to a plugin you deleted last year, remove it. If it belongs to an active plugin but does not need to load on every request, flip it to off rather than deleting it:

UPDATE wp_options SET autoload = 'off' WHERE option_name = 'some_huge_option_name';

Common offenders: old backup plugin logs, redirection tables, dismissed notice arrays, cron option (cron can grow to several MB on broken installs), and abandoned page builder caches.

3. Clean orphaned metadata

This is where the biggest row counts usually hide. Always run the SELECT COUNT version first so you know what you are about to delete.

Orphaned post meta

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

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

Orphaned comment meta

DELETE cm FROM wp_commentmeta cm
LEFT JOIN wp_comments c ON c.comment_ID = cm.comment_id
WHERE c.comment_ID IS NULL;

Orphaned user meta

DELETE um FROM wp_usermeta um
LEFT JOIN wp_users u ON u.ID = um.user_id
WHERE u.ID IS NULL;

Orphaned term relationships

DELETE tr FROM wp_term_relationships tr
LEFT JOIN wp_posts p ON p.ID = tr.object_id
WHERE p.ID IS NULL;

Leftovers from removed plugins

List the meta keys by volume, then decide:

SELECT meta_key, COUNT(*) AS total
FROM wp_postmeta
GROUP BY meta_key
ORDER BY total DESC
LIMIT 40;

If you see 400,000 rows of _yoast_wpseo_something and you migrated to another SEO plugin two years ago, that is dead weight:

DELETE FROM wp_postmeta WHERE meta_key LIKE '_oembed_%';
DELETE FROM wp_postmeta WHERE meta_key = 'plugin_prefix_old_key';

Warning: never delete a meta key you cannot identify. Search the key name before running anything. Some innocent looking keys are used by page builders to store entire layouts.

4. Delete spam comments, trash and pingbacks

Spam that sits in the queue is still indexed, still scanned and still backed up.

-- See what is there
SELECT comment_approved, COUNT(*) FROM wp_comments GROUP BY comment_approved;

-- Spam
DELETE FROM wp_comments WHERE comment_approved = 'spam';

-- Trashed
DELETE FROM wp_comments WHERE comment_approved = 'trash';

-- Unapproved older than 60 days
DELETE FROM wp_comments
WHERE comment_approved = '0'
  AND comment_date < DATE_SUB(NOW(), INTERVAL 60 DAY);

-- Pingbacks and trackbacks (optional but usually pure noise)
DELETE FROM wp_comments WHERE comment_type IN ('pingback', 'trackback');

Then clear the meta left behind and fix the comment counters, which will otherwise display wrong numbers:

DELETE cm FROM wp_commentmeta cm
LEFT JOIN wp_comments c ON c.comment_ID = cm.comment_id
WHERE c.comment_ID IS NULL;

UPDATE wp_posts p
SET comment_count = (
  SELECT COUNT(*) FROM wp_comments c
  WHERE c.comment_post_ID = p.ID AND c.comment_approved = '1'
);

WP-CLI alternative that handles counters automatically:

wp comment delete $(wp comment list --status=spam --format=ids) --force

5. Clear auto drafts, trash and Action Scheduler logs

DELETE FROM wp_posts WHERE post_status = 'auto-draft';
DELETE FROM wp_posts WHERE post_status = 'trash';

-- Then clean the meta they left behind
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

On WooCommerce and any site using scheduled background jobs, the Action Scheduler tables are frequently the single largest tables in the whole database. Check them:

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

If you see hundreds of thousands of completed actions, purge the old ones (logs first, to respect the relationship):

DELETE l FROM wp_actionscheduler_logs l
INNER JOIN wp_actionscheduler_actions a ON a.action_id = l.action_id
WHERE a.status IN ('complete', 'failed', 'canceled')
  AND a.scheduled_date_gmt < DATE_SUB(NOW(), INTERVAL 30 DAY);

DELETE FROM wp_actionscheduler_actions
WHERE status IN ('complete', 'failed', 'canceled')
  AND scheduled_date_gmt < DATE_SUB(NOW(), INTERVAL 30 DAY);

Other tables worth inspecting on older stores: wp_wc_download_log, wp_woocommerce_sessions (expired sessions), analytics or statistics plugin tables, and 404 logs from redirection plugins.

DELETE FROM wp_woocommerce_sessions WHERE session_expiry < UNIX_TIMESTAMP();

6. Convert MyISAM tables to InnoDB

If any WordPress table still runs on MyISAM in 2026, converting it is probably the highest impact change in this whole article. InnoDB gives you row level locking instead of table level locking, real transactions, crash recovery and a proper buffer pool.

Find the culprits:

SELECT table_name, engine
FROM information_schema.TABLES
WHERE table_schema = DATABASE() AND engine = 'MyISAM';

Let MySQL write the conversion statements for you:

SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS conversion_sql
FROM information_schema.TABLES
WHERE table_schema = DATABASE() AND engine = 'MyISAM';

Copy the output, paste it back into the SQL tab, and run it. Or convert table by table:

ALTER TABLE wp_posts ENGINE=InnoDB;
ALTER TABLE wp_postmeta ENGINE=InnoDB;
ALTER TABLE wp_options ENGINE=InnoDB;

Things to check before you convert:

• Locking: the table is locked during conversion. On a 3 GB table this can take minutes. Do it during low traffic.
• Disk space: MySQL builds a copy, so you need free space roughly equal to the table size.
• FULLTEXT indexes: supported by InnoDB on MySQL 5.6+ and MariaDB 10.0+, so this is a non issue on any modern stack. Very old custom search tables are the only edge case.
• Custom plugin tables: convert them too, unless the plugin documentation explicitly requires MyISAM (extremely rare today).

Verify afterwards that engine reads InnoDB everywhere.

7. Add the indexes WordPress core does not ship

WordPress indexes meta_key in wp_postmeta but not the pair meta_key + meta_value. Any query filtering on a meta value (stock status, price, custom field, ACF relationship) therefore reads far more rows than necessary.

Check what you already have:

SHOW INDEX FROM wp_postmeta;
SHOW INDEX FROM wp_options;
SHOW INDEX FROM wp_usermeta;

Then add composite indexes with prefix lengths (full length would exceed key size limits on longtext columns):

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

ALTER TABLE wp_usermeta
  ADD INDEX meta_key_value (meta_key(32), meta_value(32));

ALTER TABLE wp_comments
  ADD INDEX comment_post_approved (comment_post_ID, comment_approved);

If your wp_options table does not already show an index on the autoload column (core added one in recent versions), add it:

ALTER TABLE wp_options ADD INDEX autoload_idx (autoload);

Now re run your EXPLAIN from Step 0. You want to see the new index name in the key column and a much smaller rows estimate. That single line is the proof your work paid off.

Do not over index. Every index costs write performance and disk space. Three or four well chosen indexes beat fifteen speculative ones. If you would rather not manage this by hand, the plugin Index WP MySQL For Speed applies a tested set of WordPress specific indexes and can revert them cleanly.

Rebuild tables and reclaim disk space

Deleting rows does not shrink the file on disk. You need a table rebuild to release the fragmented space you saw earlier in the free_mb column.

OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options, wp_comments, wp_commentmeta, wp_term_relationships;

On InnoDB, MySQL will print “Table does not support optimize, doing recreate + analyze instead”. That message is normal and the operation still works: it rebuilds the table and updates the statistics. The equivalent explicit command is:

ALTER TABLE wp_postmeta ENGINE=InnoDB;
ANALYZE TABLE wp_postmeta;

Or simply, from the command line:

wp db optimize

In phpMyAdmin you can also tick all tables in the Structure view and choose Optimize table from the dropdown. Same result, no SQL needed.

The plugin route: same results, far less risk

Not everyone wants to run DELETE statements on a production store, and that is a perfectly reasonable position. Plugins do 80 percent of the work above with a checkbox interface.

Plugin	Best for	Covers	Price
WP-Optimize	All in one cleanup plus scheduling	Revisions, transients, spam, trash, table optimization, page cache, image compression	Free / Premium
Advanced Database Cleaner	Identifying orphaned data by plugin	Orphaned options, meta, unused tables, cron jobs	Free / Pro
WP-Sweep	Safe cleanup via WordPress APIs	Revisions, orphans, duplicate meta, term relationships	Free
Index WP MySQL For Speed	Indexing only, with revert option	Adds and monitors WordPress specific indexes	Free
WP Rocket	Sites already using it for caching	Basic database tab: revisions, drafts, spam, transients, optimize tables	Premium

What plugins will not do for you

• Convert MyISAM tables to InnoDB (you still need the ALTER TABLE statements).
• Add composite indexes, except Index WP MySQL For Speed.
• Judge whether a 900,000 row meta key belongs to a plugin you still use.
• Trim Action Scheduler tables aggressively.

The realistic best practice is hybrid: plugin for the weekly routine, SQL and WP-CLI for the deep annual cleanup.

Before and after: what the numbers look like

Below are the results from a WooCommerce store we migrated to Kelio Host, running about 2,400 products and six years of order history. Every step in this article was applied in order, on a staging copy first.

Metric	Before	After	Change
Total database size	1.94 GB	412 MB	-79%
wp_posts rows	186,400	31,900	-83%
wp_postmeta rows	2,140,000	688,000	-68%
wp_options rows	94,300	8,100	-91%
Autoloaded data	5.6 MB	0.61 MB	-89%
Product meta query (median of 3)	1,940 ms	88 ms	22x faster
Admin “All Products” screen	4.1 s	0.9 s	-78%
Uncached homepage TTFB	1.18 s	0.42 s	-64%
Queries on homepage (Query Monitor)	312	147	-53%

Two observations from that run. First, the indexes produced the query time gain, not the row deletions. Deleting rows shrank the backup and made the admin lighter, but the 22x improvement came from step 7. Second, the autoload cleanup produced most of the TTFB gain, because it runs on literally every uncached request.

Your mileage will differ. That is exactly why you benchmark.

Keep it clean: a realistic maintenance schedule

1. Weekly (automated): WP-Optimize scheduled task removing revisions older than 30 days, spam, trash and expired transients.
2. Monthly (5 minutes): check database size and the top 20 autoloaded options. Investigate anything that grew.
3. Quarterly (30 minutes): orphan metadata sweep, Action Scheduler purge, table rebuild with wp db optimize.
4. After every plugin removal: search for its meta keys and options and delete them while you still remember the prefix.
5. Yearly: re run EXPLAIN on your slowest queries and confirm your indexes are still being used.

Do not forget the layer above the database

Database cleanup has a ceiling. Once your tables are lean and indexed, the next wins come from infrastructure:

• Persistent object cache with Redis, which keeps repeated queries out of MySQL entirely. This is often a bigger win than everything above combined on a busy site.
• Modern MySQL or MariaDB with a buffer pool large enough to hold your working set.
• NVMe storage and current PHP, since a fast query still needs a fast disk and a fast interpreter.

All Kelio Host plans ship with NVMe storage, current PHP releases and Redis available in one click, so the database work you do here is not undone by the stack underneath. The team at pressidium.com reached a similar conclusion.

FAQ

How can I clean up my WordPress database?

Back up first, then remove post revisions, expired transients, orphaned post and comment metadata, spam and trashed comments, auto drafts and old scheduled action logs. Finish with a table rebuild using OPTIMIZE TABLE or wp db optimize. You can do it with SQL in phpMyAdmin, with WP-CLI, or with a plugin such as WP-Optimize or WP-Sweep.

How do you optimize database performance beyond cleaning?

Cleaning removes weight, indexing removes work. Convert every table to InnoDB, add a composite index on meta_key and meta_value in wp_postmeta, keep autoloaded options under roughly 800 KB, and add a persistent object cache. Then use EXPLAIN to confirm your slowest queries actually use an index.

Is it safe to delete post revisions?

Yes, provided you have a backup and you accept losing the ability to roll back to an older version of a post. Deleting revisions never affects the published content. If you want a safety margin, delete only revisions older than 90 days and set WP_POST_REVISIONS to 5.

Is WP-Optimize good?

It is one of the most reliable free options for routine cleanup and scheduling, and it also handles caching and image compression. What it does not do is convert storage engines or add custom indexes, so treat it as your weekly maintenance tool rather than your complete optimization strategy.

Does OPTIMIZE TABLE work with InnoDB?

Yes, but indirectly. MySQL converts it into a table recreate plus an analyze, which defragments the table and refreshes index statistics. The warning message you see is expected behaviour, not an error.

How often should I optimize my WordPress database?

Automated light cleanup weekly, a manual review monthly, and a deep cleanup with orphan removal and table rebuild every quarter. High volume stores and membership sites benefit from a shorter cycle because they generate transients and scheduled actions much faster.

Will optimizing the database improve my Core Web Vitals?

It mainly improves TTFB on uncached requests, which feeds into LCP. If your pages are fully served from a page cache, visitors will notice little, but logged in users, the admin area, search, filtered archives and checkout pages will all be noticeably faster.

Is WordPress outdated in 2026?

No. It still powers a large share of the web and its recent releases have improved performance directly at the database level, including changes to autoloaded options and better default indexing. What is outdated is running WordPress on MyISAM tables, an old PHP branch or a database nobody has maintained in five years.

Next step: take the ten minutes to record your baseline numbers before you touch anything. Optimization without measurement is just housekeeping. With measurement, it becomes a repeatable process you can apply to every site you manage.