How to Clean a WordPress Database: A Real phpMyAdmin Walkthrough

Most guides about cleaning a WordPress database are written by people who have never watched one shrink. They list the same five plugins, paste the same revision-deleting SQL, and promise a faster site. This one is different in a boring but useful way: we opened phpMyAdmin on a real WordPress install, measured it, cleaned it, and measured it again. Every number and screenshot below came from that session on 20 September 2026.

The patient was the WPArena demo environment — a WordPress multisite network with 25 sites, 391 tables and years of plugin residue. It started at 42.61 MB of data and index, dragging another 30 MB of dead space inside its tablespaces. It finished at 36.48 MB with zero overhead. Here is exactly how, including the parts that did not work the way the tutorials say.

Read this before you delete a single row

Cleaning a database is destructive and there is no undo button in phpMyAdmin. A DELETE with a typo in the WHERE clause will happily remove every post you have ever written, and it will do it in under a second.

So, in order, before anything else:

1. Take a full database export and verify it. Not "I have a backup plugin installed" — an actual file, downloaded, that you have opened. Our pre-cleanup export was 14 MB of SQL (1.5 MB gzipped) and took about six seconds. There is no excuse for skipping this.
2. Know how you would restore it. A backup you have never restored is a hypothesis. If you are not sure, read our guide to choosing, scheduling and testing a restore.
3. Do it on staging if you can. A WordPress staging site turns this from a risk into a rehearsal.
4. Do not do this during traffic peaks. OPTIMIZE TABLE locks tables while it runs.

If you only take one thing from this article, take that list.

Step 1: Get into phpMyAdmin from your hosting panel

Almost every shared and VPS host puts phpMyAdmin behind the control panel rather than on a public URL. Log into the panel first.

From there, find the databases section and click through to phpMyAdmin. Some panels log you straight in; others hand you a login form. If you get the form, your credentials are the DB_USER and DB_PASSWORD values in your site's wp-config.php file — not your WordPress admin login, and not your panel login. That distinction trips up more people than anything else in this process.

Once you are in, pick your WordPress database from the left sidebar. If you host several sites on one account you will see several databases; match the name against DB_NAME in wp-config.php. Guessing is how people clean the wrong site.

Step 2: Sort the tables by size and look at what you actually have

Open the Structure tab and click the Size column header to sort largest first. This one click tells you more than any plugin dashboard.

Note what is on top in our case: comments tables at 2.9 MiB and 1.8 MiB, posts at 2.6 MiB, then a string of options tables at 1.5–1.6 MiB each. The received wisdom is that wp_postmeta is always the problem. On this install it was not. Look at your own numbers instead of assuming.

Step 3: Measure the overhead — this is the number nobody shows you

Table sizes only tell half the story. MySQL and MariaDB also track data_free: space that has been allocated to a table and then abandoned when rows were deleted. It still occupies disk. It is invisible in the WordPress admin.

Run this in the SQL tab, replacing nothing — DATABASE() resolves to whichever database you have selected:

SELECT COUNT(*) AS `Tables`,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS `Total MB`,
       ROUND(SUM(data_free) / 1024 / 1024, 2) AS `Reclaimable overhead MB`,
       CONCAT(ROUND(100 * SUM(data_free) / SUM(data_length + index_length + data_free), 1), '%') AS `Wasted space`
FROM information_schema.TABLES
WHERE table_schema = DATABASE();

Thirty megabytes of nothing. That is the single biggest win available on most older WordPress databases, and no amount of deleting revisions will recover it on its own — you need OPTIMIZE TABLE for that, which we get to in step 8.

Step 4: Audit your autoloaded options

This matters more than total database size. Every single page load, WordPress runs one query that pulls every wp_options row marked autoload = yes into memory. A bloated autoload payload is a tax on every request, including cached ones being generated.

SELECT option_name AS `Autoloaded option`,
       ROUND(LENGTH(option_value) / 1024, 1) AS `KB`
FROM wp_options
WHERE autoload IN ('yes', 'on')
ORDER BY LENGTH(option_value) DESC
LIMIT 20;

Two things stand out in that result, and they are typical:

• Transients are autoloading. _transient_wp_core_block_css_files at 21.7 KB and _transient_dirsize_cache at 4.6 KB were being read on every page load. Transients are supposed to be temporary; autoloaded ones are just permanent overhead with extra steps.
• Dead plugins leave live rows. updraft_last_backup (17.6 KB) and a cluster of Jetpack sync settings were autoloading whether or not anyone was using those features.

On WordPress 6.6 and later you will also see autoload values of on, off and auto alongside the old yes/no. That is why the query above checks for both 'yes' and 'on'. A lot of older tutorials only check 'yes' and quietly under-report your real autoload payload.

Step 5: Inventory the bloat before you delete it

Count first, delete second. This query gives you the whole picture in one result:

