Commit be0c91f0f88 for woocommerce
commit be0c91f0f885f6e731ed93a15d1b48adb02ff844
Author: Michal Iwanow <4765119+mcliwanow@users.noreply.github.com>
Date: Tue Jun 23 12:26:16 2026 +0200
[HPOS] Speed up order admin list queries on large stores (#65663)
* [HPOS] Rewrite multi-status order list queries as per-status UNIONs
A query filtering by multiple statuses and ordered by creation date
(the default WooCommerce > Orders list query) cannot be served by a
single index: status IN (...) prevents type_status_date from providing
a global date_created_gmt ordering. On large stores, and depending on
optimizer heuristics (e.g. MySQL's prefer_ordering_index switch being
disabled), the optimizer may choose a plan that examines millions of
rows to produce a single page of results.
Rewriting the query as a UNION ALL of single-status branches lets each
branch be fully served - filtering and ordering - by type_status_date
regardless of optimizer behavior or status selectivity. On a 12M-row
production table this took the first-page query from ~12-21s to ~3ms.
The rewrite only applies when the final clauses are byte-identical to
those generated purely by the 'type' and 'status' query args, when the
woocommerce_orders_table_query_sql filter returned the query unchanged,
and within branch-count and pagination-depth caps. It can be disabled
via the new woocommerce_orders_table_query_status_union_optimization
filter.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
* [HPOS] Keep empty-search order list requests on the cached-count path
The order search form always submits its filter dropdown value, and
set_search_args() recorded it even when the search term was empty. The
stray 'search_filter' query arg (inert without a term, since the search
query is only built when 's' is non-empty) disqualified the request
from the cached-count fast path, so every page load of such URLs ran
SELECT COUNT(*) over the full orders table - seconds on stores with
millions of orders.
Only set 'search_filter' when a search term is present, so empty-search
requests use cached per-status order counts like the plain list view.
Stray key introduced in #43356; became a performance issue when the
cached-count fast path was added in #54034.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
* [HPOS] Gate status union rewrite by store size and extract it into a dedicated class
Extract the HPOS status union rewrite into a dedicated
OrdersTableStatusUnionQuery class and gate it by store size: the
rewrite is now enabled by default only when cached order counts (read
via OrderCountCache without recomputing) for the queried types and
statuses reach 500,000, since smaller stores pay the union's small
constant cost without needing it. The
woocommerce_orders_table_query_status_union_optimization filter now
receives that computed default so it can force-enable or disable the
rewrite either way, and is checked before the more expensive
WHERE-clause comparison. Also lower the maximum pagination depth for
the rewrite from 10,000 to 2,000 rows per branch.
Addresses review feedback in #65663.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
* Refactor HPOS status union query and trim verbose comments
* Enable status union rewrite from partial cached order counts
* Use OrderUtil::get_count_for_type to gate status union rewrite
* Simplify HPOS order list changelog entries
---------
Co-authored-by: Claude Fable 5 <noreply@anthropic.com>
diff --git a/plugins/woocommerce/changelog/hpos-orders-status-union-rewrite b/plugins/woocommerce/changelog/hpos-orders-status-union-rewrite
new file mode 100644
index 00000000000..256f7afffa6
--- /dev/null
+++ b/plugins/woocommerce/changelog/hpos-orders-status-union-rewrite
@@ -0,0 +1,4 @@
+Significance: patch
+Type: performance
+
+HPOS: speed up the order admin list on large stores by rewriting multi-status order queries as per-status UNIONs.
diff --git a/plugins/woocommerce/src/Internal/Admin/Orders/ListTable.php b/plugins/woocommerce/src/Internal/Admin/Orders/ListTable.php
index a0eb20d53e6..b3daab966a4 100644
--- a/plugins/woocommerce/src/Internal/Admin/Orders/ListTable.php
+++ b/plugins/woocommerce/src/Internal/Admin/Orders/ListTable.php
@@ -577,11 +577,13 @@ class ListTable extends WP_List_Table {
if ( ! empty( $search_term ) ) {
$this->order_query_args['s'] = $search_term;
$this->has_filter = true;
- }
- $filter = trim( sanitize_text_field( $this->request['search-filter'] ) );
- if ( ! empty( $filter ) ) {
- $this->order_query_args['search_filter'] = $filter;
+ // 'search_filter' is inert without a search term, but setting it (the form always submits the dropdown)
+ // would disqualify the request from the cached-count fast path in prepare_items() and force a COUNT.
+ $filter = trim( sanitize_text_field( $this->request['search-filter'] ) );
+ if ( ! empty( $filter ) ) {
+ $this->order_query_args['search_filter'] = $filter;
+ }
}
}
diff --git a/plugins/woocommerce/src/Internal/DataStores/Orders/OrdersTableQuery.php b/plugins/woocommerce/src/Internal/DataStores/Orders/OrdersTableQuery.php
index 589556df85c..a9f11ec2a34 100644
--- a/plugins/woocommerce/src/Internal/DataStores/Orders/OrdersTableQuery.php
+++ b/plugins/woocommerce/src/Internal/DataStores/Orders/OrdersTableQuery.php
@@ -911,19 +911,38 @@ class OrdersTableQuery {
$this->sql = "SELECT $fields FROM $orders_table $join WHERE $where $groupby $orderby $limits";
+ $filtered_sql = $this->sql;
if ( ! $this->suppress_filters ) {
/**
* Filters the completed SQL query.
*
+ * Note: queries left unmodified by this filter may later be rewritten for performance (see
+ * OrdersTableStatusUnionQuery), in which case the SQL received here is not the SQL that ends up
+ * being executed. Returning a modified query from this filter disables any such rewrite.
+ *
* @since 7.9.0
*
* @param string $sql The complete SQL query.
* @param OrdersTableQuery $query The OrdersTableQuery instance (passed by reference).
* @param array $args Query args.
*/
- $this->sql = apply_filters_ref_array( 'woocommerce_orders_table_query_sql', array( $this->sql, &$this, $this->args ) );
+ $filtered_sql = apply_filters_ref_array( 'woocommerce_orders_table_query_sql', array( $this->sql, &$this, $this->args ) );
}
+ if ( $filtered_sql === $this->sql ) {
+ // On large HPOS stores this multi-status, date-ordered query can get a slow plan (scanning millions of
+ // rows); rewriting it as a UNION of single-status queries lets the type_status_date index serve each
+ // branch. Only attempted when no 'woocommerce_orders_table_query_sql' callback changed the query. See
+ // OrdersTableStatusUnionQuery.
+ $status_union_sql = ( new OrdersTableStatusUnionQuery( $this ) )->get_sql(
+ compact( 'fields', 'join', 'where', 'groupby', 'orderby', 'limits' ),
+ $this->suppress_filters
+ );
+ $filtered_sql = $status_union_sql ?? $this->sql;
+ }
+
+ $this->sql = $filtered_sql;
+
$this->build_count_query( $fields, $join, $where, $groupby );
}
diff --git a/plugins/woocommerce/src/Internal/DataStores/Orders/OrdersTableStatusUnionQuery.php b/plugins/woocommerce/src/Internal/DataStores/Orders/OrdersTableStatusUnionQuery.php
new file mode 100644
index 00000000000..03e01a77aaf
--- /dev/null
+++ b/plugins/woocommerce/src/Internal/DataStores/Orders/OrdersTableStatusUnionQuery.php
@@ -0,0 +1,300 @@
+<?php
+/**
+ * OrdersTableStatusUnionQuery class file.
+ */
+
+declare( strict_types=1 );
+
+namespace Automattic\WooCommerce\Internal\DataStores\Orders;
+
+use Automattic\WooCommerce\Utilities\OrderUtil;
+
+defined( 'ABSPATH' ) || exit;
+
+/**
+ * Rewrites a "multiple statuses, ordered by creation date" order query (such as the default order admin list
+ * screen query) as a UNION ALL of single-status queries.
+ *
+ * `status IN (...)` prevents the `type_status_date` index from serving a global `date_created_gmt` ordering, so on
+ * large stores the optimizer may pick a plan that scans millions of rows for a single page. One branch per (type,
+ * status) pair is fully served — filter and order — by `type_status_date`, leaving the outer query to merge a few
+ * pre-sorted rows.
+ *
+ * Eligibility (exact clause match) and the store-size gate are documented at the methods that enforce them
+ * (get_sql() and is_enabled()).
+ */
+class OrdersTableStatusUnionQuery {
+
+ /**
+ * Maximum number of UNION branches (one per type/status pair). Queries needing more branches than this are
+ * left untouched.
+ */
+ private const MAX_BRANCHES = 24;
+
+ /**
+ * Maximum row depth (offset + row count). Each UNION branch must fetch up to this many rows, so deeply
+ * paginated queries are left untouched.
+ */
+ private const MAX_ROWS = 2_000;
+
+ /**
+ * Minimum number of orders (per the order count cache) matching the queried types and statuses for the rewrite
+ * to be enabled by default. A rough threshold for where a mis-planned query gets user-visible, not a measured
+ * crossover.
+ */
+ private const MIN_ORDER_COUNT = 500_000;
+
+ /**
+ * The query being rewritten.
+ *
+ * @var OrdersTableQuery
+ */
+ private OrdersTableQuery $query;
+
+ /**
+ * The orders table name.
+ *
+ * @var string
+ */
+ private string $orders_table;
+
+ /**
+ * Constructor.
+ *
+ * @param OrdersTableQuery $query The query to rewrite.
+ *
+ * @since 11.0.0
+ */
+ public function __construct( OrdersTableQuery $query ) {
+ $this->query = $query;
+ $this->orders_table = $query->get_table_name( 'orders' );
+ }
+
+ /**
+ * Returns the rewritten SQL query, or NULL when the query is not eligible for the rewrite.
+ *
+ * @param string[] $clauses Associative array with the final 'fields', 'join', 'where', 'groupby',
+ * 'orderby' and 'limits' clauses (the latter four including their keywords).
+ * @param bool $suppress_filters Whether the query is running with filters suppressed.
+ * @return string|null The rewritten SQL query, or NULL if the query is not eligible.
+ *
+ * @since 11.0.0
+ */
+ public function get_sql( array $clauses, bool $suppress_filters ): ?string {
+ // Each step either extracts a validated piece of the rewrite or bails out (returns null) when the query
+ // isn't the plain "type + status, ordered by creation date" shape we can safely rewrite. The UNION is only
+ // assembled once every piece is in place.
+ if ( ! $this->has_rewritable_clause_shape( $clauses ) ) {
+ return null;
+ }
+
+ $direction = $this->extract_order_direction( $clauses['orderby'] ?? '' );
+ if ( null === $direction ) {
+ return null;
+ }
+
+ $limit = $this->extract_limit( $clauses['limits'] ?? '' );
+ if ( null === $limit ) {
+ return null;
+ }
+
+ $types_and_statuses = $this->extract_types_and_statuses();
+ if ( null === $types_and_statuses ) {
+ return null;
+ }
+ list( $types, $statuses ) = $types_and_statuses;
+
+ if ( ! $this->is_enabled( $types, $statuses, $suppress_filters ) ) {
+ return null;
+ }
+
+ if ( ! $this->where_matches_type_status_args( $clauses['where'] ?? '' ) ) {
+ return null;
+ }
+
+ list( $offset, $row_count ) = $limit;
+
+ return $this->build_union_sql( $types, $statuses, $direction, $offset + $row_count, $clauses['limits'] ?? '' );
+ }
+
+ /**
+ * Checks the fixed clauses (selected fields, join, group by) are exactly those of the plain order id list
+ * query. Any join, grouping or extra selected field means the query isn't a candidate for the rewrite.
+ *
+ * @param string[] $clauses The query clauses (see get_sql()).
+ * @return bool Whether the clause shape is rewritable.
+ */
+ private function has_rewritable_clause_shape( array $clauses ): bool {
+ return '' === ( $clauses['join'] ?? '' )
+ && '' === ( $clauses['groupby'] ?? '' )
+ && "{$this->orders_table}.id" === ( $clauses['fields'] ?? '' );
+ }
+
+ /**
+ * Extracts the sort direction from the ORDER BY clause, or NULL when it isn't an ORDER BY on date_created_gmt
+ * alone (the only ordering the type_status_date index can satisfy within each branch).
+ *
+ * @param string $orderby The ORDER BY clause, including the keyword.
+ * @return string|null 'ASC', 'DESC', or NULL when ineligible.
+ */
+ private function extract_order_direction( string $orderby ): ?string {
+ foreach ( array( 'ASC', 'DESC' ) as $direction ) {
+ if ( "ORDER BY {$this->orders_table}.date_created_gmt {$direction}" === $orderby ) {
+ return $direction;
+ }
+ }
+
+ return null;
+ }
+
+ /**
+ * Extracts the offset and row count from the LIMIT clause, or NULL when the query is unlimited or too deeply
+ * paginated to benefit (each branch would have to fetch offset + row count rows). The offset + row count cap
+ * also rejects the "unlimited" sentinel row count.
+ *
+ * @param string $limits The LIMIT clause, including the keyword.
+ * @return int[]|null Array of [ offset, row count ], or NULL when ineligible.
+ */
+ private function extract_limit( string $limits ): ?array {
+ if ( ! preg_match( '/^LIMIT (\d+), (\d+)$/', $limits, $limit_parts ) ) {
+ return null;
+ }
+
+ $offset = (int) $limit_parts[1];
+ $row_count = (int) $limit_parts[2];
+
+ if ( $row_count < 1 || ( $offset + $row_count ) > self::MAX_ROWS ) {
+ return null;
+ }
+
+ return array( $offset, $row_count );
+ }
+
+ /**
+ * Extracts the queried order types and statuses, or NULL when they don't form a rewritable set: the 'type' and
+ * 'status' args must both be set and contain only non-empty strings, cover at least two statuses (a single
+ * status is already served by the type_status_date index), and stay within the branch cap.
+ *
+ * @return array[]|null Array of [ types, statuses ] (each a list of unique strings), or NULL when ineligible.
+ */
+ private function extract_types_and_statuses(): ?array {
+ if ( ! $this->query->arg_isset( 'type' ) || ! $this->query->arg_isset( 'status' ) ) {
+ return null;
+ }
+
+ $types = array_values( array_unique( (array) $this->query->get( 'type' ) ) );
+ $statuses = array_values( array_unique( (array) $this->query->get( 'status' ) ) );
+
+ foreach ( array_merge( $types, $statuses ) as $value ) {
+ if ( ! is_string( $value ) || '' === $value ) {
+ return null;
+ }
+ }
+
+ if ( count( $statuses ) < 2 || ( count( $types ) * count( $statuses ) ) > self::MAX_BRANCHES ) {
+ return null;
+ }
+
+ return array( $types, $statuses );
+ }
+
+ /**
+ * Checks the WHERE clause is exactly the one the 'type' and 'status' args generate (same order as
+ * OrdersTableQuery::process_orders_table_query_args()). Any other contribution — other query args or filters —
+ * disqualifies the query. Both columns are of the 'string' type per the OrdersTableDataStore column mappings.
+ *
+ * @param string $where The WHERE clause (without the WHERE keyword).
+ * @return bool Whether the WHERE clause is exactly the type/status one.
+ */
+ private function where_matches_type_status_args( string $where ): bool {
+ $expected_where = '1=1';
+ foreach ( array( 'status', 'type' ) as $arg_key ) {
+ $clause = $this->query->where( $this->orders_table, $arg_key, '=', $this->query->get( $arg_key ), 'string' );
+ $expected_where .= " AND ({$clause})";
+ }
+
+ return $where === $expected_where;
+ }
+
+ /**
+ * Assembles the UNION ALL rewrite from the validated pieces. Each branch is wrapped in a derived table (instead
+ * of using parenthesized UNION members) so that the per-branch ORDER BY + LIMIT is honored across MySQL,
+ * MariaDB and SQLite.
+ *
+ * @param string[] $types Queried order types.
+ * @param string[] $statuses Queried order statuses.
+ * @param string $direction Sort direction ('ASC' or 'DESC').
+ * @param int $branch_rows Number of rows each branch must fetch (offset + row count).
+ * @param string $limits The outer LIMIT clause, including the keyword.
+ * @return string The rewritten SQL query.
+ */
+ private function build_union_sql( array $types, array $statuses, string $direction, int $branch_rows, string $limits ): string {
+ global $wpdb;
+
+ $branches = array();
+
+ foreach ( $types as $type ) {
+ foreach ( $statuses as $status ) {
+ $branch = $wpdb->prepare(
+ "SELECT id, date_created_gmt FROM {$this->orders_table} WHERE type = %s AND status = %s ORDER BY date_created_gmt {$direction} LIMIT {$branch_rows}", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
+ $type,
+ $status
+ );
+
+ $branches[] = 'SELECT id, date_created_gmt FROM ( ' . $branch . ' ) union' . count( $branches );
+ }
+ }
+
+ return 'SELECT id FROM ( ' . implode( ' UNION ALL ', $branches ) . " ) candidates ORDER BY date_created_gmt {$direction} {$limits}";
+ }
+
+ /**
+ * Returns whether the rewrite should be used for the given types and statuses.
+ *
+ * Enabled by default once the matching order count reaches MIN_ORDER_COUNT. Counts come from
+ * OrderUtil::get_count_for_type() — the same facade the order admin list screen uses for its status counts —
+ * which reads the order count cache and computes (and caches) the counts on a miss.
+ *
+ * @param string[] $types Queried order types.
+ * @param string[] $statuses Queried order statuses.
+ * @param bool $suppress_filters Whether the query is running with filters suppressed.
+ * @return bool Whether the rewrite should be used.
+ */
+ private function is_enabled( array $types, array $statuses, bool $suppress_filters ): bool {
+ $orders_count = 0;
+
+ foreach ( $types as $type ) {
+ $counts = OrderUtil::get_count_for_type( $type );
+
+ foreach ( $statuses as $status ) {
+ $orders_count += $counts[ $status ] ?? 0;
+ }
+ }
+
+ $enabled = $orders_count >= self::MIN_ORDER_COUNT;
+
+ if ( $suppress_filters ) {
+ return $enabled;
+ }
+
+ /**
+ * Filters whether a query for multiple order statuses ordered by creation date may be rewritten as a
+ * UNION ALL of single-status queries for performance. The rewrite produces the same results and, even
+ * when enabled here, only applies to queries generated purely from the 'type' and 'status' query args
+ * (no search, meta or field filters), such as the default order admin list screen query.
+ *
+ * Hosts that know their database benefits from the rewrite regardless of store size (or that don't want
+ * to depend on the order count cache being warm) can force-enable it with
+ * add_filter( 'woocommerce_orders_table_query_status_union_optimization', '__return_true' ); the
+ * structural eligibility checks above still apply.
+ *
+ * @param bool $enabled Whether the rewrite is enabled. Defaults to TRUE only when the cached
+ * number of orders matching the queried types and statuses is at least
+ * 500,000; FALSE otherwise (including when the order count cache is cold).
+ * @param OrdersTableQuery $query The OrdersTableQuery instance.
+ *
+ * @since 11.0.0
+ */
+ return (bool) apply_filters( 'woocommerce_orders_table_query_status_union_optimization', $enabled, $this->query );
+ }
+}
diff --git a/plugins/woocommerce/tests/php/src/Internal/Admin/Orders/ListTableTest.php b/plugins/woocommerce/tests/php/src/Internal/Admin/Orders/ListTableTest.php
index 3c1a3139cea..7f1e1cd7538 100644
--- a/plugins/woocommerce/tests/php/src/Internal/Admin/Orders/ListTableTest.php
+++ b/plugins/woocommerce/tests/php/src/Internal/Admin/Orders/ListTableTest.php
@@ -232,4 +232,55 @@ class ListTableTest extends \WC_Unit_Test_Case {
$this->assertCount( 2, $filtered_items ); // Both orders should be shown.
}
+
+ /**
+ * Helper to read the order query args prepared by the list table.
+ *
+ * @return array
+ */
+ private function get_order_query_args(): array {
+ $getter = function () {
+ return $this->order_query_args;
+ };
+
+ return $getter->call( $this->sut );
+ }
+
+ /**
+ * @testdox Submitting the search form with an empty search term keeps the cached-count fast path.
+ */
+ public function test_empty_search_term_uses_cached_count_fast_path(): void {
+ \WC_Helper_Order::create_order();
+
+ $_REQUEST['s'] = '';
+ $_REQUEST['search-filter'] = 'all';
+
+ $this->sut->prepare_items();
+ $query_args = $this->get_order_query_args();
+
+ unset( $_REQUEST['s'], $_REQUEST['search-filter'] );
+
+ $this->assertArrayNotHasKey( 's', $query_args, 'An empty search term should not be added to the query args' );
+ $this->assertArrayNotHasKey( 'search_filter', $query_args, 'The search filter should not be added without a search term' );
+ $this->assertTrue( $query_args['no_found_rows'] ?? false, 'An empty search should use cached order counts instead of a COUNT query' );
+ }
+
+ /**
+ * @testdox Searching with a term applies the selected search filter and uses a real results count.
+ */
+ public function test_search_term_sets_search_filter_and_counts_results(): void {
+ \WC_Helper_Order::create_order();
+
+ $_REQUEST['s'] = 'some-term';
+ $_REQUEST['search-filter'] = 'order_id';
+
+ $this->sut->prepare_items();
+ $query_args = $this->get_order_query_args();
+
+ unset( $_REQUEST['s'], $_REQUEST['search-filter'] );
+
+ $this->assertSame( 'some-term', $query_args['s'] ?? null, 'The search term should be added to the query args' );
+ $this->assertSame( 'order_id', $query_args['search_filter'] ?? null, 'The selected search filter should be applied' );
+ $this->assertArrayNotHasKey( 'no_found_rows', $query_args, 'Searches should count their actual results' );
+ }
}
diff --git a/plugins/woocommerce/tests/php/src/Internal/DataStores/Orders/OrdersTableQueryTests.php b/plugins/woocommerce/tests/php/src/Internal/DataStores/Orders/OrdersTableQueryTests.php
index c37e906d9df..9ab17195de0 100644
--- a/plugins/woocommerce/tests/php/src/Internal/DataStores/Orders/OrdersTableQueryTests.php
+++ b/plugins/woocommerce/tests/php/src/Internal/DataStores/Orders/OrdersTableQueryTests.php
@@ -3,6 +3,7 @@ declare( strict_types = 1 );
namespace Automattic\WooCommerce\Tests\Internal\DataStores\Orders;
+use Automattic\WooCommerce\Caches\OrderCountCache;
use Automattic\WooCommerce\Enums\OrderStatus;
use Automattic\WooCommerce\Internal\DataStores\Orders\OrdersTableQuery;
use Automattic\WooCommerce\RestApi\UnitTests\Helpers\OrderHelper;
@@ -790,4 +791,285 @@ class OrdersTableQueryTests extends \WC_Unit_Test_Case {
$order2->delete( true );
$order3->delete( true );
}
+
+ /**
+ * Helper function to create orders with interleaved statuses and strictly decreasing creation dates.
+ *
+ * @param int $count Number of orders to create.
+ * @return int[] Order IDs, ordered by creation date descending.
+ */
+ private function create_orders_with_interleaved_statuses( int $count ): array {
+ $statuses = array( OrderStatus::PENDING, OrderStatus::PROCESSING, OrderStatus::COMPLETED );
+ $ids = array();
+
+ for ( $i = 0; $i < $count; $i++ ) {
+ $order = new \WC_Order();
+ $order->set_status( $statuses[ $i % count( $statuses ) ] );
+ $order->set_date_created( strtotime( '2023-06-01 12:00:00' ) - ( $i * HOUR_IN_SECONDS ) );
+ $order->save();
+ $ids[] = $order->get_id();
+ }
+
+ return $ids;
+ }
+
+ /**
+ * Helper function to run wc_get_orders() and capture the SQL query executed by OrdersTableQuery.
+ *
+ * @param array $args Query args ('return' => 'ids' is always added).
+ * @return array Two-element array containing the queried order IDs and the executed SQL query.
+ */
+ private function get_orders_and_capture_sql( array $args ): array {
+ $captured_sql = '';
+ $callback = function ( $result, $query, $sql ) use ( &$captured_sql ) {
+ // Avoid parameter not used PHPCS errors.
+ unset( $query );
+ $captured_sql = $sql;
+ return $result;
+ };
+
+ add_filter( 'woocommerce_hpos_pre_query', $callback, 10, 3 );
+ $ids = wc_get_orders( array_merge( $args, array( 'return' => 'ids' ) ) );
+ remove_filter( 'woocommerce_hpos_pre_query', $callback );
+
+ return array( $ids, $captured_sql );
+ }
+
+
+ /**
+ * Helper function to force-enable the status union rewrite, which by default is gated by store size.
+ */
+ private function force_enable_status_union_rewrite(): void {
+ add_filter( 'woocommerce_orders_table_query_status_union_optimization', '__return_true' );
+ }
+
+ /**
+ * Helper function to remove the force-enablement of the status union rewrite.
+ */
+ private function reset_status_union_rewrite(): void {
+ remove_filter( 'woocommerce_orders_table_query_status_union_optimization', '__return_true' );
+ }
+
+ /**
+ * @testdox Multi-status queries ordered by creation date are rewritten as a UNION of single-status queries and return the same results.
+ */
+ public function test_status_union_rewrite_applies_and_preserves_results(): void {
+ $ids = $this->create_orders_with_interleaved_statuses( 9 );
+ $args = array(
+ 'status' => array( OrderStatus::PENDING, OrderStatus::PROCESSING, OrderStatus::COMPLETED ),
+ 'orderby' => 'date',
+ 'order' => 'DESC',
+ 'limit' => 4,
+ );
+
+ $this->force_enable_status_union_rewrite();
+ list( $queried_ids, $sql ) = $this->get_orders_and_capture_sql( $args );
+ $this->reset_status_union_rewrite();
+
+ $this->assertStringContainsString( 'UNION ALL', $sql, 'Eligible multi-status queries should be rewritten as a UNION of single-status queries' );
+ $this->assertSame( array_slice( $ids, 0, 4 ), $queried_ids, 'The rewritten query should return the most recent orders across all statuses' );
+
+ add_filter( 'woocommerce_orders_table_query_status_union_optimization', '__return_false' );
+ list( $unoptimized_ids, $unoptimized_sql ) = $this->get_orders_and_capture_sql( $args );
+ remove_filter( 'woocommerce_orders_table_query_status_union_optimization', '__return_false' );
+
+ $this->assertStringNotContainsString( 'UNION ALL', $unoptimized_sql, 'The rewrite should be disabled by the woocommerce_orders_table_query_status_union_optimization filter' );
+ $this->assertSame( $unoptimized_ids, $queried_ids, 'Rewritten and regular queries should return identical results' );
+ }
+
+ /**
+ * @testdox The default order query (multiple statuses, ordered by creation date) is rewritten as a UNION of single-status queries.
+ */
+ public function test_status_union_rewrite_applies_to_default_query(): void {
+ $ids = $this->create_orders_with_interleaved_statuses( 3 );
+
+ $this->force_enable_status_union_rewrite();
+ list( $queried_ids, $sql ) = $this->get_orders_and_capture_sql( array() );
+ $this->reset_status_union_rewrite();
+
+ $this->assertStringContainsString( 'UNION ALL', $sql, 'The default order query should be rewritten as a UNION of single-status queries' );
+ $this->assertSame( $ids, $queried_ids, 'The rewritten default query should return all orders, most recent first' );
+ }
+
+ /**
+ * @testdox The status union rewrite returns the same results as the regular query across pages and sort directions.
+ */
+ public function test_status_union_rewrite_pagination_and_sort_direction(): void {
+ $this->create_orders_with_interleaved_statuses( 9 );
+
+ $this->force_enable_status_union_rewrite();
+
+ foreach ( array( 'DESC', 'ASC' ) as $order ) {
+ foreach ( array( 1, 2, 3 ) as $page ) {
+ $args = array(
+ 'status' => array( OrderStatus::PENDING, OrderStatus::PROCESSING, OrderStatus::COMPLETED ),
+ 'orderby' => 'date',
+ 'order' => $order,
+ 'limit' => 4,
+ 'page' => $page,
+ );
+
+ list( $queried_ids, $sql ) = $this->get_orders_and_capture_sql( $args );
+
+ add_filter( 'woocommerce_orders_table_query_status_union_optimization', '__return_false' );
+ list( $unoptimized_ids ) = $this->get_orders_and_capture_sql( $args );
+ remove_filter( 'woocommerce_orders_table_query_status_union_optimization', '__return_false' );
+
+ $this->assertStringContainsString( 'UNION ALL', $sql, "Page {$page} ({$order}) should be served by the rewritten query" );
+ $this->assertSame( $unoptimized_ids, $queried_ids, "Page {$page} ({$order}) of the rewritten query should match the regular query" );
+ }
+ }
+
+ $this->reset_status_union_rewrite();
+ }
+
+ /**
+ * @testdox The status union rewrite is skipped for queries it cannot serve identically.
+ */
+ public function test_status_union_rewrite_skipped_for_ineligible_queries(): void {
+ $this->create_orders_with_interleaved_statuses( 3 );
+ $this->force_enable_status_union_rewrite();
+
+ $ineligible_args = array(
+ 'a single status' => array( 'status' => array( OrderStatus::PENDING ) ),
+ 'no row limit' => array( 'limit' => -1 ),
+ 'a non-date orderby' => array( 'orderby' => 'id' ),
+ 'a field filter' => array( 'customer_id' => 123 ),
+ 'a meta query' => array(
+ 'meta_query' => array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
+
+ array(
+ 'key' => 'some_key',
+ 'value' => 'some_value',
+ ),
+ ),
+ ),
+ );
+
+ foreach ( $ineligible_args as $description => $args ) {
+ $args = array_merge(
+ array(
+ 'status' => array( OrderStatus::PENDING, OrderStatus::PROCESSING ),
+ 'orderby' => 'date',
+ 'order' => 'DESC',
+ 'limit' => 4,
+ ),
+ $args
+ );
+
+ list( , $sql ) = $this->get_orders_and_capture_sql( $args );
+
+ $this->assertStringNotContainsString( 'UNION ALL', $sql, "A query with {$description} should not be rewritten" );
+ }
+
+ $this->reset_status_union_rewrite();
+ }
+
+ /**
+ * @testdox Queries customized via the clauses filter are not rewritten.
+ */
+ public function test_status_union_rewrite_skipped_when_clauses_modified(): void {
+ $ids = $this->create_orders_with_interleaved_statuses( 3 );
+ $this->force_enable_status_union_rewrite();
+
+ $filter_callback = function ( $clauses ) {
+ $clauses['where'] .= ' AND 1=1';
+ return $clauses;
+ };
+
+ add_filter( 'woocommerce_orders_table_query_clauses', $filter_callback );
+ list( $queried_ids, $sql ) = $this->get_orders_and_capture_sql(
+ array(
+ 'status' => array( OrderStatus::PENDING, OrderStatus::PROCESSING ),
+ 'orderby' => 'date',
+ 'order' => 'DESC',
+ 'limit' => 4,
+ )
+ );
+ remove_filter( 'woocommerce_orders_table_query_clauses', $filter_callback );
+
+ $this->reset_status_union_rewrite();
+
+ $this->assertStringNotContainsString( 'UNION ALL', $sql, 'Queries modified via the clauses filter should not be rewritten' );
+ $this->assertSame( array( $ids[0], $ids[1] ), $queried_ids, 'The unmodified query should still return matching orders' );
+ }
+
+ /**
+ * @testdox Queries modified via the SQL filter are not rewritten, and the modified SQL is the one executed.
+ */
+ public function test_status_union_rewrite_skipped_when_sql_modified(): void {
+ $ids = $this->create_orders_with_interleaved_statuses( 3 );
+ $this->force_enable_status_union_rewrite();
+
+ $filter_callback = function ( $sql ) {
+ return $sql . ' -- modified';
+ };
+
+ add_filter( 'woocommerce_orders_table_query_sql', $filter_callback );
+ list( $queried_ids, $sql ) = $this->get_orders_and_capture_sql(
+ array(
+ 'status' => array( OrderStatus::PENDING, OrderStatus::PROCESSING ),
+ 'orderby' => 'date',
+ 'order' => 'DESC',
+ 'limit' => 4,
+ )
+ );
+ remove_filter( 'woocommerce_orders_table_query_sql', $filter_callback );
+
+ $this->reset_status_union_rewrite();
+
+ $this->assertStringNotContainsString( 'UNION ALL', $sql, 'Queries modified via the SQL filter should not be rewritten' );
+ $this->assertStringEndsWith( '-- modified', $sql, 'The SQL modified by the filter should be the SQL that gets executed' );
+ $this->assertSame( array( $ids[0], $ids[1] ), $queried_ids, 'The filter-modified query should still return matching orders' );
+ }
+
+ /**
+ * @testdox The status union rewrite is disabled by default on stores below the order count threshold.
+ */
+ public function test_status_union_rewrite_disabled_by_default_on_small_stores(): void {
+ $this->create_orders_with_interleaved_statuses( 3 );
+
+ list( , $sql ) = $this->get_orders_and_capture_sql(
+ array(
+ 'status' => array( OrderStatus::PENDING, OrderStatus::PROCESSING, OrderStatus::COMPLETED ),
+ 'orderby' => 'date',
+ 'order' => 'DESC',
+ 'limit' => 4,
+ )
+ );
+
+ $this->assertStringNotContainsString( 'UNION ALL', $sql, 'The rewrite should be disabled by default on stores below the order count threshold' );
+ }
+
+ /**
+ * @testdox The status union rewrite is enabled by default once cached order counts reach the threshold.
+ */
+ public function test_status_union_rewrite_enabled_by_default_on_large_stores(): void {
+ $ids = $this->create_orders_with_interleaved_statuses( 3 );
+
+ $count_cache = new OrderCountCache();
+ $count_cache->set_multiple(
+ 'shop_order',
+ array(
+ 'wc-pending' => 200000,
+ 'wc-processing' => 200000,
+ 'wc-completed' => 200000,
+ )
+ );
+
+ list( $queried_ids, $sql ) = $this->get_orders_and_capture_sql(
+ array(
+ 'type' => 'shop_order',
+ 'status' => array( OrderStatus::PENDING, OrderStatus::PROCESSING, OrderStatus::COMPLETED ),
+ 'orderby' => 'date',
+ 'order' => 'DESC',
+ 'limit' => 4,
+ )
+ );
+
+ $count_cache->flush( 'shop_order' );
+
+ $this->assertStringContainsString( 'UNION ALL', $sql, 'The rewrite should be enabled by default once cached order counts reach the threshold' );
+ $this->assertSame( $ids, $queried_ids, 'The rewritten query should return all orders, most recent first' );
+ }
}