woocommerce-performance（woocommerce 表现）

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: 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';

- 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)

1. 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']);
2. 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
3. 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');}});
4. 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

Related Skills

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