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

Related Skills

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

WooCommerce Database Maintenance Plugin

Problem Description

A busy WooCommerce store has been running for three years without any database maintenance. The operations team has flagged that the wp_woocommerce_sessions table has grown to over 4 million rows (most of them abandoned sessions from bots and expired guest checkouts), expired transients are cluttering wp_options, and the WooCommerce log table is consuming gigabytes of disk space. Nightly backups are taking too long and the DB server is showing elevated I/O during business hours.

The team wants a WordPress plugin that runs automated cleanup on a daily schedule. A previous developer started work on the file below but never finished it — your job is to complete the implementation so it correctly handles all three cleanup tasks. The plugin must be safe to run on a live store: large DELETE operations must be batched appropriately, and the schedule must be registered so it only fires once per day and won't double-register on repeated plugin activations.

Output Specification

Produce a single PHP file named wc-maintenance.php that contains a complete, working WordPress plugin implementing the daily cleanup routine. The plugin header (Plugin Name, Description, Version) should be included.

Also produce a cleanup-design.md that briefly describes:

• The scheduling mechanism used and why it was chosen
• The batch size decisions and their rationale
• The session expiry window used

Input Files

The following starter file is provided. Extract it before beginning.

=============== FILE: inputs/wc-maintenance-stub.php =============== <?php /**

• Plugin Name: WC Maintenance
• Description: Daily WooCommerce database cleanup
• Version: 0.1.0

*/

// TODO: Register the daily cleanup action using the correct scheduling API // TODO: Hook the cleanup function to the registered action

function wc_maintenance_run_cleanup() { global $wpdb;

// TODO: Delete expired WooCommerce sessions // Sessions are stored in {prefix}woocommerce_sessions // A session is expired when session_expiry timestamp is in the past

// TODO: Delete expired transients from wp_options // Step 1: delete _transient_timeout_ entries that are past due // Step 2: delete orphaned _transient_ entries with no matching timeout entry

// TODO: Delete old WooCommerce log entries // Logs are stored in {prefix}woocommerce_log }

WooCommerce Featured Products Widget with Caching

Problem Description

A home goods retailer has a custom "Featured Products" widget on their homepage that currently causes serious performance problems. Every page load executes a separate database query for each featured product, and the rendered HTML is re-generated from scratch on every request — even though featured products rarely change. On high-traffic days the widget alone accounts for over 40% of page-level database queries.

The team needs this widget rewritten to be cache-friendly. They want the product data fetched in as few queries as possible and the rendered HTML cached so repeated requests serve it from memory. When a featured product is updated in the admin, the cache for that product's rendered output must be invalidated. The current broken implementation is provided below.

Additionally, the store's admin has a report page that loops through orders to generate a revenue summary. That query is currently written to fetch all orders at once and has caused MySQL timeouts on stores with large order histories. The admin report code also needs to be corrected.

Output Specification

Produce two PHP files:

• featured-products-widget.php — the corrected widget implementation with fragment caching
• admin-order-report.php — the corrected order query for the admin revenue report

Also produce optimization-notes.md briefly describing the caching strategy used for the widget (cache key scheme, group name, TTL, and invalidation approach).

Input Files

The following files are provided. Extract them before beginning.

=============== FILE: inputs/featured-products-widget-broken.php =============== <?php /**

• Broken Featured Products Widget
• Problems: N+1 queries, no caching, no cache invalidation

*/

function render_featured_products(): string { $product_ids = wc_get_featured_product_ids(); $html = '';

foreach ($product_ids as $id) { // N+1: separate DB query per product $product = wc_get_product($id); if (!$product) continue;

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

return $html; }

=============== FILE: inputs/admin-order-report-broken.php =============== <?php /**

• Broken Admin Order Report
• Problem: fetches all orders at once — causes MySQL timeouts on large stores

*/

function get_revenue_summary(): array { // BAD: loads every order into memory at once $orders = wc_get_orders([ 'status' => ['wc-completed', 'wc-processing'], 'limit' => -1, 'return' => 'objects', ]);

$total = 0; $count = 0; foreach ($orders as $order) { $total += $order->get_total(); $count++; }

return ['total' => $total, 'count' => $count]; }

WooCommerce Redis Caching Setup

Problem Description

A mid-sized fashion retailer running WooCommerce is experiencing significant database load during peak hours. Their DBA has identified that MySQL is handling thousands of identical queries per minute — most of them fetching the same product data, shipping zone configuration, and tax rules over and over on every page request. The team has provisioned a Redis server at 127.0.0.1:6379 and wants it wired into WordPress as a persistent object cache.

The store runs WooCommerce on a standard LEMP stack. They want an experienced developer to produce a production-ready wp-config.php snippet that enables Redis object caching correctly. They're particularly concerned about cache correctness on the checkout path, where volatile data (sessions, cart counters) must never be served stale, and they want a sensible maximum TTL so memory doesn't grow unbounded.

Output Specification

Produce a file named redis-config.php containing only the define() constants that should be added to wp-config.php to configure the Redis object cache. The file should include brief inline comments explaining the purpose of each constant.

Also produce a short setup-notes.md explaining: 1. Which WP-CLI commands are needed to install and activate the required plugin and enable the cache 2. Why certain cache groups are excluded from Redis in this configuration