When your WooCommerce store reaches 100,000+ products, database performance becomes critical. Queries that worked fine with 1,000 products now take seconds.

Understanding the Problem

WooCommerce stores product data in multiple tables: wp_posts, wp_postmeta, wp_terms, and wp_wc_product_meta_lookup. With 100K products and 30+ meta fields each, wp_postmeta can have 3+ million rows.

Optimization Strategies

1. Enable HPOS

High-Performance Order Storage moves order data out of posts/postmeta tables. Enable in WooCommerce Settings Advanced Features.

2. Add Missing Indexes

Add indexes: ALTER TABLE wp_postmeta ADD INDEX meta_key_value (meta_key, meta_value(50));

3. Use Product Lookup Tables

WooCommerce 3.6+ includes lookup tables for faster queries. Make sure these are populated.

4. Clean Up Data

Remove orphaned postmeta, delete unused attributes, clean transients regularly, remove old revisions.

5. Optimize Queries

Avoid queries scanning entire tables. Use proper indexes, add LIMIT clauses.

Infrastructure

Move MySQL to dedicated server. Tune MySQL configuration. For high traffic, use read replicas.

Need database optimization? Contact our team.