Commit 0bd1fe7d3cb for woocommerce
commit 0bd1fe7d3cb1ba0c2944013c2b918dfda5a31656
Author: Vlad Olaru <vlad.olaru@automattic.com>
Date: Mon Nov 16 14:37:26 2026 +0200
[ai] Correct the index condition in the performance reviewer's MIN/MAX rule (#71004)
* docs(review): Correct the index condition of the MIN/MAX rule
The performance persona told the reviewer to suggest rewriting
SELECT MIN(col), MAX(col) as two ORDER BY ... LIMIT 1 seeks when WHERE
and the column share a composite index with "equality or bounded-range
leading keys". A range on a leading column does not work: the index is
then sorted by that column first, so it cannot return rows in the
order of the target column.
Measured on MySQL 8.4 and MariaDB with 60,000 synthetic rows in the
HPOS orders schema. With type = 'shop_order' AND status != 'trash',
a LIMIT 1 seek forced onto type_status_date(type, status,
date_created_gmt) reads and sorts 51,600 index entries, the same as
the plain MIN/MAX. The seek is cheap only through the single-column
date_created index, and only while the rows at its ends pass the
WHERE: trashing the newest 6,000 orders made it read 6,001 rows. With
equality on every leading column, plain MIN/MAX is already answered
from the index ends and needs no rewrite.
The rule now exempts that equality-only form, asks for an index that
returns rows sorted by the column with few failing rows at its ends,
and says a range, IN list or != on an earlier index column rules the
index out. The NULL-handling part of the rule is unchanged.
The performance skill gets the same correction in a separate change.
* docs(review): Require constant equalities and full-column indexes
The corrected MIN/MAX rule exempted queries where every WHERE
predicate is "an equality on the leading columns" of an index. Review
found two readings of that wording that hide a scan.
An equality between two columns is not an equality with a constant:
the matching rows span several values of the leading column, so the
index does not return them sorted by the target column. On the HPOS
orders schema with 60,000 rows, status = 'wc-completed' is answered
from the index ends, while status = currency on the same index read
52,500 entries on MySQL 8.4 and scanned the table on MariaDB.
An index that stores only a prefix of a column does not sort the full
value. Core has one: meta_key_value (meta_key(50), meta_value(20)) on
the HPOS order meta table. MIN/MAX(meta_value) with an equality on
meta_key read every row for the key, and the ORDER BY ... LIMIT 1
form needed a filesort.
The rule now says "equality with a constant" in both places, lists a
comparison with another column among the predicates that rule an
index out, and says a prefix index serves neither the exemption nor
the seek.
* docs(review): State the MIN/MAX index condition as a single value
The MIN/MAX rule described its index condition by predicate type:
"an equality with a constant" on every leading column. Review found a
form that passes that wording and still spans several key values:
status = 'a' OR status = 'b'. On the HPOS orders schema with 60,000
rows, such an OR on type_status_date read 35,100 index entries on
MySQL 8.4 and MariaDB.
The rule now names the property the index needs: the WHERE must pin
every leading index column to one constant value. A range, IN list,
OR, != or a comparison with another column are given as examples of
what leaves more than one value.
It also says not to suggest the rewrite for a query with GROUP BY.
Two LIMIT 1 seeks return one row for the whole query, so they cannot
replace a per-group aggregate.
* docs(review): Limit the MIN/MAX exemption to pinning equalities
The MIN/MAX rule exempted a query whose WHERE pins every leading
column of an index to one constant value. That wording also covers a
WHERE with a further predicate on a column outside the index, and the
database does not answer that form from the index ends. On the HPOS
orders schema with 60,000 rows, adding currency = 'USD' to type and
status equalities turned "Select tables optimized away" into 36,000
index entries read on MySQL 8.4 and a table scan on MariaDB. The
exemption now requires a WHERE that contains only those equalities.
An earlier wording had this restriction and the last rewording
dropped it.
The rule also named the fix as a UNION ALL of two LIMIT 1 seeks. On
its own that returns two rows, and no rows when nothing matches,
where MIN/MAX returns one row of NULLs. The fix now reads MIN/MAX
over the UNION ALL, the form the orders list uses, which keeps the
one-row result.
* docs(review): Bound the MIN/MAX rule to single-table, ungrouped queries
The MIN/MAX rule exempts a WHERE that only pins the leading columns
of an index, and suggests a seek rewrite otherwise. Neither holds
outside a single-table query without GROUP BY, and the rule did not
say so.
Measured on the HPOS orders schema with 60,000 rows, on MySQL 8.4 and
MariaDB. The exempted WHERE with GROUP BY added is not answered from
the index ends: it read 36,000 index entries on MySQL and scanned the
table on MariaDB. With an inner join that keeps a third of the
orders, the aggregate read about 20,000 rows, and so did the LIMIT 1
seek, because rows at the index ends fail the join.
The rule now says the exemption and the rewrite cover that one shape
only, and that the reviewer judges any other shape on its own. This
replaces the separate sentence that excluded GROUP BY from the
rewrite alone.
* docs(review): Exempt MIN/MAX without a WHERE on an indexed column
The MIN/MAX rule exempted only queries whose WHERE pins the leading
columns of an index. A query with no WHERE at all is answered from
the index ends too, when an index starts with the column, but the
rule called it a scan and suggested a rewrite.
Core runs that shape in five places, all SELECT MAX(<id column>) FROM
<table>, for example in Internal/Admin/Analytics.php and
Internal/Admin/ImportExport/HposOrderExportHandler.php. On MySQL 8.4
and MariaDB the no-WHERE query reports "Select tables optimized away"
on date_created_gmt and on the primary key, and a table scan on a
column that no index starts with.
The exemption now covers no WHERE with an index that starts with the
column, next to the existing pinned-columns case.
diff --git a/.ai/review/personas/performance.md b/.ai/review/personas/performance.md
index d5c3d36c6f0..14ba0e7f477 100644
--- a/.ai/review/personas/performance.md
+++ b/.ai/review/personas/performance.md
@@ -55,12 +55,23 @@ This controls false positives. Follow it strictly.
`woocommerce_attribute_lookup_enabled` must be `'yes'` to use the attributes table. Code that builds
or regenerates the lookup tables must read the source tables. Removing or inverting a gate is a
correctness regression (mid-regeneration reads return incomplete rows).
-- **`SELECT MIN(col), MAX(col)`** over a large table scans every matching row. When `WHERE` and the
- column share a composite index (equality or bounded-range leading keys, target column trailing),
- rewrite as `UNION ALL` of two `ORDER BY col ASC|DESC LIMIT 1` seeks. This is only safe when the
- column is `NOT NULL` or both branches add `AND col IS NOT NULL`: `MIN`/`MAX` skip NULLs, while
- `ORDER BY ASC` returns them first. A rewrite on a nullable column without those guards silently
- changes results: a correctness regression.
+- **`SELECT MIN(col), MAX(col)`** over a large table scans every matching row. The exemption and
+ the rewrite below cover one shape only: a single-table query without `GROUP BY`. For any other
+ shape, judge the plan on its own. Exempt: no `WHERE` and an index that starts with the column, or
+ a `WHERE` that contains only equalities that pin every leading column of an index to one constant
+ value (`a = 1 AND b = 'x'`), with the column next in that index. The database answers those forms
+ from the index ends, so don't flag them. Otherwise, when an index returns rows sorted by the
+ column (it starts with the column, or every earlier column is pinned to one constant value) and
+ few rows at its ends fail the `WHERE`, the fix is `MIN`/`MAX` over a `UNION ALL` of two
+ `ORDER BY col ASC|DESC LIMIT 1` seeks, which keeps the one-row result.
+ Anything that leaves an earlier index column with more than one value breaks that order: a range,
+ `IN (...)`, `OR`, `!=` or a comparison with another column. Such an index cannot serve the seek,
+ so don't suggest the rewrite on it. An index that stores only a prefix of a column, such as
+ `meta_key_value (meta_key(50), meta_value(20))` on the HPOS order meta table, serves neither the
+ exemption nor the seek. The rewrite is only safe when the column is `NOT NULL` or both branches
+ add `AND col IS NOT NULL`: `MIN`/`MAX` skip NULLs, while `ORDER BY ASC` returns them first. A
+ rewrite on a nullable column without those guards silently changes results: a correctness
+ regression.
- **Dynamic `IN (...)` lists** must guard against empty input (`IN ()` is a SQL syntax error) and
sanitize members (`array_map( 'absint', ... )` for IDs, `$wpdb->prepare` placeholders for strings).
These are correctness and security findings, not performance ones.