WordPress Database Cleanup: Reduce Size by Up to 80% with Real Queries

Your WordPress database is 400 MB, but your site only has 2 MB of content

You know this scenario: you take a look at the wp_options table in phpMyAdmin and your eyes land on the number 350,000 rows. Or you open wp_postmeta and see 120,000 rows, while you only have 300 posts. Backing up the database takes 10 minutes, WordPress admin pages have become slow, and every time you install a plugin, it feels like the site is gasping for air.

The problem isn't WordPress itself. The problem is the things that WordPress and plugins have silently accumulated in the database over the years. The good news is that most of this bulk can be removed with a few simple queries. No plugins, no unnecessary risk, just pure SQL.

Four things that eat up WordPress database size

Before we write queries, we need to know what we're dealing with. These are the four main sources of WordPress database bloat:

• Revisions — Every time you edit a post, WordPress saves a full copy of it in wp_posts. Ten edits on one post means ten extra rows, each with the entire text.
• Transients — Temporary cache that plugins and WordPress itself keep in wp_options. The problem is that many transients aren't cleaned up after they expire and stay forever.
• Orphaned Meta — When you delete a post, its related rows in wp_postmeta remain. No query accesses them, but they take up space.
• Leftover tables from deleted plugins — You delete the plugin, but its tables stay in the database. Some of these tables can be tens of megabytes in size.

Now let's see how to clean each one.

Delete revisions with one query

The simplest and safest place to start is revisions. This query deletes all revisions except the latest version of each post:

DELETE FROM wp_posts
WHERE post_type = 'revision'
AND ID NOT IN (
    SELECT MAX(ID) FROM wp_posts
    WHERE post_type = 'revision'
    GROUP BY post_parent
);

If you want revisions to not be created at all from now on, add this line to wp-config.php:

define('WP_POST_REVISIONS', 2);

This setting forces WordPress to keep only the last 2 versions of each post. Zero means completely disabling, but my recommendation is 2. Because if you accidentally delete some text and want to restore it, you'll have a previous version available.

Clean up expired transients

Transients are stored in wp_options with names like _transient_... and _transient_timeout_.... The following query deletes all transients whose time has passed:

DELETE FROM wp_options
WHERE option_name LIKE '_transient_timeout_%'
AND option_value < UNIX_TIMESTAMP();

After this, you also need to delete the corresponding rows. Run this second query:

DELETE o1 FROM wp_options o1
LEFT JOIN wp_options o2
ON o2.option_name = CONCAT('_transient_', SUBSTRING(o1.option_name, 20))
WHERE o1.option_name LIKE '_transient_%'
AND o2.option_id IS NULL;

This is where people make mistakes: many run the first query and think the job is done. But the main transient rows (without timeout) still remain in the table. If you only run the first query, the size will be reduced by about half, and a few weeks later the same situation returns. Run both queries back to back.

Find and delete orphaned meta

Orphaned meta are rows in wp_postmeta that reference a post that no longer exists. This query deletes them:

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

You can repeat the same pattern for wp_commentmeta if you see that table has also grown. Just change the table name.

Identify leftover tables from deleted plugins

This part is the most sensitive task. Some plugins delete their tables when removed, and some don't. To find extra tables, first check what prefix WordPress uses. It's usually wp_, but some sites have a custom prefix. Run this query in phpMyAdmin:

SHOW TABLES LIKE 'wp\_%';

Now set aside the list of standard WordPress tables: wp_posts, wp_postmeta, wp_options, wp_users, wp_usermeta, wp_terms, wp_termmeta, wp_term_taxonomy, wp_term_relationships, wp_comments, wp_commentmeta, wp_links, and multisite tables if your WordPress is multisite. Any other table you see is either from a plugin or from a custom script.

Before deleting any table, do a quick Google search with the table name to see which plugin it belongs to. If you've truly deleted the plugin and don't need its data, run DROP TABLE. But if you're unsure, take a backup first. Deleting the wrong table means losing data that might have no way to be recovered.

Back up before anything, optimize after everything

Don't break this rule: before running any DELETE query, take a full backup of the database. Not just the table you want to clean, but the entire database. In phpMyAdmin, click the Export option and choose the Quick method. It takes two minutes, and if something goes wrong, it's your lifeline.

After cleanup, optimize the tables so the freed space actually returns to the server. In phpMyAdmin, click on the database name, select all tables, and choose the Optimize Table option from the dropdown menu. This rebuilds indexes and organizes tables that have become fragmented after deleting many rows.

Important note: if your site is on shared hosting and has a large database, running DELETE queries might encounter a 504 Gateway Timeout error. In that case, limit the query with LIMIT and run it multiple times:

DELETE FROM wp_posts
WHERE post_type = 'revision'
LIMIT 1000;

Run this query repeatedly until it says 0 rows affected. Yes, it's tedious. But it's safer than a heavy query that crashes the server.

This is where they go wrong: installing a database cleanup plugin and forgetting about it

I've seen this pattern many times: a site admin installs a database cleanup plugin, runs it once, sees the result, and is satisfied. Three months later, the database is back to its previous size. Because cleanup plugins only work when they're executed. If you don't set up a Cron Job for them, they run that one time and that's it.

Additionally, every plugin you install adds its own layer of overhead to the database. Installing a permanent plugin to solve a problem that can be fixed with 5 simple queries is like hiring a full-time worker to clean a room who only works one day a month.

My approach is this: write a simple script with the queries above and run it on the server with a monthly cron job. If you're using Linux hosting, you can use crontab. This runs automatically without needing a plugin, and you can forget about it.

After cleanup, be careful about what you install

The database is clean and its size has dropped from 400 MB to 80 MB. Now what? If your usage patterns don't change, you'll be back in the same situation in six months. Take a few simple habits seriously:

• Delete any plugin you've installed experimentally and don't use, on the same day. Not a week later — the same day.
• Before deleting a plugin, check its documentation to see whether it cleans up its tables or not. If it doesn't, manually delete the tables after removal using the method I described above.
• For sites with multiple authors, definitely set the revision limit in wp-config.php. Authors tend to edit a post ten times.

If your site has a large volume of images and media files, a big part of the slowness might not be from the database, but from the lack of proper caching. In that case, first run a website speed test and see where the real problem is. Database cleanup is just one of the optimization tools, not all of them.

Frequently Asked Questions

Will deleting revisions harm my posts?

No. Revisions are previous versions of posts, and deleting them has no effect on the final published version. The only thing you lose is the ability to revert to older edit versions. If you need edit history, set the limit to 2 or 3, not zero.

How do I know if my WordPress database needs cleanup?

In phpMyAdmin, click on the database name and check the total size. If the database size is larger than the total content size of your site (images and files excluded), or if the number of rows in wp_options has exceeded a few tens of thousands, it's time for cleanup. A typical site with 500 posts shouldn't have a database larger than 50 MB.

Will database cleanup cause loss of WordPress settings?

No, not if you only delete revisions, expired transients, and orphaned meta. WordPress's main settings are stored in rows with specific names like siteurl or blogname in wp_options, and the queries above don't touch them. Just be careful not to write a query that empties the entire wp_options table.

Will the site become faster after database cleanup?

It depends. If the site's slowness was due to a heavy database, then yes, you'll notice a difference. But if the problem is lack of caching, heavy images, or weak hosting, database cleanup won't perform miracles. To diagnose accurately, first find the source of the slowness. If the database is running on hosting with limited resources and its size is large, maybe it's time to consider WordPress hosting with higher resources.