CustomOrdersTableController.php
4 days ago
DataSynchronizer.php
7 months ago
HposOrderCapabilityHelper.php
4 days ago
LegacyDataCleanup.php
1 year ago
LegacyDataHandler.php
5 months ago
OrdersTableDataStore.php
4 days ago
OrdersTableDataStoreMeta.php
4 days ago
OrdersTableFieldQuery.php
2 years ago
OrdersTableMetaQuery.php
1 year ago
OrdersTableQuery.php
4 days ago
OrdersTableRefundDataStore.php
2 months ago
OrdersTableSearchQuery.php
11 months ago
OrdersTableStatusUnionQuery.php
4 days ago
OrdersTableStatusUnionQuery.php
301 lines
| 1 | <?php |
| 2 | /** |
| 3 | * OrdersTableStatusUnionQuery class file. |
| 4 | */ |
| 5 | |
| 6 | declare( strict_types=1 ); |
| 7 | |
| 8 | namespace Automattic\WooCommerce\Internal\DataStores\Orders; |
| 9 | |
| 10 | use Automattic\WooCommerce\Utilities\OrderUtil; |
| 11 | |
| 12 | defined( 'ABSPATH' ) || exit; |
| 13 | |
| 14 | /** |
| 15 | * Rewrites a "multiple statuses, ordered by creation date" order query (such as the default order admin list |
| 16 | * screen query) as a UNION ALL of single-status queries. |
| 17 | * |
| 18 | * `status IN (...)` prevents the `type_status_date` index from serving a global `date_created_gmt` ordering, so on |
| 19 | * large stores the optimizer may pick a plan that scans millions of rows for a single page. One branch per (type, |
| 20 | * status) pair is fully served — filter and order — by `type_status_date`, leaving the outer query to merge a few |
| 21 | * pre-sorted rows. |
| 22 | * |
| 23 | * Eligibility (exact clause match) and the store-size gate are documented at the methods that enforce them |
| 24 | * (get_sql() and is_enabled()). |
| 25 | */ |
| 26 | class OrdersTableStatusUnionQuery { |
| 27 | |
| 28 | /** |
| 29 | * Maximum number of UNION branches (one per type/status pair). Queries needing more branches than this are |
| 30 | * left untouched. |
| 31 | */ |
| 32 | private const MAX_BRANCHES = 24; |
| 33 | |
| 34 | /** |
| 35 | * Maximum row depth (offset + row count). Each UNION branch must fetch up to this many rows, so deeply |
| 36 | * paginated queries are left untouched. |
| 37 | */ |
| 38 | private const MAX_ROWS = 2_000; |
| 39 | |
| 40 | /** |
| 41 | * Minimum number of orders (per the order count cache) matching the queried types and statuses for the rewrite |
| 42 | * to be enabled by default. A rough threshold for where a mis-planned query gets user-visible, not a measured |
| 43 | * crossover. |
| 44 | */ |
| 45 | private const MIN_ORDER_COUNT = 500_000; |
| 46 | |
| 47 | /** |
| 48 | * The query being rewritten. |
| 49 | * |
| 50 | * @var OrdersTableQuery |
| 51 | */ |
| 52 | private OrdersTableQuery $query; |
| 53 | |
| 54 | /** |
| 55 | * The orders table name. |
| 56 | * |
| 57 | * @var string |
| 58 | */ |
| 59 | private string $orders_table; |
| 60 | |
| 61 | /** |
| 62 | * Constructor. |
| 63 | * |
| 64 | * @param OrdersTableQuery $query The query to rewrite. |
| 65 | * |
| 66 | * @since 11.0.0 |
| 67 | */ |
| 68 | public function __construct( OrdersTableQuery $query ) { |
| 69 | $this->query = $query; |
| 70 | $this->orders_table = $query->get_table_name( 'orders' ); |
| 71 | } |
| 72 | |
| 73 | /** |
| 74 | * Returns the rewritten SQL query, or NULL when the query is not eligible for the rewrite. |
| 75 | * |
| 76 | * @param string[] $clauses Associative array with the final 'fields', 'join', 'where', 'groupby', |
| 77 | * 'orderby' and 'limits' clauses (the latter four including their keywords). |
| 78 | * @param bool $suppress_filters Whether the query is running with filters suppressed. |
| 79 | * @return string|null The rewritten SQL query, or NULL if the query is not eligible. |
| 80 | * |
| 81 | * @since 11.0.0 |
| 82 | */ |
| 83 | public function get_sql( array $clauses, bool $suppress_filters ): ?string { |
| 84 | // Each step either extracts a validated piece of the rewrite or bails out (returns null) when the query |
| 85 | // isn't the plain "type + status, ordered by creation date" shape we can safely rewrite. The UNION is only |
| 86 | // assembled once every piece is in place. |
| 87 | if ( ! $this->has_rewritable_clause_shape( $clauses ) ) { |
| 88 | return null; |
| 89 | } |
| 90 | |
| 91 | $direction = $this->extract_order_direction( $clauses['orderby'] ?? '' ); |
| 92 | if ( null === $direction ) { |
| 93 | return null; |
| 94 | } |
| 95 | |
| 96 | $limit = $this->extract_limit( $clauses['limits'] ?? '' ); |
| 97 | if ( null === $limit ) { |
| 98 | return null; |
| 99 | } |
| 100 | |
| 101 | $types_and_statuses = $this->extract_types_and_statuses(); |
| 102 | if ( null === $types_and_statuses ) { |
| 103 | return null; |
| 104 | } |
| 105 | list( $types, $statuses ) = $types_and_statuses; |
| 106 | |
| 107 | if ( ! $this->is_enabled( $types, $statuses, $suppress_filters ) ) { |
| 108 | return null; |
| 109 | } |
| 110 | |
| 111 | if ( ! $this->where_matches_type_status_args( $clauses['where'] ?? '' ) ) { |
| 112 | return null; |
| 113 | } |
| 114 | |
| 115 | list( $offset, $row_count ) = $limit; |
| 116 | |
| 117 | return $this->build_union_sql( $types, $statuses, $direction, $offset + $row_count, $clauses['limits'] ?? '' ); |
| 118 | } |
| 119 | |
| 120 | /** |
| 121 | * Checks the fixed clauses (selected fields, join, group by) are exactly those of the plain order id list |
| 122 | * query. Any join, grouping or extra selected field means the query isn't a candidate for the rewrite. |
| 123 | * |
| 124 | * @param string[] $clauses The query clauses (see get_sql()). |
| 125 | * @return bool Whether the clause shape is rewritable. |
| 126 | */ |
| 127 | private function has_rewritable_clause_shape( array $clauses ): bool { |
| 128 | return '' === ( $clauses['join'] ?? '' ) |
| 129 | && '' === ( $clauses['groupby'] ?? '' ) |
| 130 | && "{$this->orders_table}.id" === ( $clauses['fields'] ?? '' ); |
| 131 | } |
| 132 | |
| 133 | /** |
| 134 | * Extracts the sort direction from the ORDER BY clause, or NULL when it isn't an ORDER BY on date_created_gmt |
| 135 | * alone (the only ordering the type_status_date index can satisfy within each branch). |
| 136 | * |
| 137 | * @param string $orderby The ORDER BY clause, including the keyword. |
| 138 | * @return string|null 'ASC', 'DESC', or NULL when ineligible. |
| 139 | */ |
| 140 | private function extract_order_direction( string $orderby ): ?string { |
| 141 | foreach ( array( 'ASC', 'DESC' ) as $direction ) { |
| 142 | if ( "ORDER BY {$this->orders_table}.date_created_gmt {$direction}" === $orderby ) { |
| 143 | return $direction; |
| 144 | } |
| 145 | } |
| 146 | |
| 147 | return null; |
| 148 | } |
| 149 | |
| 150 | /** |
| 151 | * Extracts the offset and row count from the LIMIT clause, or NULL when the query is unlimited or too deeply |
| 152 | * paginated to benefit (each branch would have to fetch offset + row count rows). The offset + row count cap |
| 153 | * also rejects the "unlimited" sentinel row count. |
| 154 | * |
| 155 | * @param string $limits The LIMIT clause, including the keyword. |
| 156 | * @return int[]|null Array of [ offset, row count ], or NULL when ineligible. |
| 157 | */ |
| 158 | private function extract_limit( string $limits ): ?array { |
| 159 | if ( ! preg_match( '/^LIMIT (\d+), (\d+)$/', $limits, $limit_parts ) ) { |
| 160 | return null; |
| 161 | } |
| 162 | |
| 163 | $offset = (int) $limit_parts[1]; |
| 164 | $row_count = (int) $limit_parts[2]; |
| 165 | |
| 166 | if ( $row_count < 1 || ( $offset + $row_count ) > self::MAX_ROWS ) { |
| 167 | return null; |
| 168 | } |
| 169 | |
| 170 | return array( $offset, $row_count ); |
| 171 | } |
| 172 | |
| 173 | /** |
| 174 | * Extracts the queried order types and statuses, or NULL when they don't form a rewritable set: the 'type' and |
| 175 | * 'status' args must both be set and contain only non-empty strings, cover at least two statuses (a single |
| 176 | * status is already served by the type_status_date index), and stay within the branch cap. |
| 177 | * |
| 178 | * @return array[]|null Array of [ types, statuses ] (each a list of unique strings), or NULL when ineligible. |
| 179 | */ |
| 180 | private function extract_types_and_statuses(): ?array { |
| 181 | if ( ! $this->query->arg_isset( 'type' ) || ! $this->query->arg_isset( 'status' ) ) { |
| 182 | return null; |
| 183 | } |
| 184 | |
| 185 | $types = array_values( array_unique( (array) $this->query->get( 'type' ) ) ); |
| 186 | $statuses = array_values( array_unique( (array) $this->query->get( 'status' ) ) ); |
| 187 | |
| 188 | foreach ( array_merge( $types, $statuses ) as $value ) { |
| 189 | if ( ! is_string( $value ) || '' === $value ) { |
| 190 | return null; |
| 191 | } |
| 192 | } |
| 193 | |
| 194 | if ( count( $statuses ) < 2 || ( count( $types ) * count( $statuses ) ) > self::MAX_BRANCHES ) { |
| 195 | return null; |
| 196 | } |
| 197 | |
| 198 | return array( $types, $statuses ); |
| 199 | } |
| 200 | |
| 201 | /** |
| 202 | * Checks the WHERE clause is exactly the one the 'type' and 'status' args generate (same order as |
| 203 | * OrdersTableQuery::process_orders_table_query_args()). Any other contribution — other query args or filters — |
| 204 | * disqualifies the query. Both columns are of the 'string' type per the OrdersTableDataStore column mappings. |
| 205 | * |
| 206 | * @param string $where The WHERE clause (without the WHERE keyword). |
| 207 | * @return bool Whether the WHERE clause is exactly the type/status one. |
| 208 | */ |
| 209 | private function where_matches_type_status_args( string $where ): bool { |
| 210 | $expected_where = '1=1'; |
| 211 | foreach ( array( 'status', 'type' ) as $arg_key ) { |
| 212 | $clause = $this->query->where( $this->orders_table, $arg_key, '=', $this->query->get( $arg_key ), 'string' ); |
| 213 | $expected_where .= " AND ({$clause})"; |
| 214 | } |
| 215 | |
| 216 | return $where === $expected_where; |
| 217 | } |
| 218 | |
| 219 | /** |
| 220 | * Assembles the UNION ALL rewrite from the validated pieces. Each branch is wrapped in a derived table (instead |
| 221 | * of using parenthesized UNION members) so that the per-branch ORDER BY + LIMIT is honored across MySQL, |
| 222 | * MariaDB and SQLite. |
| 223 | * |
| 224 | * @param string[] $types Queried order types. |
| 225 | * @param string[] $statuses Queried order statuses. |
| 226 | * @param string $direction Sort direction ('ASC' or 'DESC'). |
| 227 | * @param int $branch_rows Number of rows each branch must fetch (offset + row count). |
| 228 | * @param string $limits The outer LIMIT clause, including the keyword. |
| 229 | * @return string The rewritten SQL query. |
| 230 | */ |
| 231 | private function build_union_sql( array $types, array $statuses, string $direction, int $branch_rows, string $limits ): string { |
| 232 | global $wpdb; |
| 233 | |
| 234 | $branches = array(); |
| 235 | |
| 236 | foreach ( $types as $type ) { |
| 237 | foreach ( $statuses as $status ) { |
| 238 | $branch = $wpdb->prepare( |
| 239 | "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 |
| 240 | $type, |
| 241 | $status |
| 242 | ); |
| 243 | |
| 244 | $branches[] = 'SELECT id, date_created_gmt FROM ( ' . $branch . ' ) union' . count( $branches ); |
| 245 | } |
| 246 | } |
| 247 | |
| 248 | return 'SELECT id FROM ( ' . implode( ' UNION ALL ', $branches ) . " ) candidates ORDER BY date_created_gmt {$direction} {$limits}"; |
| 249 | } |
| 250 | |
| 251 | /** |
| 252 | * Returns whether the rewrite should be used for the given types and statuses. |
| 253 | * |
| 254 | * Enabled by default once the matching order count reaches MIN_ORDER_COUNT. Counts come from |
| 255 | * OrderUtil::get_count_for_type() — the same facade the order admin list screen uses for its status counts — |
| 256 | * which reads the order count cache and computes (and caches) the counts on a miss. |
| 257 | * |
| 258 | * @param string[] $types Queried order types. |
| 259 | * @param string[] $statuses Queried order statuses. |
| 260 | * @param bool $suppress_filters Whether the query is running with filters suppressed. |
| 261 | * @return bool Whether the rewrite should be used. |
| 262 | */ |
| 263 | private function is_enabled( array $types, array $statuses, bool $suppress_filters ): bool { |
| 264 | $orders_count = 0; |
| 265 | |
| 266 | foreach ( $types as $type ) { |
| 267 | $counts = OrderUtil::get_count_for_type( $type ); |
| 268 | |
| 269 | foreach ( $statuses as $status ) { |
| 270 | $orders_count += $counts[ $status ] ?? 0; |
| 271 | } |
| 272 | } |
| 273 | |
| 274 | $enabled = $orders_count >= self::MIN_ORDER_COUNT; |
| 275 | |
| 276 | if ( $suppress_filters ) { |
| 277 | return $enabled; |
| 278 | } |
| 279 | |
| 280 | /** |
| 281 | * Filters whether a query for multiple order statuses ordered by creation date may be rewritten as a |
| 282 | * UNION ALL of single-status queries for performance. The rewrite produces the same results and, even |
| 283 | * when enabled here, only applies to queries generated purely from the 'type' and 'status' query args |
| 284 | * (no search, meta or field filters), such as the default order admin list screen query. |
| 285 | * |
| 286 | * Hosts that know their database benefits from the rewrite regardless of store size (or that don't want |
| 287 | * to depend on the order count cache being warm) can force-enable it with |
| 288 | * add_filter( 'woocommerce_orders_table_query_status_union_optimization', '__return_true' ); the |
| 289 | * structural eligibility checks above still apply. |
| 290 | * |
| 291 | * @param bool $enabled Whether the rewrite is enabled. Defaults to TRUE only when the cached |
| 292 | * number of orders matching the queried types and statuses is at least |
| 293 | * 500,000; FALSE otherwise (including when the order count cache is cold). |
| 294 | * @param OrdersTableQuery $query The OrdersTableQuery instance. |
| 295 | * |
| 296 | * @since 11.0.0 |
| 297 | */ |
| 298 | return (bool) apply_filters( 'woocommerce_orders_table_query_status_union_optimization', $enabled, $this->query ); |
| 299 | } |
| 300 | } |
| 301 |