WooCommerce stores inherit WordPress’s schema — wp_posts for products and orders, wp_postmeta for all custom fields, and wp_options for configuration. This works fine at small scale but degrades predictably as data grows. WooCommerce database optimisation targets the specific pain points: unindexed meta queries, bloated autoload in wp_options, and the architectural shift to HPOS which moves orders to purpose-built indexed tables. Combined with Query Monitor for real-time diagnosis, this is a complete WooCommerce database optimisation toolkit. See our WooCommerce page speed guide for the full performance context.

WooCommerce database optimisation — missing indexes, HPOS migration, autoload cleanup and slow query log by CodeAtoZ

The single most impactful WooCommerce database optimisation on most stores is adding missing indexes to wp_postmeta. WooCommerce queries like get_posts(meta_query: [...]) perform full table scans without the right composite index on (meta_key, meta_value). For stores with hundreds of thousands of postmeta rows, this is the difference between a 20ms query and a 2,000ms one. Run SHOW INDEX FROM wp_postmeta in phpMyAdmin — if you only see the default meta_id and post_id indexes, add the composite one. Check Google Web.dev for performance benchmarking methodology.

-- Check existing indexes first
SHOW INDEX FROM wp_postmeta;

-- Add composite index if missing (run in phpMyAdmin or via WP-CLI)
ALTER TABLE wp_postmeta ADD INDEX meta_key_value (meta_key(32), meta_value(64));

-- Also useful for product attribute queries:
ALTER TABLE wp_term_relationships ADD INDEX term_order (term_order);

-- Reclaim space after bulk deletions:
OPTIMIZE TABLE wp_postmeta wp_posts wp_options;

High-Performance Order Storage (HPOS) is the most important structural WooCommerce database optimisation for high-order-volume stores. HPOS moves orders from wp_posts and wp_postmeta into dedicated wp_wc_orders and wp_wc_orders_meta tables with proper indexes designed for order queries. Stores with 10,000+ orders see 10x faster order list pages and admin order searches after HPOS migration. Enable it at WooCommerce → Settings → Advanced → Features → High-Performance Order Storage. Run compatibility mode first — it keeps both tables in sync — then switch to HPOS-only after confirming all plugins are compatible. The WooCommerce HPOS documentation covers the migration process in detail.

The wp_options table’s autoload column controls which options are loaded on every single WordPress request. Plugins that store large transients or serialised data with autoload=yes bloat the startup memory footprint. For WooCommerce database optimisation, query your total autoload size — if it exceeds 1MB, you have a problem. Delete expired transients (WP-Optimize or WP-CLI transient delete --all), and set large plugin options to autoload=no where not needed on every page load. Enable the MySQL slow query log to find the most expensive queries across your whole stack — not just WooCommerce — and use EXPLAIN SELECT to analyse them. Use the free Query Monitor plugin to profile live queries. Our WooCommerce performance team runs full database audits as part of site speed projects. See also our WooCommerce caching plugins guide for the complementary caching layer.

-- Total autoload size (should be under 1MB)
SELECT SUM(LENGTH(option_value)) / 1024 / 1024 as autoload_mb
FROM wp_options WHERE autoload = 'yes';

-- Find biggest autoload offenders
SELECT option_name, LENGTH(option_value) as size_bytes
FROM wp_options WHERE autoload = 'yes'
ORDER BY size_bytes DESC LIMIT 20;

-- Enable slow query log (run in MySQL as root)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';