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.