How to Set Up Related Products in WooCommerce

Problem Statement & Impact

WooCommerce installations processing high volumes of catalog requests frequently face architectural bottlenecks when rendering related products. By default, the WooCommerce engine executes dynamic SQL queries on every single product page load to fetch related items based on shared taxonomy terms—specifically categories and tags. In high-traffic environments, this default behavior introduces severe performance degradation, database row-locking, and exponential increases in Time to First Byte (TTFB).

The core technical challenge stems from the unindexed or poorly indexed taxonomy relationship tables (`wp_term_relationships`, `wp_term_taxonomy`, and `wp_posts`) within the MySQL or MariaDB storage engine. When a user requests a product page, WooCommerce triggers a query joining these tables alongside post metadata to calculate relevancy. Without strict object caching, persistent cache layers, or programmatic control over the selection algorithm, the database server experiences CPU exhaustion. This directly triggers memory limit errors, spikes slow query logs, and compromises the overall availability of the e-commerce infrastructure.

From a security and operational standpoint, unoptimized related product queries represent a vector for Denial of Service (DoS). Malicious actors can script concurrent HTTP GET requests targeting complex product pages with numerous taxonomy associations, forcing the MySQL query optimizer to execute expensive full table scans and temporary disk-based tables. Concurrently, failing to curate or programmatically limit related products leads to stale inventory recommendations, poor user engagement metrics, and reduced average order values (AOV). Addressing this requires a shift from native, unoptimized taxonomy queries to programmatic overrides, manual curation via post meta arrays, and execution-level caching.

Root Cause Analysis & Architecture

To understand why the default WooCommerce related products mechanism impacts site performance, we must examine the underlying execution lifecycle of the WordPress HTTP request. When a visitor navigates to a singular product post type (`product`), WordPress loads the template hierarchy and invokes the WooCommerce template loader, which eventually calls the `woocommerce_output_related_products()` hook.

Internally, WooCommerce executes the `WC_Query::get_related_products()` method. This method initiates a database query construct that looks structurally similar to the following unoptimized abstraction:

SELECT DISTINCT p.ID 
FROM wp_posts p
JOIN wp_term_relationships tr ON p.ID = tr.object_id
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy IN ('product_cat', 'product_tag')
  AND tt.term_id IN (12, 45, 67)
  AND p.post_type = 'product'
  AND p.post_status = 'publish'
  AND p.ID != 1042
ORDER BY RAND() 
LIMIT 3;

The performance bottleneck in this architecture is twofold:

• The `ORDER BY RAND()` Clause: The use of randomized sorting forces MySQL to create a temporary table in memory (or on disk if the dataset exceeds `tmp_table_size`), iterating through every matched row and assigning a random floating-point number before sorting and slicing the result set. On catalogues exceeding tens of thousands of products, this halts query execution and spikes thread concurrency.
• Absence of Native Caching Layers: Native transient API utilization for related product IDs is historically inconsistent across various WooCommerce extensions and custom themes. Every page view executes the database scan anew unless an object cache (such as Redis or Memcached) is actively intercepting database calls via plugins like Redis Object Cache.

Furthermore, managing related products via default category matching lacks fine-grained editorial control. Enterprise deployments require programmatic filters, explicit cross-sell/upsell definitions, or custom meta queries that bypass taxonomy inheritance entirely when performance demands it.

Step-by-Step Implementation & Remediation Guide

To establish a high-performance, secure, and structurally sound related products architecture, administrators must bypass native runtime randomization and implement explicit filtering via custom PHP functions or explicit database relationships. The following procedures outline programmatic optimization, query modification, and caching integration.

Step 1: Disable Native Randomized Sorting

The primary performance killer is `ORDER BY RAND()`. Eliminate this immediately by injecting a filter into your active theme’s `functions.php` file or a site-specific functionality plugin. This forces the query to use indexed post IDs rather than randomized sorting.

