WooCommerce database optimization for high-order-volume stores

Contents

• Prerequisites
• Finding the slow queries
• Migrating to HPOS (High-Performance Order Storage)
• Cleaning the tables that grow alongside orders
• Verifying the result
• Scheduling recurring maintenance
• When to escalate

Prerequisites

• A WooCommerce store running WooCommerce 8.2 or later (the version that made HPOS stable and default for new installs). Older versions can upgrade WooCommerce first.
• SSH or WP-CLI access to the server. Most steps use wp commands. If you only have wp-admin access, the WooCommerce > Status > Tools screen covers some of the same actions, but CLI is faster and more precise for stores with large tables.
• A current database backup. Every step below is reversible, but a backup means you do not have to trust that claim. On managed hosting, check that your daily backup ran within the last 24 hours. On a self-managed server, wp db export backup-$(date +%Y%m%d).sql creates one.
• Access to phpMyAdmin, Adminer, or direct MySQL/MariaDB CLI for running diagnostic queries.
• Familiarity with why WooCommerce puts different pressure on the database than a content site. That concept article covers the "why"; this article covers the "fix."

Finding the slow queries

Before changing anything, identify what is actually slow. The wrong fix for the wrong bottleneck wastes time.

Step 1. Install Query Monitor on a staging copy (never production for debugging). Open the admin orders screen. In the Query Monitor panel, sort queries by duration. The top three queries tell you whether the cost is in wp_postmeta joins (legacy order storage), wp_wc_orders lookups (HPOS), or something else entirely.

Step 2. Check which order storage backend is active. In wp-admin, go to WooCommerce > Settings > Advanced > Features. If "Order data storage" says WordPress posts storage, the store is on legacy storage and the biggest single improvement is HPOS migration. If it says High-Performance Order Storage, the store is already on HPOS and the bottleneck is elsewhere: orphaned data, Action Scheduler bloat, or autoload pressure in wp_options.

Step 3. Measure the size of the tables that typically bloat. Run this in your database client:

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()
  AND table_name IN (
    'wp_postmeta', 'wp_posts',
    'wp_wc_orders', 'wp_wc_orders_meta',
    'wp_actionscheduler_actions', 'wp_actionscheduler_logs',
    'wp_woocommerce_sessions', 'wp_options'
  )
ORDER BY data_length DESC;

On a store with 100,000+ orders that has never been cleaned, wp_actionscheduler_logs alone can reach tens of gigabytes. That table has nothing to do with order storage but everything to do with how the admin feels.

Migrating to HPOS (High-Performance Order Storage)

If the store is still on legacy post-based order storage, this is the single largest performance improvement available. WooCommerce's own benchmarks on a 400,000-order test store show the difference:

The reason is structural. Legacy storage writes roughly 40 rows per order (one to wp_posts, ~39 to wp_postmeta). HPOS writes at most 5 rows across four dedicated tables with purpose-built indexes.

The migration steps

Step 1. Run the compatibility checker first. Go to WooCommerce > Settings > Advanced > Features and look for the compatibility status. WooCommerce flags extensions that have not declared HPOS support. An incompatible extension does not necessarily block migration, but it means you should test on staging before touching production.

Step 2. Enable HPOS with synchronization on. This is the safe path: both the old tables and the new tables stay in sync, so rolling back is a settings change, not a data recovery.

wp wc hpos enable --with-sync

Or in wp-admin: set "Order data storage" to High-Performance Order Storage and keep "Enable compatibility mode" checked. At this point, new orders write to HPOS tables and get synced back to wp_posts. Old orders still need to be migrated.

Step 3. Sync existing orders. On a store with fewer than 100,000 orders, the built-in background sync usually finishes within hours. For larger stores, the CLI is more reliable:

wp wc hpos sync --batch-size=500

WooCommerce's large store migration guide documents a store with 9 million orders where the CLI sync took approximately one week. That guide recommends a three-phase approach: keep sync-on-read active for the first ~6 hours, then disable it, then disable sync-on-write after ~1 week once you have confirmed data integrity.

Step 4. Verify data integrity after the sync completes:

wp wc hpos verify_cot_data --verbose

This command compares orders across both storage backends and reports any mismatches. Zero mismatches means the migration is clean.

Step 5. Once you are confident, disable compatibility mode to stop the dual-write overhead:

wp wc hpos compatibility-mode disable

If anything goes wrong, the official rollback path is explicit: re-enable sync, wait for tables to reconcile, then switch back to posts storage. Both directions are hot migrations with no downtime required.

Important for April 2026 and later: WooCommerce 10.7, scheduled for April 14, 2026, disables sync-on-read by default. This affects stores that have custom code writing orders directly to wp_postmeta (bypassing the WooCommerce CRUD layer). If your store has custom integrations that use wp_update_post() or update_post_meta() for order data, test them before upgrading to 10.7.

