Knowing how to optimise WordPress database tables is one of the most effective ways to keep your site fast, stable, and efficient. Over time, WordPress databases accumulate post revisions, transients, spam comments, orphaned metadata, and table overhead that quietly slow down every page load. This guide walks you through every practical method — from beginner-friendly plugins to advanced WP-CLI commands — so you can reclaim performance without risking your data.
Why WordPress Database Tables Become Bloated
Before diving into the fix, it helps to understand the cause. WordPress stores almost everything — posts, options, user data, plugin settings — in a MySQL (or MariaDB) database made up of several core tables such as wp_posts, wp_options, wp_postmeta, and wp_comments.
Common Sources of Database Bloat
- Post revisions: Every time you save a draft or update a post, WordPress stores a revision. A single post can accumulate dozens of revision rows.
- Auto-drafts: WordPress saves auto-drafts constantly; old ones are rarely cleaned up automatically.
- Transients: Plugins store temporary data as transients in
wp_options. Expired transients that are never purged inflate this table significantly. - Spam and trashed comments: Comment spam sits in the database even after it is marked as spam.
- Orphaned metadata: When posts or users are deleted, their associated rows in
wp_postmetaandwp_usermetaoften remain. - Table overhead: After rows are deleted, MySQL does not immediately reclaim that disk space, leaving "overhead" that slows queries.
How to Optimise WordPress Database Tables Using a Plugin
Plugins are the safest and most beginner-friendly route. They provide a visual interface, let you preview what will be deleted, and often include scheduling features so optimisation runs automatically.
Using WP-Optimize
- Log in to your WordPress dashboard and navigate to Plugins > Add New.
- Search for WP-Optimize and click Install Now, then Activate.
- Go to WP-Optimize > Database from the left-hand menu.
- Review the list of optimisations available — post revisions, auto-drafts, trashed posts, spam comments, expired transients, and table overhead.
- Tick the items you want to clean. For a first run, select all categories.
- Click Run all selected optimizations and wait for the process to complete.
- Check the Tables tab to see a before-and-after overhead report for each table.
- Optionally, click Settings to schedule weekly automatic optimisation.
Using Advanced Database Cleaner
Advanced Database Cleaner is worth considering if you want granular control. It lets you inspect orphaned tables left by uninstalled plugins — something WP-Optimize does not do by default.
- Install and activate Advanced Database Cleaner from the plugin repository.
- Navigate to WP DB Cleaner > General Clean Up.
- Click Scan next to each category (revisions, transients, orphaned data, etc.).
- Review the items found, then click Delete All or select specific rows to remove.
- Go to the Tables tab, tick all tables with overhead, and click Optimize selected.
Always take a full database backup before running any cleanup tool. Use your host's backup feature, UpdraftPlus, or the export function in phpMyAdmin.
How to Optimise WordPress Database Tables with WP-CLI
WP-CLI is the command-line interface for WordPress. It is the fastest and most scriptable way to optimise database tables, making it ideal for developers and site owners with SSH access.
Basic WP-CLI Database Commands
Open your terminal, SSH into your server, and navigate to your WordPress root directory. Then run the commands below.
# Optimise all database tables in one command
wp db optimize
# Check tables for errors before optimising
wp db check
# Repair corrupted tables
wp db repair
# Delete all post revisions
wp post delete $(wp post list --post_type=revision --format=ids) --force
# Delete all expired transients
wp transient delete --expired
# Delete ALL transients (use with caution)
wp transient delete --all
# Show database size per table
wp db size --tables
Scheduling WP-CLI Optimisation with a Cron Job
You can automate database optimisation by adding a server-side cron job. Log in to your server and edit the crontab with crontab -e, then add a line like the one below to run optimisation every Sunday at 2 AM:
0 2 * * 0 cd /var/www/html && wp db optimize --allow-root >> /var/log/wp-db-optimize.log 2>&1
Replace /var/www/html with your actual WordPress installation path. The log file lets you confirm the job ran successfully each week.
How to Optimise WordPress Database Tables via phpMyAdmin
phpMyAdmin is available on virtually all shared hosting control panels (cPanel, Plesk, DirectAdmin). It gives you direct SQL access without needing a plugin or CLI.
Running OPTIMIZE TABLE in phpMyAdmin
- Log in to your hosting control panel and open phpMyAdmin.
- Select your WordPress database from the left sidebar.
- Click Check All at the bottom of the tables list to select every table.
- Open the With selected dropdown and choose Optimize table.
- phpMyAdmin will display a results table confirming each table was optimised successfully.
Running Custom SQL to Remove Bloat
Click the SQL tab in phpMyAdmin and run the following queries one at a time. Replace wp_ with your actual table prefix if you changed it during installation.
-- Delete all post revisions
DELETE FROM wp_posts WHERE post_type = 'revision';
-- Delete orphaned post meta
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL;
-- Delete expired transients
DELETE FROM wp_options
WHERE option_name LIKE '_transient_timeout_%'
AND option_value < UNIX_TIMESTAMP();
DELETE FROM wp_options
WHERE option_name LIKE '_transient_%'
AND option_name NOT LIKE '_transient_timeout_%'
AND LEFT(option_name, 19) = '_transient_timeout_';
-- Optimise all tables (run after deletes)
OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options, wp_comments, wp_commentmeta;
Preventing Future Database Bloat in WordPress
Cleaning your database is only half the battle. Preventing bloat from returning keeps your maintenance overhead low and your site consistently fast.
Limit Post Revisions in wp-config.php
By default, WordPress stores unlimited revisions. You can cap this number by adding a constant to your wp-config.php file.
// Limit post revisions to 3 per post (add before the line "That's all, stop editing!")
define( 'WP_POST_REVISIONS', 3 );
// Disable revisions entirely (not recommended for most sites)
define( 'WP_POST_REVISIONS', false );
Control the Auto-Save Interval
WordPress auto-saves every 60 seconds by default. Reducing the frequency cuts the number of auto-draft rows created:
// Auto-save every 5 minutes instead of every 60 seconds
define( 'AUTOSAVE_INTERVAL', 300 );
Audit and Remove Unnecessary Plugins
Every plugin you install can write rows to the database. Deactivating a plugin does not remove its data; you must delete it. After deletion, use Advanced Database Cleaner to identify and remove any orphaned tables the plugin left behind. Perform a plugin audit at least once per quarter.
Use Object Caching to Reduce Query Load
Object caching (via Redis or Memcached) stores frequently requested database results in memory, dramatically reducing how often MySQL is queried. Many managed WordPress hosts offer this as a one-click feature. If yours does not, the Redis Object Cache plugin combined with a Redis server is a robust solution.
Schedule Regular Backups Before Optimisation
Make automated backups a non-negotiable step before any database work. UpdraftPlus, BlogVault, and ManageWP all support scheduled off-site backups. Set backups to run at least daily, and verify that restores work by testing one periodically.
Measuring the Impact of Your Database Optimisation
After optimising, you should verify that your efforts made a tangible difference. Here are the key metrics and tools to check.
Database Size and Table Overhead
Run wp db size --tables before and after optimisation to compare sizes. In phpMyAdmin, the Data and Overhead columns on the main tables view give an at-a-glance summary. A well-optimised site should show 0 B overhead on all tables.
Query Performance
Install the Query Monitor plugin to inspect how many database queries each page load triggers and how long they take. After optimisation, you should see reduced query counts and faster execution times — typically under 50ms for straightforward queries on a well-tuned site.
Page Load Speed
Use Google PageSpeed Insights, GTmetrix, or WebPageTest before and after your optimisation session. Database improvements most noticeably reduce Time to First Byte (TTFB), because the server spends less time waiting for query results before it begins sending the HTML response.
Frequently Asked Questions
How often should I optimise my WordPress database tables?
For most sites, once a month is sufficient. High-traffic sites with active comment sections or WooCommerce orders may benefit from weekly optimisation. Use a plugin like WP-Optimize to schedule it automatically so you never have to remember.
Is it safe to delete WordPress post revisions?
Yes, deleting old post revisions is safe. Revisions are historical snapshots used only for manual restores within the editor. Once a post is published and you are happy with its content, older revisions have no functional value. Limiting revisions to 3–5 per post going forward is a widely recommended best practice.
Will optimising the database speed up my WordPress site?
It depends on how bloated your database has become. Sites that have never been optimised often see noticeable TTFB improvements — sometimes 100–300ms faster — especially when the wp_options table has been inflated by thousands of expired transients. Optimisation is most impactful when combined with caching and a good hosting environment.
What is table overhead in a WordPress database?
Table overhead is unused disk space left behind after rows are deleted from a MySQL table. MySQL marks the space as free but does not immediately return it to the filesystem. Running OPTIMIZE TABLE rebuilds the table structure, reclaims that space, and can also defragment index pages, resulting in faster query execution.
Keeping your WordPress database lean is an ongoing task, not a one-time fix. By combining the plugin, WP-CLI, and phpMyAdmin methods described above with proactive prevention steps like limiting revisions and scheduling automated cleanups, you can maintain a fast and healthy database with minimal effort. If you would rather not handle any of this manually, WP AI Agent is a powerful tool that lets you manage WordPress maintenance tasks — including database optimisation — through simple natural-language AI chat, making advanced site management accessible to everyone.