finsilabs/woocommerce-performance

WooCommerce Performance

Overview

WooCommerce performance problems typically stem from five sources: expensive product queries with un-indexed meta tables, unbounded AJAX cart/checkout calls, missing persistent object cache, bloated wp_options autoload data, and a growing wp_woocommerce_sessions and order table without cleanup. This skill covers profiling, query optimization, Redis object caching, and scheduled maintenance routines.

When to Use This Skill

• When store pages are slow to load under moderate traffic (hundreds of concurrent users)
• When server CPU spikes during WooCommerce AJAX calls (add-to-cart, shipping calculation)
• When MySQL query time shows in New Relic or Query Monitor as the primary bottleneck
• When the wp_options table autoload size exceeds 1–2 MB
• When wp_woocommerce_sessions has millions of rows slowing down session lookups
• When implementing Redis or Memcached to reduce MySQL load on a high-traffic store

Core Instructions

1. Profile first with Query Monitor

   Install Query Monitor plugin to identify slow queries in the admin and frontend:

   # Via WP-CLI
   wp plugin install query-monitor --activate
   

   Focus on:

   • Queries per page load (> 50 is a concern)
   • Duplicate queries (same query run multiple times)
   • Slow queries (> 100ms individual queries)
   • Large result sets (queries returning thousands of rows)

   For production profiling, enable MySQL slow query log:

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

2. Enable Redis persistent object cache

   The single highest-impact optimization for WooCommerce. Without it, every page load re-fetches the same product data from MySQL:

   # Install Redis server
   sudo apt install redis-server
   
   # Install the Redis Object Cache plugin
   wp plugin install redis-cache --activate
   wp redis enable
   

   Configure in wp-config.php:

   define('WP_REDIS_HOST', '127.0.0.1');
   define('WP_REDIS_PORT', 6379);
   define('WP_REDIS_DATABASE', 0);
   define('WP_REDIS_TIMEOUT', 1);
   define('WP_REDIS_READ_TIMEOUT', 1);
   define('WP_REDIS_MAXTTL', 86400); // 24 hours max TTL
   
   // Selective cache groups — exclude user sessions from Redis (they change constantly)
   define('WP_REDIS_IGNORED_GROUPS', ['wc_session_id', 'counts', 'plugins']);
   