Cleaning the tables that grow alongside orders

HPOS migration fixes the order query path. These steps fix the adjacent tables that bloat silently on every active WooCommerce store, regardless of order storage backend.

Action Scheduler logs

The wp_actionscheduler_logs table records a log entry for every scheduled action WooCommerce and its extensions run. The default retention is 30 days, and the default cleanup batch size is 20 rows per pass. On a store that processes hundreds of orders per day, the cleanup cannot keep up and the table grows without bound. GitHub issue #26818 documents stores where this table reached 55 GB+.

Clean up the backlog:

# Delete completed actions older than 7 days (adjust threshold as needed)
wp action-scheduler clean --before="7 days ago" --status=complete

Then increase the cleanup batch size so it keeps up going forward. Add this to your theme's functions.php or a site-specific plugin:

// Raise Action Scheduler cleanup from 20 to 500 per batch
add_filter('action_scheduler_cleanup_batch_size', function () {
    return 500;
});

Expired WooCommerce sessions

The wp_woocommerce_sessions table stores cart session data for every visitor who adds something to their cart. Expired sessions accumulate from abandoned carts and are not cleaned automatically on every store. Remove them:

DELETE FROM wp_woocommerce_sessions
WHERE session_expiry < UNIX_TIMESTAMP();

On a high-traffic store, this can reclaim hundreds of megabytes.

Orphaned postmeta

If the store ran on legacy storage for years before migrating to HPOS, wp_postmeta likely contains orphaned rows: meta entries whose parent post no longer exists, revision meta that serves no purpose, and leftover data from uninstalled extensions. The safe diagnostic query:

SELECT COUNT(*)
FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

If the count is significant (thousands or more), delete the orphans:

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

Autoloaded data in wp_options

WooCommerce extensions are a common source of autoload bloat in wp_options. Session data, large product attribute arrays, and extension settings pile up. The autoload article covers the full diagnostic and cleanup workflow. The short version: if your autoloaded data exceeds 800 KB (the WordPress 6.6 Site Health threshold), that is worth fixing independently of order storage.

Post revisions

WordPress stores revisions for every saved change. WooCommerce order edits on legacy storage created post revisions too. Limit them going forward in wp-config.php:

define('WP_POST_REVISIONS', 5);

Clean up excess existing revisions:

wp post delete $(wp post list --post_type=revision --format=ids) --force

Verifying the result

After completing the steps above, re-run the table size query from the diagnostic step. Compare data_mb values before and after. On a store that was on legacy storage with 100,000+ orders and had never run cleanup, I have seen the combined database size drop by 40 to 60 percent.

Then open the admin orders screen. Filter, search, sort by date. The difference on a store that migrated from legacy to HPOS is usually immediately obvious: a screen that took 8 to 12 seconds loads in under 2.

Scheduling recurring maintenance

The cleanup above is not a one-time event. These tables grow back.

• Action Scheduler: the action_scheduler_cleanup_batch_size filter from the earlier step keeps the table from growing out of control. Verify it quarterly by checking table size.
• Expired sessions: add a scheduled WP-CLI command or a server cron that runs the session cleanup SQL monthly.
• Autoload audit: run the four-value autoload measurement query every quarter. Five minutes of checking prevents months of accumulated overhead.
• Table optimization: run wp db optimize monthly to reclaim disk space from deleted rows. InnoDB does not return space to the filesystem automatically after large deletes.

When to escalate

If the admin orders screen is still slow after HPOS migration and a full cleanup pass, the remaining causes are typically outside the scope of a self-service fix:

• N+1 queries from extensions. An extension that runs one database query per order row on the admin list screen produces a linear slowdown that no amount of table cleanup fixes. Query Monitor on staging shows the culprit. The fix is usually a plugin replacement or a support ticket to the extension developer.
• InnoDB buffer pool too small for the working set. If the database server has 1 GB of RAM and the combined WooCommerce tables are 4 GB, MySQL reads from disk constantly. innodb_buffer_pool_size needs to be large enough to hold the working set. On managed hosting, this is a support ticket. On a self-managed server, set it to 50 to 70 percent of available RAM. The MySQL 8.0 optimization manual covers the tuning.
• Read replicas for reporting. If the store runs heavy WooCommerce Analytics queries alongside order processing, those report queries compete with checkout queries for database resources. Offloading reports to a read replica separates the workloads entirely. This is an infrastructure change, not a WordPress change.

When you reach out for help, have this ready: the output of the table size query, the current order storage backend (HPOS or legacy), your WooCommerce and PHP versions, whether an object cache is active (Redis or Memcached), and the three slowest queries from Query Monitor on the affected screen. That is enough for someone to triage without a second round trip.