Your WordPress database quietly grows every day. Every post revision, spam comment, expired transient, and leftover setting adds rows to your database tables. Over months and years, that growth slows down backups, makes the admin area feel sluggish, and can even slow down your pages. The good news is that you can clean it up yourself, with no plugin, using tools your hosting account already provides.
In this guide, you will learn how to optimize WordPress database without plugin help, starting with a backup, then removing the most common kinds of bloat safely, then optimizing the tables themselves. You will also learn how to prevent the bloat from coming back, and exactly which data you must never delete.
What Actually Bloats a WordPress Database?
Most database bloat comes from normal WordPress activity, not from anything going wrong. Understanding each source helps you clean the right things and leave the rest alone.
- Post revisions: WordPress saves a copy of a post every time you save a draft. A post edited twenty times can carry twenty revisions, and each one is a full row in the posts table.
- Spam and trashed comments: comments marked as spam or moved to trash stay in the database until they are permanently deleted.
- Transients: these are temporary cached values, often set by plugins and themes. Expired transients should disappear on their own, but many are left behind, especially by poorly coded plugins.
- Orphaned postmeta: when a post, product, or attachment is deleted, some of its metadata rows are left behind with no parent post to belong to.
- Autoloaded options: options marked for autoloading are loaded on every single page request. A bloated options table, often filled by old plugin settings, slows down every page on your site.
- Old drafts and auto drafts: abandoned drafts and auto saved drafts accumulate silently, particularly on multi author sites.
Why Should You Back Up Before Touching the Database?
Database cleanup involves deleting rows, and a wrong delete cannot be undone with an undo button. A full backup taken immediately before you start is your safety net. If anything goes wrong, you restore the backup and try again, with no lost content.
Do not rely on a backup from last week. Take a fresh one right before the cleanup, and confirm that it actually completed. If you have never made a database backup manually, read our guide on how to back up your WordPress database without a plugin first. It takes only a few minutes, and it turns a risky job into a safe one.
How Do You Inspect Your Database in phpMyAdmin?
phpMyAdmin is the database tool included with most hosting control panels, including cPanel. Open it, select your WordPress database, and sort the table list by size. On most sites, the posts, postmeta, options, and comments tables are the largest, and they are where cleanup pays off most.
Take note of your table prefix before running any SQL. The default prefix is wp_, but many sites use a custom prefix for security. Every query in this guide uses wp_ as an example, so replace it with your own prefix before running anything. Running a query with the wrong prefix either does nothing, which is harmless, or targets the wrong tables, which is not, so double check the prefix first.
How Do You Delete Old Post Revisions Safely?
Post revisions are the safest and usually the largest cleanup win. Old revisions are not shown on your site, and deleting them does not change any published post. In phpMyAdmin, open the SQL tab for your database and run a query like this, after replacing wp_ with your prefix:
DELETE FROM wp_posts WHERE post_type = 'revision';This deletes all revision rows while leaving published posts, pages, and attachments untouched. If you want to keep recent revisions and delete only very old ones, that level of filtering needs a date condition in the query, so for a first cleanup, deleting all revisions is simpler and still safe, provided you have a fresh backup.
After deleting revisions, you will also have orphaned postmeta rows that belonged to those revisions. Clean those next with a query that removes metadata whose parent post no longer exists:
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts p ON pm.post_id = p.ID WHERE p.ID IS NULL;Run this only after the revision delete, and only on a backed up database. It removes rows that point to nothing, which is the definition of orphaned data, so it cannot affect live content.
How Do You Clean Spam Comments and Transients?
Spam comments that sit in the spam queue still occupy database rows. You can delete them from the WordPress admin by emptying the spam and trash queues, which is safer than deleting them with SQL. Open Comments, switch to the Spam view, and click Empty Spam. Do the same for Trash. Only use SQL for comments if the admin screen is too slow to load because of an enormous spam backlog.
Expired transients are another safe cleanup. These are temporary values whose expiry time has already passed, so deleting them cannot break live features. A plugin that needs a transient value will simply regenerate it on the next request. Run this query in phpMyAdmin:
DELETE FROM wp_options WHERE option_name LIKE '_transient_timeout_%' AND option_value < UNIX_TIMESTAMP();This removes only expired transient timeouts. A second step removes the matching transient values themselves, but many site owners stop here and still see a meaningful reduction. If you are unsure about the second step, skip it: leaving unexpired transients alone is always safe.
How Do You Optimize and Repair Tables?
Deleting rows does not automatically shrink a table on disk. The freed space stays inside the table file until the table is optimized. In phpMyAdmin, select all tables in your database, open the With selected dropdown at the bottom of the table list, and choose Optimize table. This reclaims unused space and rebuilds the table indexes, which can speed up queries.
The same dropdown offers a Repair table option. Use it only if phpMyAdmin reports a table as crashed or marked as needing repair. Repairing a healthy table is unnecessary, and on a large table it can take a while, so do not run it as a routine step. Optimization is the routine step; repair is the emergency step.
How Do You Limit Revisions With wp-config.php?
Cleaning revisions once is helpful, but preventing unlimited revisions is better. You can limit how many revisions WordPress keeps by adding a line to your wp-config.php file, above the line that says stop editing. For example, keeping three revisions per post looks like this:
define( 'WP_POST_REVISIONS', 3 );With this setting, WordPress automatically discards older revisions once a post has three, so the posts table stops growing without bound. Setting the value to false disables revisions entirely, which we do not recommend for most sites, because revisions are genuinely useful when you need to recover an earlier draft. Three to five revisions is a sensible balance between safety and size.
Can You Optimize the Database With WP-CLI?
If your host provides command line access with WP-CLI installed, optimization becomes a one line job. After backing up, run the optimize command from your WordPress installation directory:
wp db optimizeThis performs the same table optimization as the phpMyAdmin method, without opening a browser tool. WP-CLI also offers a repair command and a command to check table status, which are useful on larger sites. If your host does not offer WP-CLI, the phpMyAdmin route achieves the same result, so do not switch hosts just for this feature.
For full documentation of every WP-CLI database command, see the official reference at developer.wordpress.org. The command line route is worth learning if you manage several sites, because the same cleanup script can be reused across all of them.
How Often Should You Optimize Your Database?
For a small blog with a few posts per month, cleaning the database every three to six months is plenty. For a busy WooCommerce store or a news site with daily publishing and heavy commenting, once a month is more appropriate. The right frequency depends on how fast your tables grow, which you can judge by checking table sizes in phpMyAdmin each time you clean.
Do not optimize daily. Table optimization locks tables briefly while it runs, and on a busy site that can cause momentary slowdowns. It is maintenance, not a performance trick, and doing it too often gains nothing. A sensible routine is to back up, clean revisions and transients, optimize tables, and then verify that the site still loads correctly, all in one session every few months.
What Should You Never Delete?
Some database rows look like clutter but hold live content or settings. Deleting them can break your site in ways that are hard to diagnose.
- Published posts, pages, and attachments in the posts table: never delete these with SQL. Delete content from the WordPress admin instead.
- Active users and their metadata: deleting user rows locks people out and breaks authorship on existing posts.
- Options rows for active plugins and your theme: if you do not recognize an option name, research it before deleting it.
- Unexpired transients: these hold live cached data. Deleting them is usually harmless, since they regenerate, but there is no benefit to removing values that are still valid.
- Any table you do not recognize: unknown tables may belong to a plugin you still use. Check the plugin list before touching them.
Will Database Cleanup Fix a Slow WordPress Site?
Database cleanup helps, but it is only one part of site speed. If your site is slow mainly because of a bloated database, cleanup will produce a noticeable improvement, especially in the admin area and on pages that run many queries. If the slowness comes from heavy images, a slow theme, or poor hosting, cleanup alone will not fix it.
For a broader diagnosis, read our guide on why your WordPress site is slow, which walks through the common causes in order. Hosting quality matters here too: a database on an overloaded shared server will struggle no matter how clean it is, which is why our web hosting guide covers the hosting factors that affect database performance.
Frequently Asked Questions
Is it safe to optimize a WordPress database without a plugin?
Yes, provided you take a fresh backup first and delete only provably dead data, such as old revisions, spam comments, and expired transients. Never delete published content, users, or options for active plugins.
How often should I clean my WordPress database?
Every three to six months for a small blog, and about once a month for a busy store or news site. Check table sizes in phpMyAdmin to judge how fast your database is growing.
Do I need WP-CLI to optimize my database?
No. phpMyAdmin offers the same optimize function through its table list, and it works on almost every shared host. WP-CLI is simply faster if you manage several sites from the command line.
Will deleting revisions remove my published posts?
No. Revisions are separate rows from published posts. Deleting rows with post type revision leaves your published posts, pages, and media completely untouched.
Last updated: October 2026.