SELECT 'Post revisions' AS `What is in there`, COUNT(*) AS `Rows` FROM wp_posts WHERE post_type = 'revision'
UNION ALL SELECT 'Auto-drafts', COUNT(*) FROM wp_posts WHERE post_status = 'auto-draft'
UNION ALL SELECT 'Trashed posts', COUNT(*) FROM wp_posts WHERE post_status = 'trash'
UNION ALL SELECT 'Transient rows in wp_options', COUNT(*) FROM wp_options WHERE option_name LIKE '%\_transient\_%'
UNION ALL SELECT 'Spam comments', COUNT(*) FROM wp_comments WHERE comment_approved = 'spam'
UNION ALL SELECT 'Unapproved comments', COUNT(*) FROM wp_comments WHERE comment_approved = '0'
UNION ALL SELECT 'Orphaned postmeta rows', COUNT(*) FROM wp_postmeta pm
  LEFT JOIN wp_posts p ON p.ID = pm.post_id WHERE p.ID IS NULL;

Across the whole network the totals were 241 post revisions, 184 transient rows, 14 auto-drafts, one spam comment and — notably — zero orphaned postmeta and zero orphaned commentmeta. Every cleanup plugin on the market advertises orphaned meta removal. On a reasonably maintained site there frequently is not any. Measure before you pay for a solution to a problem you do not have.

Step 6: Delete post revisions

WordPress keeps every saved draft of every post forever unless you tell it otherwise. On a site with 135 posts we had 94 revisions. On a 2,000-post publication you can have tens of thousands.

The standard query removes revisions along with their term relationships and meta rows in one pass:

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';

Note the row count: 94 revisions, but 237 rows deleted. Each revision drags meta rows behind it, which is why revisions cost more than their post count suggests.

Deleting revisions is a one-off. To stop them accumulating again, cap them in wp-config.php:

define( 'WP_POST_REVISIONS', 5 );

Five is a sensible number for most sites — enough to recover from a bad edit, not enough to matter. While you are in there, it is worth understanding how AUTOSAVE_INTERVAL works, since autosaves are the other half of this equation.

Step 7: Clear the transients

Transients are cached values with an expiry date. WordPress is good at creating them and historically bad at cleaning them up, particularly when a plugin that created them has been deactivated.

DELETE FROM wp_options WHERE option_name LIKE '%\_transient\_%';

This is safe. Anything that genuinely needs a transient will regenerate it on the next request. The one visible cost is that the first few page loads afterwards will be marginally slower while caches rebuild. That is it.

The backslashes in that LIKE pattern are deliberate — underscore is a single-character wildcard in SQL, so _transient_ unescaped would match more than you intend.

Step 8: Run OPTIMIZE TABLE — and understand what actually happens

Deleting rows does not shrink files. It marks space as reusable inside the table, which is exactly the 30 MB of overhead we measured in step 3. To give that space back you have to rebuild the tables.

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

Read that output carefully, because it confuses a lot of people: "Table does not support optimize, doing recreate + analyze instead." That is not an error. Modern WordPress tables are InnoDB, and InnoDB does not implement OPTIMIZE TABLE literally — it silently converts it into a full table rebuild, which is precisely what you wanted. Status OK means it worked.

What it does mean is that the operation is heavier than it looks. Each table is copied and rebuilt, and it is locked while that happens. Five tables took 0.89 seconds here; on a 2 GB store database it can take minutes. Do it during a quiet window.

The before and after, measured

Total rows removed across the network: 612. Backups now transfer 22% faster, which is the most reliable practical benefit of this whole exercise.

What we did not measure, and will not pretend to

We did not benchmark a page-load improvement, because on a 42 MB database there would not have been an honest one to report. This is the part the thin guides get wrong. Database cleanup is not a speed plugin.

Database size genuinely matters when:

• Your autoload payload is in the megabytes — that is a real per-request cost.
• Backups are timing out or filling your disk quota.
• wp_postmeta has grown into the millions of rows and admin queries are crawling.
• You are migrating and a 3 GB export will not import inside the host's time limit.
• Your host meters database storage.

If your site is slow and your database is 40 MB, the database is not why. Go and look at time to first byte, your caching setup, and unoptimised images instead.

Safe to delete vs. looks like junk but is not

This is where cleanup plugins do damage, because they operate on patterns rather than understanding.

Safe to delete:

• Post revisions, auto-drafts, and posts sitting in trash.
• Spam and trashed comments.
• Transients, expired or not.
• Genuinely orphaned postmeta and commentmeta — rows whose parent no longer exists.
• Tables belonging to plugins you removed months ago, after you have confirmed the plugin is gone and you have a backup.

Looks like junk, is not:

• rewrite_rules in wp_options. It is large and it looks like cache. Deleting it breaks every permalink until WordPress regenerates it. Flush permalinks from Settings instead.
• wp_user_roles. It is a serialised blob that looks like debris. It defines who can do what on your site.
• cron. Delete it and scheduled posts, backups and update checks stop happening.
• _wp_attached_file and _wp_attachment_metadata in wp_postmeta. High row counts, but they are what connects your media library to actual files.
• wp_termmeta on a site that "does not use term meta". Plenty of SEO and ecommerce plugins do.
• Anything in wp_usermeta matching session_tokens — deleting these logs everyone out, including you, mid-operation.
• Action Scheduler tables (*_actionscheduler_*). They can get big, but WooCommerce and others depend on them. Prune them through WooCommerce's own tools, not with raw SQL.

