How 847,000 Post Revisions Turned a WordPress Admin Dashboard Into a 12-Second Ordeal

A client running a WooCommerce store with around 600 products and a blog with 180 posts messaged me about their WordPress admin. The posts list page was taking twelve seconds to load. The WooCommerce orders page was nearly as bad. Product editing was sluggish. The front end was fine — Redis object cache and nginx fastcgi cache were doing their jobs — but anything behind /wp-admin/ felt like wading through treacle.

The server wasn't under stress. A 4-core VPS with 8GB RAM, PHP 8.3, MariaDB 10.11. Load average sat at 0.4. PHP-FPM workers weren't exhausted. Nothing in the error logs. The site had been live for six years with steady content updates, and the slowness had crept in so gradually that the client assumed it was just "how WordPress is now."

It isn't. Something was wrong with the database.

Finding the bloat

I SSH'd in and checked the size of the main WordPress tables:

SELECT table_name,
       table_rows,
       ROUND(data_length / 1024 / 1024, 2) AS data_mb,
       ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_length DESC
LIMIT 10;

The result told the whole story:

+---------------------+------------+---------+----------+
| table_name          | table_rows | data_mb | index_mb |
+---------------------+------------+---------+----------+
| wp_posts            |     847263 |  980.44 |   412.18 |
| wp_postmeta         |    1204881 |  198.72 |   156.33 |
| wp_options          |       4812 |   14.28 |     0.89 |
| wp_wc_orders_meta   |      38291 |    6.14 |     3.22 |
| ...                 |            |         |          |
+---------------------+------------+---------+----------+

847,000 rows in wp_posts. Nearly a gigabyte of data, plus 412MB of indexes. For a site with 600 products and 180 blog posts.

Where were the rows coming from?

I broke down wp_posts by post_type to see what was actually in there:

SELECT post_type, post_status, COUNT(*) AS total
FROM wp_posts
GROUP BY post_type, post_status
ORDER BY total DESC
LIMIT 20;

+------------------+-------------+--------+
| post_type        | post_status | total  |
+------------------+-------------+--------+
| revision         | inherit     | 831406 |
| product          | publish     |    614 |
| auto-draft       | auto-draft  |   4218 |
| post             | publish     |    183 |
| attachment        | inherit     |   3842 |
| shop_order       | wc-completed|   2891 |
| nav_menu_item    | publish     |     42 |
| page             | publish     |     28 |
| ...              |             |        |
+------------------+-------------+--------+

831,000 revisions. That's 98% of the table. The remaining 2% was the actual content.

WordPress stores every revision as a full row in wp_posts, including a complete copy of the post_content field. A 2,000-word product description edited 80 times means 80 full copies of that content sitting in the database. Multiply that across 600 products — each updated every time someone adjusted a price, tweaked a description, or changed a stock level — and six years of unlimited revisions adds up to a staggering amount of dead weight.

Why this kills WP-Admin performance

The front end was fast because Redis cached the query results and nginx served full pages from disk. But WP-Admin bypasses most of that caching. Every time the client loaded the posts list, WordPress ran queries against wp_posts that had to scan or filter 847,000 rows. The indexes were bloated. The InnoDB buffer pool was wasting memory caching revision data that would never be read again.

I confirmed this by enabling the MariaDB slow query log temporarily:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

Within minutes of the client browsing WP-Admin, the slow log filled with queries like:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
WHERE 1=1
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future'
     OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending'
     OR wp_posts.post_status = 'private')
ORDER BY wp_posts.post_date DESC
LIMIT 0, 20;

This query should be instant on a table with 183 published posts. But MariaDB was filtering through 847,000 rows because the composite index on (post_type, post_status, post_date) had become enormous and fragmented. The query was taking 3.8 seconds. The posts list page fires several of these in sequence, which is how twelve seconds of wall-clock time accumulated.

Checking which posts had the most revisions

Before cleaning anything up, I wanted to know which specific posts were the worst offenders:

SELECT parent.ID,
       parent.post_title,
       parent.post_type,
       COUNT(child.ID) AS revision_count
FROM wp_posts AS parent
INNER JOIN wp_posts AS child
  ON child.post_parent = parent.ID
  AND child.post_type = 'revision'
GROUP BY parent.ID
ORDER BY revision_count DESC
LIMIT 15;

+------+-----------------------------+----------+----------------+
| ID   | post_title                  | post_type| revision_count |
+------+-----------------------------+----------+----------------+
| 1204 | Shipping & Delivery         | page     |           1847 |
|  892 | Terms & Conditions          | page     |           1203 |
|   48 | Homepage                    | page     |            987 |
|  614 | Premium Gift Set            | product  |            412 |
|  291 | Summer Collection           | product  |            388 |
| ...  |                             |          |                |
+------+-----------------------------+----------+----------------+

1,847 revisions on a single page. Every time someone tweaked the shipping policy over six years, WordPress dutifully stored a complete copy of the entire page content. The Terms & Conditions page had 1,203 revisions. The homepage had nearly a thousand.

