woocommerce-performance

What this skill does

# 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:

   ```bash
   # 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:

   ```sql
   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:

   ```bash
   # 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`:

   ```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
   <?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:

   ```php
   // 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.
   ```

   ```bash
   # 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
   <?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:

   ```sql
   -- 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
   <?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
<?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
<?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)