Commit 42726828ff for woocommerce
commit 42726828ffafa1a3b7e4bb0fd36318d5f786e350
Author: Vladimir Reznichenko <kalessil@gmail.com>
Date: Thu Feb 5 11:16:51 2026 +0100
[Performance] Optimize slow SQL on order confirmation page (#63043)
Use direct queries and a lookup table for best performance when checking the availability of downloadable products.
diff --git a/plugins/woocommerce/changelog/performance-63042-slow-SQL-order-confirmation b/plugins/woocommerce/changelog/performance-63042-slow-SQL-order-confirmation
new file mode 100644
index 0000000000..163580bc34
--- /dev/null
+++ b/plugins/woocommerce/changelog/performance-63042-slow-SQL-order-confirmation
@@ -0,0 +1,4 @@
+Significance: minor
+Type: performance
+
+Orders: optimized a slow SQL query executed on the order confirmation page, checking for the existence of downloadable products.
diff --git a/plugins/woocommerce/phpstan-baseline.neon b/plugins/woocommerce/phpstan-baseline.neon
index af0392b43c..52851381f9 100644
--- a/plugins/woocommerce/phpstan-baseline.neon
+++ b/plugins/woocommerce/phpstan-baseline.neon
@@ -58647,12 +58647,6 @@ parameters:
count: 1
path: src/Blocks/BlockTypes/OrderConfirmation/Downloads.php
- -
- message: '#^Function get_transient invoked with 2 parameters, 1 required\.$#'
- identifier: arguments.count
- count: 1
- path: src/Blocks/BlockTypes/OrderConfirmation/DownloadsWrapper.php
-
-
message: '#^Method Automattic\\WooCommerce\\Blocks\\BlockTypes\\OrderConfirmation\\DownloadsWrapper\:\:enqueue_data\(\) has no return type specified\.$#'
identifier: missingType.return
@@ -61023,12 +61017,6 @@ parameters:
count: 1
path: src/Blocks/BlockTypesController.php
- -
- message: '#^Method Automattic\\WooCommerce\\Blocks\\BlockTypesController\:\:delete_product_transients\(\) has no return type specified\.$#'
- identifier: missingType.return
- count: 1
- path: src/Blocks/BlockTypesController.php
-
-
message: '#^Method Automattic\\WooCommerce\\Blocks\\BlockTypesController\:\:init\(\) has no return type specified\.$#'
identifier: missingType.return
diff --git a/plugins/woocommerce/src/Blocks/BlockTypes/OrderConfirmation/DownloadsWrapper.php b/plugins/woocommerce/src/Blocks/BlockTypes/OrderConfirmation/DownloadsWrapper.php
index 69771984ee..97d53e26ca 100644
--- a/plugins/woocommerce/src/Blocks/BlockTypes/OrderConfirmation/DownloadsWrapper.php
+++ b/plugins/woocommerce/src/Blocks/BlockTypes/OrderConfirmation/DownloadsWrapper.php
@@ -20,30 +20,34 @@ class DownloadsWrapper extends AbstractOrderConfirmationBlock {
* @return boolean
*/
protected function store_has_downloadable_products() {
- $has_downloadable_product = get_transient( 'wc_blocks_has_downloadable_product', false );
+ global $wpdb;
- if ( false === $has_downloadable_product ) {
- $product_ids = get_posts(
- array(
- 'post_type' => 'product',
- 'numberposts' => 1,
- 'post_status' => 'publish',
- 'fields' => 'ids',
- // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
- 'meta_query' => array(
- array(
- 'key' => '_downloadable',
- 'value' => 'yes',
- 'compare' => '=',
- ),
- ),
- )
+ if ( get_option( 'woocommerce_product_lookup_table_is_generating' ) ) {
+ // The underlying SQL is slower than querying `wc_product_meta_lookup`, so caching is used for performance.
+ $has_downloadable_products = wp_cache_get( 'woocommerce_has_downloadable_products', 'woocommerce' );
+ if ( false === $has_downloadable_products ) {
+ $has_downloadable_products = (bool) $wpdb->get_var(
+ "SELECT posts.ID
+ FROM {$wpdb->posts} as posts
+ INNER JOIN {$wpdb->postmeta} as postmeta ON posts.ID = postmeta.post_id
+ WHERE
+ postmeta.meta_key = '_downloadable'
+ AND postmeta.meta_value = 'yes'
+ AND posts.post_type = 'product'
+ AND posts.post_status = 'publish'
+ LIMIT 1"
+ );
+ $has_downloadable_products = $has_downloadable_products ? 'yes' : 'no';
+ wp_cache_set( 'woocommerce_has_downloadable_products', $has_downloadable_products, 'woocommerce', HOUR_IN_SECONDS );
+ }
+ $has_downloadable_products = 'yes' === $has_downloadable_products;
+ } else {
+ $has_downloadable_products = (bool) $wpdb->get_var(
+ "SELECT product_id FROM {$wpdb->wc_product_meta_lookup} WHERE downloadable = 1 LIMIT 1",
);
- $has_downloadable_product = ! empty( $product_ids );
- set_transient( 'wc_blocks_has_downloadable_product', $has_downloadable_product ? '1' : '0', MONTH_IN_SECONDS );
}
- return (bool) $has_downloadable_product;
+ return $has_downloadable_products;
}
/**
diff --git a/plugins/woocommerce/src/Blocks/BlockTypesController.php b/plugins/woocommerce/src/Blocks/BlockTypesController.php
index 0216302863..afd97ad90a 100644
--- a/plugins/woocommerce/src/Blocks/BlockTypesController.php
+++ b/plugins/woocommerce/src/Blocks/BlockTypesController.php
@@ -61,7 +61,6 @@ final class BlockTypesController {
add_filter( 'render_block', array( $this, 'add_data_attributes' ), 10, 2 );
add_action( 'woocommerce_login_form_end', array( $this, 'redirect_to_field' ) );
add_filter( 'widget_types_to_hide_from_legacy_widget_block', array( $this, 'hide_legacy_widgets_with_block_equivalent' ) );
- add_action( 'woocommerce_delete_product_transients', array( $this, 'delete_product_transients' ) );
add_filter( 'register_block_type_args', array( $this, 'enqueue_block_style_for_classic_themes' ), 10, 2 );
add_filter( 'block_core_breadcrumbs_post_type_settings', array( $this, 'set_product_breadcrumbs_preferred_taxonomy' ), 10, 3 );
add_filter( 'block_core_breadcrumbs_items', array( $this, 'apply_woocommerce_breadcrumb_filters' ), 10, 1 );
@@ -360,9 +359,12 @@ final class BlockTypesController {
/**
* Delete product transients when a product is deleted.
+ *
+ * @deprecated since 10.6.0
+ * @return void
*/
public function delete_product_transients() {
- delete_transient( 'wc_blocks_has_downloadable_product' );
+ wc_deprecated_function( __METHOD__, '10.6.0' );
}
/**
diff --git a/plugins/woocommerce/tests/php/src/Blocks/BlockTypes/OrderConfirmation/DownloadsWrapper.php b/plugins/woocommerce/tests/php/src/Blocks/BlockTypes/OrderConfirmation/DownloadsWrapper.php
new file mode 100644
index 0000000000..85d09a355f
--- /dev/null
+++ b/plugins/woocommerce/tests/php/src/Blocks/BlockTypes/OrderConfirmation/DownloadsWrapper.php
@@ -0,0 +1,101 @@
+<?php declare( strict_types = 1 );
+
+namespace Automattic\WooCommerce\Tests\Blocks\BlockTypes\OrderConfirmation;
+
+use Automattic\WooCommerce\Blocks\BlockTypes\OrderConfirmation\DownloadsWrapper as DownloadsWrapperClass;
+
+/**
+ * Test DownloadsWrapper class.
+ */
+final class DownloadsWrapper extends \WP_UnitTestCase {
+ /**
+ * Perform products/options/cache cleanup.
+ */
+ public function tear_down() {
+ global $wpdb;
+
+ /** @var \WC_Product[] $products */
+ $products = ( new \WC_Product_Query() )->get_products();
+ foreach ( $products as $product ) {
+ $product->delete();
+ }
+ $wpdb->query( "TRUNCATE TABLE {$wpdb->wc_product_meta_lookup}" );
+
+ delete_option( 'woocommerce_product_lookup_table_is_generating' );
+ wp_cache_delete( 'woocommerce_has_downloadable_products', 'woocommerce' );
+
+ parent::tear_down();
+ }
+
+ /**
+ * Test `store_has_downloadable_products`: query product meta lookup table.
+ *
+ * @dataProvider provider_downloadable_products
+ * @param \WC_Product $product The product instance.
+ */
+ public function test_store_has_downloadable_products_via_product_meta_lookup_table_with_downloadable( \WC_Product $product ): void {
+ $proxy = new class() extends DownloadsWrapperClass {
+ // phpcs:ignore Squiz.Commenting.FunctionComment.Missing
+ public function __construct() {
+ }
+ // phpcs:ignore Squiz.Commenting.FunctionComment.Missing
+ public function store_has_downloadable_products_proxy(): bool {
+ return $this->store_has_downloadable_products();
+ }
+ };
+
+ $this->assertSame( $product->is_downloadable(), $proxy->store_has_downloadable_products_proxy() );
+ }
+
+ /**
+ * A data provider.
+ *
+ * @return array
+ */
+ public function provider_downloadable_products(): array {
+ return array(
+ array( \WC_Helper_Product::create_simple_product( true, array( 'downloadable' => true ) ) ),
+ array( \WC_Helper_Product::create_simple_product( true, array( 'downloadable' => false ) ) ),
+ );
+ }
+
+ /**
+ * Test `store_has_downloadable_products`: query post meta table.
+ */
+ public function test_store_has_downloadable_products_via_posts_meta_table(): void {
+ $proxy = new class() extends DownloadsWrapperClass {
+ // phpcs:ignore Squiz.Commenting.FunctionComment.Missing
+ public function __construct() {
+ }
+ // phpcs:ignore Squiz.Commenting.FunctionComment.Missing
+ public function store_has_downloadable_products_proxy(): bool {
+ return $this->store_has_downloadable_products();
+ }
+ };
+ add_option( 'woocommerce_product_lookup_table_is_generating', 'yes' );
+
+ \WC_Helper_Product::create_simple_product( true, array( 'downloadable' => true ) );
+ $this->assertTrue( $proxy->store_has_downloadable_products_proxy() );
+ $this->assertSame( 'yes', wp_cache_get( 'woocommerce_has_downloadable_products', 'woocommerce' ) );
+ }
+
+ /**
+ * Test `store_has_downloadable_products`: picking up the cached value.
+ */
+ public function test_store_has_downloadable_products_via_cache(): void {
+ $proxy = new class() extends DownloadsWrapperClass {
+ // phpcs:ignore Squiz.Commenting.FunctionComment.Missing
+ public function __construct() {
+ }
+ // phpcs:ignore Squiz.Commenting.FunctionComment.Missing
+ public function store_has_downloadable_products_proxy(): bool {
+ return $this->store_has_downloadable_products();
+ }
+ };
+ add_option( 'woocommerce_product_lookup_table_is_generating', 'yes' );
+ wp_cache_set( 'woocommerce_has_downloadable_products', 'no', 'woocommerce' );
+
+ \WC_Helper_Product::create_simple_product( true, array( 'downloadable' => true ) );
+ $this->assertFalse( $proxy->store_has_downloadable_products_proxy() );
+ }
+}