The general rule: if you cannot explain what a row does, leave it. Reclaiming 200 KB is never worth an outage.

The WP-CLI version, for people on managed hosts

If you have SSH access this is faster, safer and repeatable. It is also the only sane option when a database is too big for phpMyAdmin to handle without timing out.

# Always first. Verify the file exists before continuing.
wp db export backup-before-cleanup.sql

# See what you are dealing with
wp db size --tables --format=table

# Delete all post revisions
wp post delete $(wp post list --post_type=revision --format=ids) --force

# Clear transients (expired only, then all)
wp transient delete --expired
wp transient delete --all

# Empty spam and trashed comments
wp comment delete $(wp comment list --status=spam --format=ids) --force
wp comment delete $(wp comment list --status=trash --format=ids) --force

# Delete trashed posts
wp post delete $(wp post list --post_status=trash --format=ids) --force

# Rebuild tables and reclaim the overhead
wp db optimize

# Confirm
wp db size --size_format=mb

A caution on the $(...) patterns: if the inner command returns nothing, the outer command errors out. That is noisy but harmless. On very large sites the argument list can also get too long — in that case add --posts_per_page=500 and run it a few times.

Managed host caveats

If you are on a managed WordPress host, parts of the above will not apply, and that is deliberate on their part.

• Kinsta gives you SSH and WP-CLI, but no phpMyAdmin by default — you reach the database through the MyKinsta dashboard or an SSH tunnel. wp db optimize works fine.
• WP Engine provides phpMyAdmin through the user portal rather than a panel URL, and restricts some operations. Their platform also runs its own object cache, so transient behaviour differs from what you would see on shared hosting.
• Shared hosts often kill long-running queries. If OPTIMIZE TABLE on your whole database times out, run it on a handful of tables at a time.
• Any host with an object cache (Redis or Memcached) stores transients in memory, not in wp_options. Your transient count will be low and that is a sign of good configuration, not a clean database.

Picking a host that gives you real database access matters more than most people realise when they compare plans. Our WordPress hosting comparison covers who gives you what.

If you would rather use a plugin

Nothing wrong with it, as long as you understand you are trading control for convenience. WP-Optimize, Advanced Database Cleaner and WP-Sweep all do the work described above through a UI, and all of them will happily let you tick a box you do not understand.

Two rules if you go this route: take the backup yourself rather than trusting the plugin's, and deactivate the cleaner once you are done. A database cleanup plugin that sits active forever is one more thing autoloading options on every request — which is the problem you were trying to solve.

Troubleshooting

"Error establishing a database connection" after cleanup. You almost certainly did not cause this with a DELETE. Check that OPTIMIZE TABLE has finished, then work through the connection error checklist.

Site loads but every link 404s. You deleted rewrite_rules. Go to Settings → Permalinks and click Save. No other action needed.

You are logged out and cannot log back in. Session tokens in wp_usermeta were cleared. Log in again. If the login fails entirely, the wp_users or wp_usermeta table was damaged — restore from your backup.

Scheduled posts stopped publishing. The cron option was removed. It regenerates, but pending schedules are gone; reschedule them manually.

phpMyAdmin times out mid-query. The table is too big for the host's execution limit. Switch to WP-CLI, or batch the deletes with LIMIT 1000 and repeat until zero rows are affected.

Missing images or broken media. Attachment meta was deleted by an over-eager cleanup. Restore from backup — there is no clean way to rebuild these associations at scale.

The database is the same size afterwards. You deleted rows but did not rebuild. Run OPTIMIZE TABLE or wp db optimize. This is by far the most common complaint and it is always this.

For anything else, our general WordPress troubleshooting walkthrough is the place to start.

When cleaning is not the answer

Occasionally a database is beyond tidying — usually after years of migrations, a hacked site, or table prefixes from a WordPress version old enough to vote. In that case the pragmatic move is a clean install with a fresh database and a content import rather than surgery on the old one. That is a migration, not a cleanup, and we cover it properly in how to migrate a WordPress site.

If the reason your database is a mess is that something got in, clean the database second and read the WordPress security checklist first.

A maintenance schedule that actually holds

• Set once: WP_POST_REVISIONS capped in wp-config.php. This is 90% of the long-term benefit for about thirty seconds of work.
• Monthly: empty spam and trash. Both are one click in the WordPress admin.
• Quarterly: re-run the overhead query from step 3. If wasted space is over 20%, run wp db optimize.
• Annually, or after removing plugins: audit autoloaded options and check for tables left behind by plugins you no longer run.

That is the whole discipline. The reason databases end up 41% empty is not that cleanup is hard — it is that nobody ever looks.