Commit cda1a93d21e for woocommerce
commit cda1a93d21eae7dbfb8eff655dcac3235fb2b95c
Author: Vladimir Reznichenko <kalessil@gmail.com>
Date: Wed Mar 4 11:35:39 2026 +0100
[Performance] Blocks: pin the number of DB lookup SQLs for fetching block templates (#63399)
Reduces the number of SQL queries on store pages (this PR's context is product/cart/checkout trail).
diff --git a/plugins/woocommerce/changelog/performance-63118-reduce-sqls-number-iteration-6 b/plugins/woocommerce/changelog/performance-63118-reduce-sqls-number-iteration-6
new file mode 100644
index 00000000000..5399bbe6356
--- /dev/null
+++ b/plugins/woocommerce/changelog/performance-63118-reduce-sqls-number-iteration-6
@@ -0,0 +1,4 @@
+Significance: patch
+Type: performance
+
+Blocks: pin the number of SQL queries required to retrieve block templates from the database (2 per template type).
diff --git a/plugins/woocommerce/includes/class-wc-post-data.php b/plugins/woocommerce/includes/class-wc-post-data.php
index ffe9bc552d3..56fa862a165 100644
--- a/plugins/woocommerce/includes/class-wc-post-data.php
+++ b/plugins/woocommerce/includes/class-wc-post-data.php
@@ -38,6 +38,7 @@ class WC_Post_Data {
*/
public static function init() {
add_action( 'clean_post_cache', array( __CLASS__, 'invalidate_products_last_modified' ), 10, 2 );
+ add_action( 'clean_post_cache', array( __CLASS__, 'invalidate_db_block_templates_cache' ), 10, 2 );
add_filter( 'post_type_link', array( __CLASS__, 'variation_post_link' ), 10, 2 );
add_action( 'shutdown', array( __CLASS__, 'do_deferred_product_sync' ), 10 );
add_action( 'set_object_terms', array( __CLASS__, 'force_default_term' ), 10, 5 );
@@ -179,6 +180,24 @@ class WC_Post_Data {
}
}
+ /**
+ * Invalidates cache entries related to fetching block templates from DB. Please reference to
+ * `Utils\BlockTemplateUtils::get_block_templates_from_db` for further details.
+ *
+ * @param int $post_id Post ID.
+ * @param \WP_Post $post Post object.
+ *
+ * @internal
+ * @since 10.7.0
+ *
+ * @return void
+ */
+ public static function invalidate_db_block_templates_cache( $post_id, $post ): void {
+ if ( $post instanceof \WP_Post && in_array( $post->post_type, array( 'wp_template_part', 'wp_template' ), true ) ) {
+ wp_cache_delete( $post->post_type . '-ids', 'woocommerce_blocks' );
+ }
+ }
+
/**
* Handle type changes.
*
diff --git a/plugins/woocommerce/phpstan-baseline.neon b/plugins/woocommerce/phpstan-baseline.neon
index 38396fe12d8..b23033204ff 100644
--- a/plugins/woocommerce/phpstan-baseline.neon
+++ b/plugins/woocommerce/phpstan-baseline.neon
@@ -56476,12 +56476,6 @@ parameters:
count: 1
path: src/Blocks/Utils/BlockTemplateUtils.php
- -
- message: '#^Method Automattic\\WooCommerce\\Blocks\\Utils\\BlockTemplateUtils\:\:get_block_templates_from_db\(\) should return array\<WP_Block_Template\> but returns array\<WP_Block_Template\|WP_Error\>\.$#'
- identifier: return.type
- count: 1
- path: src/Blocks/Utils/BlockTemplateUtils.php
-
-
message: '#^Method Automattic\\WooCommerce\\Blocks\\Utils\\BlockTemplateUtils\:\:get_template\(\) has invalid return type Automattic\\WooCommerce\\Blocks\\Utils\\AbstractTemplate\.$#'
identifier: class.notFound
@@ -56506,12 +56500,6 @@ parameters:
count: 1
path: src/Blocks/Utils/BlockTemplateUtils.php
- -
- message: '#^Parameter \#1 \$post of static method Automattic\\WooCommerce\\Blocks\\Utils\\BlockTemplateUtils\:\:build_template_result_from_post\(\) expects WP_Post, int\|WP_Post given\.$#'
- identifier: argument.type
- count: 1
- path: src/Blocks/Utils/BlockTemplateUtils.php
-
-
message: '#^Parameter \#1 \$str of function ucwords expects string, string\|null given\.$#'
identifier: argument.type
diff --git a/plugins/woocommerce/src/Blocks/BlockTypes/Checkout.php b/plugins/woocommerce/src/Blocks/BlockTypes/Checkout.php
index 510a74ed052..4cbc98d0ef0 100644
--- a/plugins/woocommerce/src/Blocks/BlockTypes/Checkout.php
+++ b/plugins/woocommerce/src/Blocks/BlockTypes/Checkout.php
@@ -443,6 +443,16 @@ class Checkout extends AbstractBlock {
FILTER_VALIDATE_BOOLEAN
)
);
+ // Optimization note: reduce the number of SQLs required to fetch the options in the lines below.
+ wp_prime_option_caches(
+ array(
+ 'woocommerce_enable_checkout_login_reminder',
+ 'woocommerce_tax_display_cart', // This one is autoloaded, but we add it here for clarity.
+ 'woocommerce_tax_total_display',
+ 'woocommerce_ship_to_destination',
+ 'woocommerce_registration_generate_password',
+ )
+ );
$this->asset_data_registry->add( 'checkoutShowLoginReminder', filter_var( get_option( 'woocommerce_enable_checkout_login_reminder' ), FILTER_VALIDATE_BOOLEAN ) );
$this->asset_data_registry->add( 'displayCartPricesIncludingTax', 'incl' === get_option( 'woocommerce_tax_display_cart' ) );
$this->asset_data_registry->add( 'displayItemizedTaxes', 'itemized' === get_option( 'woocommerce_tax_total_display' ) );
diff --git a/plugins/woocommerce/src/Blocks/Utils/BlockTemplateUtils.php b/plugins/woocommerce/src/Blocks/Utils/BlockTemplateUtils.php
index fb9ca1186cf..2081d5c25cd 100644
--- a/plugins/woocommerce/src/Blocks/Utils/BlockTemplateUtils.php
+++ b/plugins/woocommerce/src/Blocks/Utils/BlockTemplateUtils.php
@@ -714,32 +714,59 @@ class BlockTemplateUtils {
* @return \WP_Block_Template[] An array of found templates.
*/
public static function get_block_templates_from_db( $slugs = array(), $template_type = 'wp_template' ) {
- $check_query_args = array(
- 'post_type' => $template_type,
- 'posts_per_page' => -1,
- 'no_found_rows' => true,
- 'tax_query' => array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_tax_query
+ static $request_level_cache = array();
+
+ // Optimization note: first query, which optimized for fetching IDs, to minimize temporary/filesort overhead.
+ $theme = get_stylesheet();
+ $ids = wp_cache_get( $template_type . '-ids', 'woocommerce_blocks' );
+ if ( ! isset( $ids[ $theme ] ) ) {
+ $ids = false === $ids ? array() : $ids;
+ // 'post__not_in' directs the query to use the `type_status_date` index on the posts table. Omitting this may
+ // impact index usage on some systems and result in `Using join buffer (flat, BNL join)`.
+ // As the table grows, the number of template type entries stays small, which helps maintain strong query performance.
+ $ids[ $theme ] = ( new \WP_Query(
array(
- 'taxonomy' => 'wp_theme',
- 'field' => 'name',
- 'terms' => array( self::DEPRECATED_PLUGIN_SLUG, self::PLUGIN_SLUG, get_stylesheet() ),
- ),
- ),
- );
+ 'post_type' => $template_type,
+ 'post__not_in' => array( 0 ),
+ 'posts_per_page' => -1,
+ 'fields' => 'ids',
+ 'tax_query' => array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_tax_query
+ array(
+ 'taxonomy' => 'wp_theme',
+ 'field' => 'name',
+ 'terms' => array( self::DEPRECATED_PLUGIN_SLUG, self::PLUGIN_SLUG, $theme ),
+ ),
+ ),
+ )
+ ) )->posts;
+ // 12 hours is half of default nonce lifetime, if out of sync, templates operating with nonce will keep running.
+ wp_cache_set( $template_type . '-ids', $ids, 'woocommerce_blocks', 12 * HOUR_IN_SECONDS );
+ $request_level_cache[ $template_type ][ $theme ] = null;
+ }
- if ( is_array( $slugs ) && count( $slugs ) > 0 ) {
- $check_query_args['post_name__in'] = $slugs;
+ // Optimization note: second query, which optimized for fetching templates data with caches priming API.
+ if ( null === ( $request_level_cache[ $template_type ][ $theme ] ?? null ) ) {
+ $request_level_cache[ $template_type ][ $theme ] = array();
+ if ( ! empty( $ids[ $theme ] ) ) {
+ _prime_post_caches( $ids[ $theme ], false, false );
+ $request_level_cache[ $template_type ][ $theme ] = array_filter( array_map( 'get_post', $ids[ $theme ] ) );
+ }
}
- $check_query = new \WP_Query( $check_query_args );
- $saved_woo_templates = $check_query->posts;
+ // Optimization note: populate template objects; optimized for subsequent calls, without spawning consequent SQLs.
+ $saved_templates = $request_level_cache[ $template_type ][ $theme ];
+ if ( ! empty( $saved_templates ) && is_array( $slugs ) && array() !== $slugs ) {
+ $slugs = array_map( 'sanitize_title', $slugs );
+ $saved_templates = array_filter( $saved_templates, fn( $template ) => in_array( $template->post_name, $slugs, true ) );
+ }
+ if ( ! empty( $saved_templates ) ) {
+ $block_templates = array_map( fn( $template ) => self::build_template_result_from_post( $template ), $saved_templates );
+ $block_templates = array_values( array_filter( $block_templates, fn( $template ) => $template instanceof \WP_Block_Template ) );
- return array_map(
- function ( $saved_woo_template ) {
- return self::build_template_result_from_post( $saved_woo_template );
- },
- $saved_woo_templates
- );
+ return $block_templates;
+ }
+
+ return array();
}
/**
diff --git a/plugins/woocommerce/tests/php/src/Blocks/Utils/BlockTemplateUtilsTest.php b/plugins/woocommerce/tests/php/src/Blocks/Utils/BlockTemplateUtilsTest.php
index 31e6c13b44b..27a7b4e0b91 100644
--- a/plugins/woocommerce/tests/php/src/Blocks/Utils/BlockTemplateUtilsTest.php
+++ b/plugins/woocommerce/tests/php/src/Blocks/Utils/BlockTemplateUtilsTest.php
@@ -318,6 +318,58 @@ class BlockTemplateUtilsTest extends WP_UnitTestCase {
delete_option( Options::WC_BLOCK_USE_BLOCKIFIED_PRODUCT_GRID_BLOCK_AS_TEMPLATE );
}
+ /**
+ * Test `get_block_templates_from_db`: workflow and properly handling input parameters.
+ */
+ public function test_get_block_templates_from_db(): void {
+ $now = time();
+ $theme = get_stylesheet();
+
+ $date = gmdate( 'Y-m-d H:i:s', $now - 1 );
+ $attributes = array(
+ 'post_name' => 'slug-1',
+ 'post_type' => 'wp_template',
+ 'post_title' => 'title',
+ 'post_status' => 'publish',
+ 'post_date' => $date,
+ 'post_date_gmt' => get_gmt_from_date( $date ),
+
+ );
+ $template_slug_1 = $this->createPost( $attributes, BlockTemplateUtils::PLUGIN_SLUG );
+
+ $date = gmdate( 'Y-m-d H:i:s', $now );
+ $attributes = array(
+ 'post_name' => 'slug',
+ 'post_type' => 'wp_template',
+ 'post_title' => 'title',
+ 'post_status' => 'publish',
+ 'post_date' => $date,
+ 'post_date_gmt' => get_gmt_from_date( $date ),
+
+ );
+ $template_slug = $this->createPost( $attributes, BlockTemplateUtils::PLUGIN_SLUG );
+
+ // Verify fetching all templates and caches population correctness.
+ $templates = BlockTemplateUtils::get_block_templates_from_db();
+ $this->assertSame( array( $template_slug->ID, $template_slug_1->ID ), wp_cache_get( 'wp_template-ids', 'woocommerce_blocks' )[ $theme ] ?? null );
+ $this->assertSame( array( 'slug', 'slug-1' ), array_column( $templates, 'slug' ) );
+
+ // Verify request-level cache hit handling correctness.
+ $templates = BlockTemplateUtils::get_block_templates_from_db( array( 'slug' ), 'wp_template' );
+ $this->assertSame( array( 'slug' ), array_column( $templates, 'slug' ) );
+
+ // Verify request-level cache miss handling correctness: no templates with specified slug.
+ $templates = BlockTemplateUtils::get_block_templates_from_db( array( 'oops' ), 'wp_template_part' );
+ $this->assertCount( 0, $templates );
+
+ // Verify request-level cache miss handling correctness: no templates with the specified type.
+ $templates = BlockTemplateUtils::get_block_templates_from_db( array( 'slug' ), 'wp_template_part' );
+ $this->assertSame( array(), wp_cache_get( 'wp_template_part-ids', 'woocommerce_blocks' )[ $theme ] ?? null );
+ $this->assertCount( 0, $templates );
+
+ wp_cache_delete_multiple( array( 'wp_template-ids', 'wp_template_part-ids' ), 'woocommerce_blocks' );
+ }
+
/**
* Runs the migration that happen after a plugin update
*
@@ -334,10 +386,13 @@ class BlockTemplateUtilsTest extends WP_UnitTestCase {
* @param array $post Post data.
* @param string $theme Theme name.
*
- * @return WP_Post
+ * @return \WP_Post
*/
private function createPost( $post, $theme ) {
- $term = wp_insert_term( $theme, 'wp_theme' );
+ $term = get_term_by( 'slug', $theme, 'wp_theme', ARRAY_A );
+ if ( ! $term ) {
+ $term = wp_insert_term( $theme, 'wp_theme' );
+ }
$post_id = wp_insert_post( $post );
wp_set_post_terms( $post_id, array( $term['term_id'] ), 'wp_theme' );