Stop Writing WooCommerce Queries That Crash Your Site

I got a Slack message from one of our project managers. A client’s WooCommerce shop—a big one, thousands of products—was crawling. Product pages were taking forever to load, sometimes hitting a gateway timeout. Total mess. They were convinced it was a server issue, but I’ve seen this movie before. It’s almost always a bad query.

After digging around for five minutes, I found it. A custom “Customers Also Bought” widget, built by a junior dev, was firing off a ridiculous number of database calls on every single page load. The first step to fix this was to figure out how to optimize the WooCommerce query that was bringing the site to its knees.

The N+1 Query Bomb Hiding in Plain Sight

Here’s the kicker. The original code looked logical, which is why it passed a code review for a staging site that only had 20 products. It would get the current product, find orders containing that product, then loop through those orders to find *other* products purchased. For each product found, it ran wc_get_product() inside the loop. On a site with two orders, that’s no big deal. On a site with 20,000 orders? It’s a self-inflicted denial of service attack. A classic N+1 query problem.

My first instinct, the lazy one, was to just cache the whole block in a transient. And yeah, that would have “fixed” it for a few minutes. But that’s just putting a band-aid on a bullet wound. The real fix had to be at the query level. You have to stop the bleeding, not just mop up the floor.

Instead of running a query for every single product inside a loop, you fetch all the data you need in one, maybe two, efficient database calls. Get all the relevant order IDs, then get all the product IDs from those orders, and then process the results with PHP. Here’s the right way to do it.

function get_frequently_bought_together_products( $product_id ) {
    global $wpdb;

    // Get all order IDs that contain the main product
    $order_ids = $wpdb->get_col( $wpdb->prepare( "
        SELECT order_id
        FROM {$wpdb->prefix}woocommerce_order_items
        WHERE order_item_id IN (
            SELECT order_item_id
            FROM {$wpdb->prefix}woocommerce_order_itemmeta
            WHERE meta_key = '_product_id' AND meta_value = %d
        )
    ", $product_id ) );

    if ( empty( $order_ids ) ) {
        return [];
    }

    $order_ids_placeholder = implode( ',', array_fill( 0, count( $order_ids ), '%d' ) );

    // Get all product IDs from those orders, excluding the main product
    $product_ids = $wpdb->get_col( $wpdb->prepare( "
        SELECT DISTINCT meta_value
        FROM {$wpdb->prefix}woocommerce_order_itemmeta
        WHERE meta_key = '_product_id'
        AND order_item_id IN (
            SELECT order_item_id FROM {$wpdb->prefix}woocommerce_order_items WHERE order_id IN ( $order_ids_placeholder )
        )
        AND meta_value != %d
    ", array_merge( $order_ids, [$product_id] ) ) );

    return $product_ids;
}

This approach hits the database twice, no matter how many products or orders there are. It’s predictable. It’s stable. It doesn’t fall apart under pressure. This whole exercise was a good teaching moment, and it reminded me of a great concept I saw over at carlalexander.ca about the importance of teaching what you know. You don’t just fix the code; you explain the why so the whole team gets better.

So, What’s the Point?

The lesson here is simple: code that works on a small scale is not the same as code that works in production. A proper solution has to account for load. You can’t just write a loop and call it a day. You have to think about how many times that loop is going to run and what it’s doing each time. One database query that returns 1000 results is infinitely better than 1000 separate queries. Period.

Look, this stuff gets complicated fast. If you’re tired of debugging someone else’s mess and just want your site to work, drop my team a line. We’ve probably seen it before.