How I Cleaned Up 8 Million Orphaned Rows Choking a WooCommerce Database

Checkout Was Taking 12 Seconds and Nobody Could Explain Why

A client running a WooCommerce store — around 4,000 products, 200 orders a day — reported that checkout had become painfully slow. Add to cart was fine. Browsing was fine. But the moment a customer hit the checkout page, they'd wait 10-12 seconds before the form rendered. Abandoned carts were climbing.

The server was a 4-core VPS with 8GB RAM, PHP 8.2-FPM, Nginx, MariaDB 10.11, and Redis object cache. Plenty of headroom for this traffic level. WP Rocket was handling page caching. Everything looked right on paper.

I started with the slow query log.

The Slow Query Log Told the Whole Story

I enabled the MariaDB slow query log with a 1-second threshold:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Within five minutes of a customer reaching checkout, I had my answer. The same pattern repeated over and over:

SELECT meta_key, meta_value FROM wp_postmeta WHERE post_id = 48231;
# Query_time: 3.412  Rows_examined: 8421053

A simple get_post_meta() call was scanning 8.4 million rows. That single query was running multiple times per checkout page load — once for the order, once for each product in the cart, once for the customer record. Each one taking 2-4 seconds.

The wp_postmeta Table Had Grown to 8.4 Million Rows

I connected to the database and checked the table sizes:

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

The result was ugly:

+---------------------+---------+----------+------------+
| table_name          | data_mb | index_mb | table_rows |
+---------------------+---------+----------+------------+
| wp_postmeta         | 2,847.3 |   1,102.4|    8421053 |
| wp_posts            |   312.7 |      48.2|      89431 |
| wp_options          |    28.4 |       3.1|       4218 |
| wp_wc_orders        |    22.1 |       8.7|      31205 |
+---------------------+---------+----------+------------+

Nearly 3GB of data in wp_postmeta alone. The store had 89,000 posts (products, orders, revisions, attachments combined), but 8.4 million meta rows — roughly 94 meta rows per post. That ratio should be closer to 20-30 on a healthy WooCommerce store.

Finding Out What Was Actually in There

Before deleting anything, I needed to understand what was eating all that space. This query groups meta rows by key and shows the biggest offenders:

SELECT meta_key,
       COUNT(*) AS row_count,
       ROUND(SUM(LENGTH(meta_value)) / 1024 / 1024, 2) AS value_size_mb
FROM wp_postmeta
GROUP BY meta_key
ORDER BY row_count DESC
LIMIT 20;

The results revealed five distinct problems:

1. Orphaned meta from deleted posts — 2.1 million rows where the parent post no longer existed. Years of WooCommerce orders had been deleted through the admin, but their postmeta rows were left behind.

2. Revision meta — 1.8 million rows belonging to post revisions. Every time someone edited a product, WordPress created a revision and duplicated all its meta fields. With 4,000 products edited regularly, this added up fast.

3. Abandoned plugin meta — 1.4 million rows from three plugins that had been deactivated and removed over a year ago. Their _yoast_wpseo_*, _elementor_*, and _aiowps_* prefixed meta keys were still sitting in the table.

4. WooCommerce legacy order meta — 1.9 million rows of order meta that should have been migrated to HPOS tables but hadn't been cleaned up after migration.

5. ACF field reference duplicates — 600,000 rows of _field_* reference keys that ACF stores alongside every field value.

The Cleanup — Step by Step

I took a full database backup first. No exceptions.

wp db export /tmp/backup-before-postmeta-cleanup.sql --add-drop-table

Step 1: Delete Orphaned Meta

These are rows where the parent post has been deleted but the meta rows remain. This is always safe to remove:

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

This removed 2,138,491 rows in about 45 seconds. On larger tables, I batch it to avoid locking:

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

Run that in a loop until it reports zero affected rows.

Step 2: Purge Revision Meta

WordPress revisions inherit all meta from the parent post. On a WooCommerce store with products that have 30-40 meta fields each, this is devastating. First, I limited future revisions in wp-config.php:

define('WP_POST_REVISIONS', 5);

