Commit 76f05a870d5 for woocommerce
commit 76f05a870d5069ac7bb25ba06774f17f5c7b4520
Author: Vladimir Reznichenko <kalessil@gmail.com>
Date: Tue May 19 11:16:49 2026 +0200
[Performance] Optimize slow SQL and high CPU usage by DB in product filter utility (#65119)
- Resolves the root cause by overriding IN-condition SQL generation for WP_Tax_Query.
- Adds an additional index to the wc_product_attributes_lookup table for improved SQL performance on medium-density curve.
diff --git a/plugins/woocommerce/changelog/performance-product-filterer-slow-sql b/plugins/woocommerce/changelog/performance-product-filterer-slow-sql
new file mode 100644
index 00000000000..e44833c4725
--- /dev/null
+++ b/plugins/woocommerce/changelog/performance-product-filterer-slow-sql
@@ -0,0 +1,4 @@
+Significance: minor
+Type: performance
+
+Optimize slow SQL and high CPU usage by DB in product filter utility.
diff --git a/plugins/woocommerce/src/Internal/ProductAttributesLookup/DataRegenerator.php b/plugins/woocommerce/src/Internal/ProductAttributesLookup/DataRegenerator.php
index c9d5af78b5a..fb96dd3a225 100644
--- a/plugins/woocommerce/src/Internal/ProductAttributesLookup/DataRegenerator.php
+++ b/plugins/woocommerce/src/Internal/ProductAttributesLookup/DataRegenerator.php
@@ -533,6 +533,7 @@ class DataRegenerator {
is_variation_attribute tinyint(1) NOT NULL,
in_stock tinyint(1) NOT NULL,
INDEX is_variation_attribute_term_id (is_variation_attribute, term_id),
+ INDEX taxonomy_term_id_in_stock_product_or_parent_id (taxonomy, term_id, in_stock, product_or_parent_id),
PRIMARY KEY ( `product_or_parent_id`, `term_id`, `product_id`, `taxonomy` ),
KEY product_id (product_id)
) $collate;";
diff --git a/plugins/woocommerce/src/Internal/ProductAttributesLookup/Filterer.php b/plugins/woocommerce/src/Internal/ProductAttributesLookup/Filterer.php
index f7ae2aaad36..c5c404a2015 100644
--- a/plugins/woocommerce/src/Internal/ProductAttributesLookup/Filterer.php
+++ b/plugins/woocommerce/src/Internal/ProductAttributesLookup/Filterer.php
@@ -166,8 +166,6 @@ class Filterer {
public function get_filtered_term_product_counts( $term_ids, $taxonomy, $query_type ) {
global $wpdb;
- $use_lookup_table = $this->filtering_via_lookup_table_is_active();
-
$tax_query = \WC_Query::get_main_tax_query();
$meta_query = \WC_Query::get_main_meta_query();
if ( 'or' === $query_type ) {
@@ -179,14 +177,22 @@ class Filterer {
}
$meta_query = new \WP_Meta_Query( $meta_query );
- $tax_query = new \WP_Tax_Query( $tax_query );
+ $tax_query = new TaxQuery( $tax_query );
- if ( $use_lookup_table ) {
+ if ( $this->filtering_via_lookup_table_is_active() ) {
$query = $this->get_product_counts_query_using_lookup_table( $tax_query, $meta_query, $taxonomy, $term_ids );
} else {
$query = $this->get_product_counts_query_not_using_lookup_table( $tax_query, $meta_query, $term_ids );
}
+ /**
+ * Customizes the SQL query that populates product counts in layered navigation.
+ *
+ * @since 10.9.0 the query was optimized to remove the join on term_relationships in favor of a nested select from term_relationships.
+ * @since 2.6.1
+ *
+ * @param array $query Query fragments (the available fragments are: select, from, join, where, group_by).
+ */
$query = apply_filters( 'woocommerce_get_filtered_term_product_counts_query', $query );
$query_sql = implode( ' ', $query );
@@ -306,7 +312,6 @@ class Filterer {
$query['where'] .= ' AND ' . $search_query_sql;
}
- $query['group_by'] = 'GROUP BY terms.term_id';
$query['group_by'] = "GROUP BY {$this->lookup_table_name}.term_id";
return $query;
diff --git a/plugins/woocommerce/src/Internal/ProductAttributesLookup/TaxQuery.php b/plugins/woocommerce/src/Internal/ProductAttributesLookup/TaxQuery.php
new file mode 100644
index 00000000000..f16d33e9513
--- /dev/null
+++ b/plugins/woocommerce/src/Internal/ProductAttributesLookup/TaxQuery.php
@@ -0,0 +1,44 @@
+<?php declare( strict_types=1 );
+
+namespace Automattic\WooCommerce\Internal\ProductAttributesLookup;
+
+/**
+ * Tax query class introduced to optimize SQL performance in bigger product/category catalogs.
+ */
+class TaxQuery extends \WP_Tax_Query {
+ /**
+ * Generates SQL JOIN and WHERE clauses for a "first-order" query clause.
+ *
+ * @since 10.9.0
+ *
+ * @param array $clause Query clause (passed by reference).
+ * @param array $parent_query Parent query array.
+ * @return array {
+ * Array containing JOIN and WHERE SQL clauses to append to a first-order query.
+ *
+ * @type string[] $join Array of SQL fragments to append to the main JOIN clause.
+ * @type string[] $where Array of SQL fragments to append to the main WHERE clause.
+ * }
+ */
+ public function get_sql_for_clause( &$clause, $parent_query ) {
+ global $wpdb;
+
+ // Optimization note: targeting only the 'IN' operator, where the default 'LEFT JOIN' causes performance issues.
+ if ( 'IN' !== $clause['operator'] ) {
+ return parent::get_sql_for_clause( $clause, $parent_query );
+ }
+
+ // Call the parent method so it does necessary cleanup with its private APIs.
+ $fallback = parent::get_sql_for_clause( $clause, $parent_query );
+ if ( array( '' ) === $fallback['join'] && array( '0 = 1' ) === $fallback['where'] ) {
+ return $fallback;
+ }
+
+ // Optimization note: 'fan-out LEFT JOIN' -> 'pre-materialized subquery' transition for better SQL performance.
+ $terms = implode( ',', array_map( 'absint', $clause['terms'] ) );
+ return array(
+ 'join' => array( '' ),
+ 'where' => array( "$this->primary_table.$this->primary_id_column IN ( SELECT object_id FROM $wpdb->term_relationships WHERE term_taxonomy_id IN ( $terms ) )" ),
+ );
+ }
+}
diff --git a/plugins/woocommerce/tests/php/src/Internal/ProductAttributesLookup/TaxQueryTest.php b/plugins/woocommerce/tests/php/src/Internal/ProductAttributesLookup/TaxQueryTest.php
new file mode 100644
index 00000000000..f511abbdaec
--- /dev/null
+++ b/plugins/woocommerce/tests/php/src/Internal/ProductAttributesLookup/TaxQueryTest.php
@@ -0,0 +1,65 @@
+<?php
+declare( strict_types = 1 );
+
+namespace Automattic\WooCommerce\Tests\Internal\ProductAttributesLookup;
+
+use Automattic\WooCommerce\Internal\ProductAttributesLookup\TaxQuery;
+use WC_Unit_Test_Case;
+
+/**
+ * Tests for the TaxQuery class.
+ */
+class TaxQueryTest extends WC_Unit_Test_Case {
+
+ /**
+ * Data provider.
+ */
+ public static function provide_get_sql_for_clause_scenarios(): array {
+ global $wpdb;
+
+ $term_id = get_term_by( 'slug', 'featured', 'product_visibility' )->term_taxonomy_id;
+
+ return array(
+ array(
+ 'clause' => array(
+ 'taxonomy' => 'product_visibility',
+ 'field' => 'term_taxonomy_id',
+ 'terms' => array( $term_id ),
+ 'operator' => 'IN',
+ 'include_children' => false,
+ ),
+ 'expected' => array(
+ 'join' => array( '' ),
+ 'where' => array( "{$wpdb->posts}.ID IN ( SELECT object_id FROM {$wpdb->term_relationships} WHERE term_taxonomy_id IN ( $term_id ) )" ),
+ ),
+ ),
+ array(
+ 'clause' => array(
+ 'taxonomy' => 'product_visibility',
+ 'field' => 'term_taxonomy_id',
+ 'terms' => array(),
+ 'operator' => 'IN',
+ 'include_children' => false,
+ ),
+ 'expected' => array(
+ 'join' => array( '' ),
+ 'where' => array( '0 = 1' ),
+ ),
+ ),
+ );
+ }
+
+ /**
+ * @dataProvider provide_get_sql_for_clause_scenarios
+ *
+ * @param array $clause Input clause.
+ * @param array $expected Expected result.
+ */
+ public function test_get_sql_for_clause( array $clause, array $expected ): void {
+ $query = new TaxQuery( array() );
+ $query->primary_table = 'wp_posts';
+ $query->primary_id_column = 'ID';
+
+ $this->assertSame( $expected, $query->get_sql_for_clause( $clause, array() ) );
+ }
+}