WooCommerce products were heavy too. Products get updated far more often than blog posts — price changes, stock adjustments, description tweaks, SEO updates. A product with 400+ revisions was common across the catalogue.

The cleanup — safely, with WP-CLI

I wasn't going to run a raw DELETE FROM wp_posts WHERE post_type = 'revision' on a live production database. That approach skips WordPress hooks, leaves orphaned postmeta behind, and on a table this large, the single transaction would lock wp_posts for minutes.

WP-CLI handles this properly. It deletes revisions through WordPress's wp_delete_post_revision() function, which also cleans up associated metadata in wp_postmeta.

First, I counted what I was about to remove:

wp post list --post_type=revision --format=count

831406

Deleting 831,000 posts in a single WP-CLI command would exhaust PHP memory. I batched it in chunks of 1,000:

#!/bin/bash
BATCH=1000
TOTAL=$(wp post list --post_type=revision --format=count)
echo "Total revisions to delete: $TOTAL"

while [ "$TOTAL" -gt 0 ]; do
    IDS=$(wp post list --post_type=revision --format=ids --posts_per_page=$BATCH)
    if [ -z "$IDS" ]; then
        break
    fi
    wp post delete $IDS --force
    TOTAL=$(wp post list --post_type=revision --format=count)
    echo "Remaining: $TOTAL"
    sleep 1
done

echo "Done."

I ran this in a screen session during a low-traffic window (early morning). The sleep 1 between batches kept MariaDB from getting hammered with continuous deletes. The whole process took about 45 minutes.

While I was at it, I cleaned up the 4,218 auto-drafts — WordPress creates one every time someone opens a new post editor and then navigates away without saving:

wp post delete $(wp post list --post_type=any --post_status=auto-draft --format=ids) --force

Cleaning up orphaned postmeta

Deleting revisions through WP-CLI handles the associated postmeta, but years of plugin installs and removals had also left orphaned rows in wp_postmeta — metadata pointing to post IDs that no longer existed:

SELECT COUNT(*)
FROM wp_postmeta
WHERE post_id NOT IN (SELECT ID FROM wp_posts);

+----------+
| COUNT(*) |
+----------+
|   187429 |
+----------+

187,000 orphaned postmeta rows. I cleaned these out with a direct SQL delete, after taking a database backup:

wp db export ~/backup-before-postmeta-cleanup.sql --single-transaction

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

Reclaiming the space

After the cleanup, wp_posts was down to 16,200 rows. But InnoDB doesn't automatically reclaim disk space after deletes — the tablespace file stays the same size with empty pages inside it. I needed to rebuild the table:

OPTIMIZE TABLE wp_posts;
OPTIMIZE TABLE wp_postmeta;

On a nearly-gigabyte table, OPTIMIZE TABLE takes a while and locks the table during the rebuild. I ran this during the same maintenance window. For wp_posts it took about 90 seconds. The result:

wp_posts:     980MB → 18MB
wp_postmeta:  198MB → 64MB

The result

The posts list page in WP-Admin went from 12 seconds to 0.8 seconds. Product editing page loads dropped from 6 seconds to under 2. The MariaDB slow query log went silent — no more queries over 1 second from WP-Admin.

The InnoDB buffer pool was no longer wasting 600MB caching revision data that nobody would ever read. That freed memory improved cache hit rates for the queries that actually mattered — product lookups, cart calculations, and checkout processing.

Preventing it from happening again

The root cause was WordPress's default: unlimited revisions, forever. Six years of unlimited revisions on a site with active content editing is how you get 831,000 rows of dead weight.

I added two lines to wp-config.php:

define('WP_POST_REVISIONS', 5);
define('AUTOSAVE_INTERVAL', 120);

WP_POST_REVISIONS set to 5 means WordPress keeps the five most recent revisions per post and automatically deletes older ones when the post is next saved. AUTOSAVE_INTERVAL set to 120 seconds (up from the default 60) reduces the frequency of auto-draft creation.

Five revisions is enough to recover from a bad edit. More than that is insurance nobody ever claims.

For ongoing maintenance, I added a monthly cron job on the server to clean up any auto-drafts older than 30 days:

0 3 1 * * /usr/local/bin/wp post delete --path=/var/www/site $(wp post list --path=/var/www/site --post_type=any --post_status=auto-draft --date_query='{"before":"30 days ago"}' --format=ids) --force 2>/dev/null

How to check your own site

If your WordPress admin has been getting slower over time and you're not sure why, run this query:

SELECT post_type, COUNT(*) AS total
FROM wp_posts
GROUP BY post_type
ORDER BY total DESC;

If revision is the largest post type by a wide margin, you have the same problem. A site with 500 published posts shouldn't have 200,000 rows in wp_posts.

Stop Firefighting. Start Maintaining.

I manage 70+ WordPress sites for agencies and businesses. Revision bloat is one of those problems that builds silently over years until the admin dashboard becomes painful to use. It's part of the routine database health checks I run on every site under a maintenance plan.

Whether you need ongoing maintenance, emergency support, or a one-off performance fix — I can help.

View Maintenance Plans | Get in Touch