Most guides that tell you how to optimize a WordPress database stop at “delete your post revisions and run OPTIMIZE TABLE”. That is fine advice, but on the sites we audit it usually buys back disk space, not speed. The steps that actually cut Time To First Byte (TTFB) are structural: converting leftover MyISAM tables to InnoDB, fixing an oversized autoload payload, and adding the indexes WordPress core never shipped for heavy meta_query workloads.
This walkthrough follows one real cleanup on a WooCommerce store, with measurements taken after every single step. By the end you will know exactly which tasks are worth an afternoon and which ones are mostly housekeeping.
The test site: what we started with
All numbers below come from a single production store cloned to an identical staging container (4 vCPU, 8 GB RAM, MariaDB 10.11, PHP 8.3, no page cache so database work stays visible).
| Metric | Starting value |
|---|---|
| Total database size | 1.24 GB |
| wp_posts rows | 52,900 (38,100 of them revisions) |
| wp_postmeta rows | 891,400 |
| Expired transient rows in wp_options | 61,700 |
| Autoloaded options payload | 2.41 MB |
| Spam + trashed comments | 24,300 |
| MyISAM tables | 5 (including wp_postmeta and wp_comments) |
| Homepage DB time / queries | 780 ms / 214 queries |
| Homepage TTFB (median of 20) | 1,410 ms |
Step 0: measure before you touch anything
If you skip this part you will never know whether the cleanup worked. Three tools are enough.
1. TTFB from the command line
for i in $(seq 1 20); do curl -o /dev/null -s -w "%{time_starttransfer}\n" https://example.com/; doneTake the median, not the average, and run it from the same network each time.
2. Query Monitor for per-page database cost
Install Query Monitor and note the total query time, the query count, and the slowest single query on your homepage, a category archive, a product page, and wp-admin. Those four URLs cover almost every pattern.
3. The slow query log
If you can edit MySQL configuration, enable it for the session and set the threshold low so you catch everything worth fixing:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.2;
SET GLOBAL log_queries_not_using_indexes = 'ON';Then check table sizes and engines in one shot:
SELECT table_name, engine, table_rows,
ROUND((data_length + index_length)/1024/1024, 1) AS mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC;Back up first, always
Every SQL statement in this article is destructive. Take a dump you can actually restore:
wp db export backup-before-cleanup.sql
# or
mysqldump -u user -p --single-transaction dbname > backup-before-cleanup.sqlWork on staging if the site earns money. Restoring a 1 GB dump under pressure is not a fun way to spend a Friday. Related reading: What is Database Optimization in WordPress.
Step 1: delete post revisions (space win, tiny speed win)
Revisions live in wp_posts and drag their own wp_postmeta rows along. Deleting 38,100 of them cut 150 MB of storage. It barely touched page speed because revisions are excluded from front end queries by post type anyway.
The safe way, using WP-CLI so hooks fire and meta gets cleaned:
wp post delete $(wp post list --post_type=revision --format=ids) --forceIf the list is too long for your shell, batch it:
wp post list --post_type=revision --format=ids | tr ' ' '\n' | xargs -n 200 wp post delete --forceThen stop the regrowth in wp-config.php:
define( 'WP_POST_REVISIONS', 5 );
define( 'EMPTY_TRASH_DAYS', 7 );
define( 'AUTOSAVE_INTERVAL', 120 );Result: 1.24 GB to 1.09 GB, DB time 780 ms to 762 ms, TTFB 1,410 ms to 1,385 ms.
Step 2: expired transients and the autoload payload (first real win)
This is where most sites hide their biggest cheap gain. Every page load reads all autoloaded options into memory. When that payload passes roughly 1 MB, PHP spends real time unserializing garbage on every request.
Check your payload first:
SELECT ROUND(SUM(LENGTH(option_value))/1024/1024, 2) AS autoload_mb, COUNT(*) AS rows_count
FROM wp_options WHERE autoload = 'yes';
SELECT option_name, ROUND(LENGTH(option_value)/1024, 1) AS kb
FROM wp_options WHERE autoload = 'yes'
ORDER BY LENGTH(option_value) DESC LIMIT 25;Clear expired transients with WP-CLI (it handles site transients too):
wp transient delete --expired
wp transient delete --expired --networkIf a plugin left orphaned transients with no timeout row, remove them in SQL:
DELETE a, b 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_value < UNIX_TIMESTAMP();Then deal with the top offenders from the size query above. Typical culprits: abandoned plugin logs, license caches, feed caches, and “stats” rows from plugins you removed years ago. For rows you must keep but do not need on every request, flip autoload off instead of deleting:
UPDATE wp_options SET autoload = 'no' WHERE option_name = 'some_huge_plugin_log';Our payload dropped from 2.41 MB to 310 KB.
Result: 1.09 GB to 1.02 GB, DB time 762 ms to 611 ms, TTFB 1,385 ms to 1,180 ms. That is a 15 percent TTFB gain from an hour of work.
Step 3: orphaned metadata and term relationships
Deleted posts, users and comments leave meta rows behind whenever a plugin deletes records directly in SQL. Those rows inflate indexes and slow every JOIN.
-- Orphaned post meta
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;Run each as a SELECT COUNT(*) first so you know how many rows you are about to remove. On multisite, repeat per blog prefix.
Also worth pruning: leftover meta keys from uninstalled plugins. List them before deleting anything.
SELECT meta_key, COUNT(*) AS c FROM wp_postmeta
GROUP BY meta_key ORDER BY c DESC LIMIT 40;We removed 187,000 orphaned rows and 94,000 rows belonging to three dead plugins.
Result: 1.02 GB to 0.94 GB, DB time 611 ms to 570 ms, TTFB 1,180 ms to 1,135 ms.
Step 4: spam, trashed and unapproved comments
Comment tables rarely affect the front end, but they punish wp-admin and any “recent comments” widget.
wp comment delete $(wp comment list --status=spam --format=ids) --force
wp comment delete $(wp comment list --status=trash --format=ids) --forcePure SQL alternative if WP-CLI is not available:
DELETE FROM wp_comments WHERE comment_approved IN ('spam', 'trash');
DELETE cm FROM wp_commentmeta cm
LEFT JOIN wp_comments c ON c.comment_ID = cm.comment_id
WHERE c.comment_ID IS NULL;
DELETE FROM wp_comments WHERE comment_type = 'pingback';Result: 0.94 GB to 0.91 GB, homepage DB time 570 ms to 558 ms, TTFB 1,135 ms to 1,120 ms. The admin comments screen went from 3.1 s to 0.6 s, which matters to the people who run the store even though visitors never notice.
Step 5: convert MyISAM tables to InnoDB (the biggest single jump)
MyISAM uses table level locking. One slow write blocks every read on that table. On a store that receives orders while Googlebot crawls archives, this is the single worst thing in your schema. InnoDB gives you row level locking, crash recovery, proper transactions, and a real buffer pool.
Find the offenders:
SELECT table_name, engine FROM information_schema.tables
WHERE table_schema = DATABASE() AND engine != 'InnoDB';Convert one table at a time, largest last, during low traffic:
ALTER TABLE wp_comments ENGINE = InnoDB;
ALTER TABLE wp_commentmeta ENGINE = InnoDB;
ALTER TABLE wp_postmeta ENGINE = InnoDB;Or in one loop with WP-CLI:
wp db query "SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE() AND engine = 'MyISAM';" --skip-column-names | while read t; do wp db query "ALTER TABLE $t ENGINE=InnoDB;"; doneTwo things to check before you convert
- FULLTEXT indexes. Older MyISAM tables from search plugins may rely on them. InnoDB supports FULLTEXT on modern MySQL and MariaDB, but verify the index survived the conversion with
SHOW INDEX FROM table_name; - Buffer pool size. InnoDB only shines if it has memory. Ask your host to set
innodb_buffer_pool_sizeto roughly 60 to 70 percent of the RAM dedicated to MySQL. On our container we moved it from 128 MB to 2 GB, which is half of this step’s benefit.
Result: size 0.91 GB to 0.93 GB (InnoDB stores indexes differently), DB time 558 ms to 331 ms, TTFB 1,120 ms to 690 ms. That is the largest drop in the whole exercise and it required zero content deletion.
Step 6: add the indexes WordPress does not ship
Core indexes wp_postmeta on meta_key and post_id, but any query that filters by meta_value (price ranges, custom sorting, ACF filters, stock status) scans large numbers of rows. Find those queries in your slow log or Query Monitor, then run EXPLAIN:
EXPLAIN SELECT p.ID FROM wp_posts p
INNER JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE pm.meta_key = '_price' AND pm.meta_value BETWEEN 10 AND 50
AND p.post_type = 'product' AND p.post_status = 'publish';If you see type: ref with tens of thousands of rows examined, a composite prefix index helps:
ALTER TABLE wp_postmeta ADD INDEX meta_key_value (meta_key(32), meta_value(64));
ALTER TABLE wp_usermeta ADD INDEX meta_key_value (meta_key(32), meta_value(64));
ALTER TABLE wp_options ADD INDEX autoload_name (autoload, option_name(100));
ANALYZE TABLE wp_postmeta, wp_usermeta, wp_options;Here is what those three indexes did to the four slowest queries on the site:
| Query | Before | After | Rows examined |
|---|---|---|---|
| Filtered product archive (price + stock meta_query) | 1,840 ms | 41 ms | 412,000 to 1,900 |
| Autoload options read | 96 ms | 9 ms | 7,100 to 640 |
| Customer lookup by billing email | 610 ms | 18 ms | 88,000 to 3 |
| Related posts by shared terms | 270 ms | 52 ms | 140,000 to 8,400 |
Do not index everything
Each index costs write performance and disk. Add one, measure, keep it only if it earns its place. And if you use WooCommerce, make sure its own lookup tables (product attributes, order stats, and the High Performance Order Storage tables) are populated and in use, because they exist precisely to avoid wp_postmeta scans. Drop an index with ALTER TABLE wp_postmeta DROP INDEX meta_key_value; if it did not help. You will find the same thinking at a studio with a good track record on it.
Result: DB time 331 ms to 142 ms, TTFB 690 ms to 430 ms.
Step 7: defragment, then keep it clean
Now, and only now, run the housekeeping everyone else starts with:
wp db optimize
# InnoDB equivalent of a rebuild for a single table:
ALTER TABLE wp_postmeta ENGINE = InnoDB;
ANALYZE TABLE wp_postmeta;Note that OPTIMIZE TABLE on InnoDB is mapped to a full table rebuild plus ANALYZE. It reclaims space after big deletions, so it belongs at the end of the process, not the beginning. Doing it monthly on a healthy database is pointless. There’s a good explainer over at wp-rocket.me.
To stop the bloat coming back:
- Add a persistent object cache (Redis or Memcached). On our test site this alone took TTFB from 415 ms to 210 ms because repeated option and meta reads stopped hitting MySQL at all.
- Replace WP-Cron with a real system cron so scheduled events do not pile up in
wp_options:define( 'DISABLE_WP_CRON', true );plus a crontab entry callingwp cron event run --due-nowevery 5 minutes. - Audit
wp_optionsautoload size quarterly. It is the metric that degrades fastest. - Uninstall plugins properly, and check afterwards whether they left tables and meta keys behind.
The full before and after
| Step | DB size | Homepage DB time | TTFB | Worth it? |
|---|---|---|---|---|
| Baseline | 1.24 GB | 780 ms | 1,410 ms | – |
| 1. Revisions removed | 1.09 GB | 762 ms | 1,385 ms | Space only |
| 2. Transients + autoload | 1.02 GB | 611 ms | 1,180 ms | Yes |
| 3. Orphaned metadata | 0.94 GB | 570 ms | 1,135 ms | Moderate |
| 4. Spam and trash comments | 0.91 GB | 558 ms | 1,120 ms | Admin only |
| 5. MyISAM to InnoDB | 0.93 GB | 331 ms | 690 ms | Biggest win |
| 6. Missing indexes | 0.96 GB | 142 ms | 430 ms | Huge |
| 7. Rebuild + object cache | 0.88 GB | 61 ms | 210 ms | Yes |
Total: 85 percent lower TTFB and 92 percent less database time per page load, with 29 percent less storage. Notice that the two “boring” storage steps contributed about 4 percent of the speed gain, while engine and index work contributed more than 60 percent.
Doing it with a plugin instead
If SSH and SQL are not an option, plugins can handle steps 1 through 4 and the final rebuild reasonably well:
- WP-Optimize: good all rounder, deletes revisions, transients, spam and runs table optimization on a schedule.
- Advanced Database Cleaner: best at identifying orphaned tables, options and cron tasks left by removed plugins.
- WP-Sweep: uses core delete functions rather than raw SQL, which is the safest approach for meta cleanup.
- Index WP MySQL For Speed: adds and reverts schema indexes without hand written ALTER statements.
What no plugin can do for you: right size the InnoDB buffer pool, read your slow query log, or decide that a specific index is worth its write cost. That part is either your job or your host’s.
FAQ
How can I clean up my WordPress database?
Back it up, then remove post revisions, auto drafts and trashed posts, delete expired transients, clear spam and trashed comments, delete orphaned rows in wp_postmeta, wp_commentmeta, wp_usermeta and wp_term_relationships, drop tables left behind by uninstalled plugins, then rebuild the tables. WP-CLI (wp transient delete --expired, wp db optimize) or a plugin such as WP-Optimize both work.
How do you optimize database performance beyond cleanup?
Cleanup reduces size; performance comes from structure and memory. Use InnoDB everywhere, size innodb_buffer_pool_size so your working set fits in RAM, index the columns your slowest queries filter on, add a persistent object cache, and keep the autoloaded options payload under about 800 KB.
Is WP-Optimize good?
Yes for routine cleaning, scheduled table optimization and its image and cache features. It will not convert storage engines with the care a large table needs, and it will not design indexes for your specific query patterns, so treat it as maintenance rather than tuning.
Is it safe to delete all post revisions?
Yes, revisions are only editorial history. Use WP-CLI or a plugin so related meta rows are removed too, and set WP_POST_REVISIONS to a small number instead of false so you keep a short safety net. The piece How to clean your WordPress database makes a good next read.
How often should I optimize my WordPress database?
Automate transient and spam cleanup weekly. Review autoload size, orphaned meta and slow queries once a quarter. Full table rebuilds are only needed after a large deletion, not on a monthly schedule.
Is WordPress outdated in 2026?
No. It still powers a large share of the web and recent releases have improved autoload handling, query performance and caching APIs. Slow WordPress sites are almost always the result of plugin bloat, MyISAM leftovers, missing indexes and thin server memory, not the platform itself.
Will database optimization improve my Core Web Vitals?
It improves TTFB, which feeds directly into Largest Contentful Paint and Interaction to Next Paint on dynamic pages such as carts, search results, filtered archives and logged in views that page caching cannot serve.
Need someone to run this on your site?
Database work is easy to get wrong on a live store. If you want a measured audit with before and after numbers like the ones above, including engine conversion, index design and server side tuning, get in touch with our team and we will start with a read only analysis of your slow queries.