/
 * Remove ORDER BY RAND() from WooCommerce related products query
 * to prevent database CPU exhaustion and temporary table creation.
 */
add_filter( 'woocommerce_related_products_args', 'sitesuraksha_remove_random_related_products', 10, 1 );

function sitesuraksha_remove_random_related_products( $args ) {
    // Replace random ordering with post date or ID sorting
    $args['orderby'] = 'date';
    $args['order']   = 'DESC';
    return $args;
}

/
 * Force specific related products based on a custom post meta field,
 * bypassing heavy taxonomy evaluation.
 */
add_filter( 'woocommerce_related_products', 'sitesuraksha_custom_related_products_override', 20, 3 );

function sitesuraksha_custom_related_products_override( $related_posts, $product_id, $args ) {
    // Retrieve custom curated related product IDs stored in post meta
    $custom_related = get_post_meta( $product_id, '_sitesuraksha_manual_related_ids', true );

    if ( ! empty( $custom_related ) && is_array( $custom_related ) ) {
        // Sanitize IDs to ensure they are integers
        return array_map( 'absint', $custom_related );
    }

    return $related_posts;
}

Step 3: Implement WP-CLI Batch Assignment for Manual Meta

When managing thousands of products, manual dashboard entry is inefficient. Use WP-CLI to programmatically assign related product post meta at scale, avoiding database locking caused by prolonged admin session execution.

# Iterate through published products and assign baseline related product IDs via WP-CLI EVAL
wp eval '
$products = get_posts([
    "post_type" => "product",
    "posts_per_page" => -1,
    "fields" => "ids"
]);

foreach ($products as $id) {
    // Example: Assigning IDs 101, 102, 103 as standard related items
    update_post_meta($id, "_sitesuraksha_manual_related_ids", [101, 102, 103]);
}
echo "Successfully updated related product meta for all catalog items.\n";
'

Step 4: Optimize Database Indexes for Taxonomy Queries

If your architecture relies on default taxonomy lookups, ensure your database has explicit composite indexes to prevent full table scans on `wp_term_relationships`.

-- Run via MySQL CLI or phpMyAdmin to optimize taxonomy relationship lookups
ALTER TABLE wp_term_relationships ADD INDEX idx_term_object (term_taxonomy_id, object_id);
ALTER TABLE wp_postmeta ADD INDEX idx_post_meta_lookup (post_id, meta_key(50));

Production Hardening & Edge Cases

Deploying related product modifications to a high-traffic production environment requires accounting for caching conflicts and edge cases that can compromise site stability.

• Fragment Caching Conflicts: When using full-page page caches (e.g., Varnish, Nginx FastCGI Cache, or Cloudflare Enterprise cache), dynamic related product lists can cause cache pollution or display outdated inventory statuses. Ensure that related product HTML blocks are either rendered via AJAX (`wc-ajax=get_referenced_products`) or that your page cache respects short-lived TTLs (Time-To-Live) on singular product URIs.
• Out-of-Stock Product Exclusion: By default, WooCommerce may display out-of-stock items in related product grids, leading to dead ends for users. Enforce a strict inventory check within your query arguments to filter out zero-stock items:

add_filter( 'woocommerce_related_products_args', 'sitesuraksha_exclude_outofstock_related', 15, 1 );
function sitesuraksha_exclude_outofstock_related( $args ) {
    $args['meta_query'][] = array(
        'key'     => '_stock_status',
        'value'   => 'instock',
        'compare' => '='
    );
    return $args;
}

To validate your implementation in a staging environment before pushing to production, execute a load test using tools like Apache Bench (`ab`) or k6. Monitor the MySQL slow query log (`/var/log/mysql/mysql-slow.log`) to confirm that `ORDER BY RAND()` executions have dropped to zero and that query execution times remain under 15 milliseconds per request.

Diagnostic Verification & Checklist

Use the following verification matrix to audit your WooCommerce related products implementation and ensure optimal database performance, security hardening, and cache efficiency.