WooCommerce Points and Rewards

On stores with high-variation counts (e.g., 200+ variations), the get_category_points() method triggers a redundant database query for every variation during the get_available_variations() loop. For a product with 246 variations, this results in 246 identical SQL queries to wp_termmeta, causing significant TTFB delay and unnecessary DB load.

in: WC_Points_Rewards_Product::get_category_points()
wp-content/plugins/woocommerce-points-and-rewards/includes/class-wc-points-rewards-product.php:461

Query:
SELECT term_id AS category_id, meta_value AS points
FROM wp_termmeta
WHERE meta_key = ‘_wc_points_earned’
AND term_id IN ( 310,383,9,267,47 );

runs multiple (hundreds) of times depending on the number of variations

I have developed a patch that implements a static cache (memoization) within the function. Since variations within the same parent product share the same categories, we can cache the result of the first lookup in memory. This reduces the complexity from $O(N)$ queries to $O(1)$ query.

Patch:

— includes/class-wc-points-rewards-product.php
+++ includes/class-wc-points-rewards-product.php
@@ -456,6 +456,11 @@
}

$category_ids_string = implode( ‘,’, array_map( ‘intval’, $category_ids ) );
+
+ static $points_cache = array();
+ if ( isset( $points_cache[ $category_ids_string ] ) ) {
+ return $points_cache[ $category_ids_string ];
+ }

$category_points_query = “SELECT term_id AS category_id, meta_value AS points FROM {$wpdb->termmeta} WHERE meta_key = ‘_wc_points_earned’ AND term_id IN ( $category_ids_string );”;
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
@@ -487,4 +492,5 @@
}
}

+ $points_cache[ $category_ids_string ] = $category_points;
return $category_points;