Then I deleted meta belonging to revisions older than 30 days:

DELETE pm FROM wp_postmeta pm
INNER JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.post_type = 'revision'
AND p.post_date < DATE_SUB(NOW(), INTERVAL 30 DAY);

This cleared 1,614,227 rows.

Step 3: Remove Abandoned Plugin Meta

I identified the prefixes from deactivated plugins and verified each one wasn't in use:

SELECT DISTINCT meta_key FROM wp_postmeta
WHERE meta_key LIKE '_elementor_%'
   OR meta_key LIKE '_yoast_wpseo_%'
   OR meta_key LIKE '_aiowps_%'
LIMIT 50;

After confirming none of these plugins were active or needed:

DELETE FROM wp_postmeta
WHERE meta_key LIKE '_elementor_%'
   OR meta_key LIKE '_yoast_wpseo_%'
   OR meta_key LIKE '_aiowps_%';

That removed another 1,389,102 rows. Be careful with this step — only delete meta from plugins you're certain are gone for good.

Step 4: Clean Up Legacy WooCommerce Order Meta

This client had already migrated to HPOS (High-Performance Order Storage), but the old order meta in wp_postmeta hadn't been purged. WooCommerce provides a tool for this under WooCommerce > Settings > Advanced > Features, where you can delete legacy order data after verifying the HPOS migration is complete.

I used WP-CLI to trigger it:

wp wc hpos cleanup --batch-size=500

This removed the remaining 1.9 million legacy order meta rows over about 20 minutes.

Step 5: Reclaim Disk Space

After deleting millions of rows, MariaDB doesn't automatically free the disk space. You need to optimise the table:

OPTIMIZE TABLE wp_postmeta;

This took about 3 minutes and reclaimed 2.4GB of disk space. On a production site with heavy traffic, run this during a low-traffic window — it locks the table briefly.

Adding the Missing Compound Index

WordPress ships with a basic index on post_id and another on meta_key for the wp_postmeta table. But it doesn't include a compound index that covers the most common query pattern — looking up a specific meta key for a specific post. I added one:

ALTER TABLE wp_postmeta ADD INDEX pm_post_key (post_id, meta_key(191));

The (191) prefix length keeps the index within InnoDB's 767-byte limit for older configurations. On MariaDB 10.11 with innodb_large_prefix=ON (the default), you can use the full key length, but 191 characters covers every meta key I've ever seen.

The Results

Before cleanup:

• wp_postmeta: 8,421,053 rows, 2,847 MB
• Checkout page load: 10-12 seconds
• get_post_meta query time: 2-4 seconds

After cleanup:

• wp_postmeta: 1,279,233 rows, 418 MB
• Checkout page load: 1.8 seconds
• get_post_meta query time: 2-8 milliseconds

The compound index alone dropped query times by roughly 80%. Combined with removing 7 million unnecessary rows, the improvement was dramatic.

Preventing This From Happening Again

This kind of bloat accumulates silently over months and years. By the time someone notices, the table is already massive. I set up a monthly monitoring query that runs via WP-CLI cron:

wp db query "SELECT COUNT(*) AS total_rows, ROUND(SUM(LENGTH(meta_value))/1024/1024, 1) AS size_mb FROM wp_postmeta;" --format=table

I also added an orphan check to the monthly maintenance routine:

wp db query "SELECT COUNT(*) AS orphaned_rows FROM wp_postmeta pm LEFT JOIN wp_posts p ON p.ID = pm.post_id WHERE p.ID IS NULL;"

If either number starts climbing unexpectedly, it's time to investigate before the table hits millions of rows again.

The other long-term fix is migrating WooCommerce order data to HPOS if you haven't already. Orders generate the bulk of postmeta on most WooCommerce stores, and HPOS moves all of that into dedicated, properly indexed tables. I've written about the HPOS migration process and the silent breakage it can cause if done carelessly.

Stop Firefighting. Start Maintaining.

I manage 70+ WordPress sites for agencies and businesses. Whether you need ongoing maintenance, emergency support, or a one-off performance fix — I can help.

View Maintenance Plans → Get in Touch →