3. Optimize WooCommerce-specific database queries

   <?php
   
   // Add missing indexes for common WooCommerce product queries
   // Run once via WP-CLI: wp eval-file add-indexes.php
   
   global $wpdb;
   
   // Index for product meta queries (stock status, visibility, price)
   $wpdb->query("
       ALTER TABLE {$wpdb->postmeta}
       ADD INDEX wc_product_meta_lookup (meta_key(32), meta_value(64))
   ");
   
   // Index for order meta queries
   $wpdb->query("
       ALTER TABLE {$wpdb->postmeta}
       ADD INDEX wc_order_customer_lookup (meta_key(32), meta_value(100))
   ");
   

   Use WooCommerce's HPOS (High-Performance Order Storage) to move orders out of post meta:

   // wp-config.php — enable HPOS (WooCommerce 7.1+)
   // This is configured via WooCommerce Settings → Advanced → Features
   // Enables dedicated order tables: wp_wc_orders, wp_wc_order_items, etc.
   

   # Check HPOS status
   wp wc hpos status
   # Migrate orders to HPOS
   wp wc hpos migrate --batch-size=500
   

4. Clean up transients, sessions, and log tables

   <?php
   // Scheduled cleanup — run daily via Action Scheduler
   
   add_action('my_plugin_daily_cleanup', function () {
       global $wpdb;
   
       // Delete expired WooCommerce sessions (older than 48 hours)
       $expiry_threshold = time() - (48 * HOUR_IN_SECONDS);
       $wpdb->query($wpdb->prepare("
           DELETE FROM {$wpdb->prefix}woocommerce_sessions
           WHERE session_expiry < %d
           LIMIT 5000
       ", $expiry_threshold));
   
       // Delete expired transients
       $wpdb->query("
           DELETE FROM {$wpdb->options}
           WHERE option_name LIKE '_transient_timeout_%'
             AND option_value < UNIX_TIMESTAMP()
           LIMIT 5000
       ");
       $wpdb->query("
           DELETE t FROM {$wpdb->options} t
           LEFT JOIN {$wpdb->options} e
               ON e.option_name = REPLACE(t.option_name, '_transient_', '_transient_timeout_')
           WHERE t.option_name LIKE '_transient_%'
             AND e.option_id IS NULL
           LIMIT 5000
       ");
   
       // Rotate WooCommerce logs older than 30 days
       $wpdb->query("
           DELETE FROM {$wpdb->prefix}woocommerce_log
           WHERE timestamp < DATE_SUB(NOW(), INTERVAL 30 DAY)
           LIMIT 10000
       ");
   });
   
   // Register the scheduled event
   add_action('init', function () {
       if (!as_next_scheduled_action('my_plugin_daily_cleanup')) {
           as_schedule_recurring_action(strtotime('tomorrow midnight'), DAY_IN_SECONDS, 'my_plugin_daily_cleanup');
       }
   });
   

5. Optimize the wp_options autoload table

   Autoloaded options are loaded on every page request. Bloated autoload causes significant overhead:

   -- Find large autoloaded options
   SELECT option_name, LENGTH(option_value) as size_bytes
   FROM wp_options
   WHERE autoload = 'yes'
   ORDER BY LENGTH(option_value) DESC
   LIMIT 30;
   

   <?php
   // Fix: Store plugin data as transients or post meta instead of autoloaded options
   
   // Bad — autoloaded, loaded on every request
   update_option('my_plugin_product_cache', $large_array, true);
   
   // Good — not autoloaded
   update_option('my_plugin_product_cache', $large_array, false);
   
   // Better — use transient with expiry
   set_transient('my_plugin_product_cache', $large_array, HOUR_IN_SECONDS);
   
   // Disable autoload for existing options
   global $wpdb;
   $wpdb->update(
       $wpdb->options,
       ['autoload' => 'no'],
       ['option_name' => 'woocommerce_attribute_taxonomies']
   );
   

Examples

Fragment caching for product loops

<?php

// Cache rendered product card HTML in Redis to avoid re-rendering
function render_product_card_cached(int $product_id): string {
    $cache_key = "product_card_{$product_id}_" . get_locale();
    $cached = wp_cache_get($cache_key, 'product_cards');

    if ($cached !== false) {
        return $cached;
    }

    $product = wc_get_product($product_id);
    if (!$product) return '';

    ob_start();
    wc_get_template('content-product.php', ['product' => $product]);
    $html = ob_get_clean();

    // Cache for 30 minutes; invalidate on product update
    wp_cache_set($cache_key, $html, 'product_cards', 30 * MINUTE_IN_SECONDS);

    return $html;
}

// Purge cache when product is updated
add_action('woocommerce_update_product', function (int $product_id) {
    wp_cache_delete("product_card_{$product_id}_" . get_locale(), 'product_cards');
    // Also delete all locale variants
    wp_cache_flush_group('product_cards');
});

Detect and fix N+1 product queries

<?php

// Bad — triggers N+1 queries (one per product in loop)
$product_ids = wc_get_featured_product_ids();
foreach ($product_ids as $id) {
    $product = wc_get_product($id); // <-- separate query for each product
    echo $product->get_name();
}

// Good — prime the cache before the loop
$product_ids = wc_get_featured_product_ids();
// Pre-warm the object cache with all products in a single query
$products = array_map('wc_get_product', $product_ids); // Still N queries without this:

// Better — use WC_Product_Query with ID filter (single query)
$products = wc_get_products([
    'include' => $product_ids,
    'limit'   => count($product_ids),
    'return'  => 'objects',
]);

foreach ($products as $product) {
    echo $product->get_name(); // data already loaded
}

Best Practices

• Enable HPOS (High-Performance Order Storage) on WooCommerce 7.1+ stores — it moves order data into dedicated tables with proper indexes, eliminating the wp_postmeta bottleneck for orders
• Add Redis/Memcached object cache before anything else — it eliminates redundant MySQL queries that WordPress and WooCommerce make on every request and often cuts DB load by 60–80%
• Run cleanup on off-peak hours via Action Scheduler — batch DELETE operations (limit 5000 rows per run) to avoid long table locks during cleanup
• Use WP_DEBUG_LOG with SAVEQUERIES only on dev — enabling these on production kills performance; use APM tools (New Relic, Datadog) for production profiling
• Paginate admin order queries — loading all orders in one go (posts_per_page: -1) locks MySQL and causes timeouts; always use paged with posts_per_page ≤ 100
• Set WP_REDIS_IGNORED_GROUPS to exclude volatile data (cart, session counters) from Redis to prevent cache stampedes on high-traffic checkout pages
• Monitor wp_options table size weekly — set up an alert if autoloaded data exceeds 800KB; common culprits are plugins that store large arrays as autoloaded options

Common Pitfalls

| Problem | Solution | |---------|----------| | Redis cache not reducing MySQL queries | Check wp_cache_get hit rate in Query Monitor — a low hit rate means cache keys are being invalidated too aggressively or TTLs are too short | | Cleanup queries cause table-level locks | Use row-level LIMIT clauses in DELETE queries (max 5000 rows per run) and schedule them during low-traffic windows | | HPOS migration breaks third-party plugins | Audit all plugins for get_post_meta(order_id, ...) usage before enabling HPOS — these must be updated to use $order->get_meta() | | Slow product listing despite indexes | Check whether wc_lookup_table_enabled is active; WooCommerce 3.7+ introduced wp_wc_product_meta_lookup table that dramatically speeds up product queries | | Object cache stale after product import | Call wp_cache_flush() or wp_cache_flush_group('products') at the end of bulk import scripts to invalidate stale product caches | | Session table grows despite cleanup | Ensure WC_Session_Handler is using the database backend (not PHP sessions); default cookie-based sessions don't create DB rows, but custom plugins may force DB sessions |

Related Skills

• @woocommerce-plugin-development
• @woocommerce-rest-api
• @database-query-optimization
• @caching-strategies
• @infrastructure-performance