• New optimizer_switch flags.  MySQL 8.0.21 adds two new flags for the optimizer_switch system variable, as described in the following list: 

• prefer_ordering_index  flag 

By default, MySQL attempts to use an ordered index for any ORDER BY  or GROUP BY  query that has a LIMIT  clause, whenever the optimizer determines that this would result in faster execution. Because it is possible in some cases that choosing a different optimization for such queries actually performs better, it is now possible to disable this optimization by setting the prefer_ordering_index  flag to off . 

The default value for this flag is on . 

• subquery_to_derived  flag 

When this flag is set to on , the optimizer transforms eligible scalar subqueries into joins on derived tables. For example, the query SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)  is rewritten as SELECT t1.a FROM t1 JOIN ( SELECT COUNT(t2.a) AS c FROM t2 ) AS d WHERE t1.a > d.c . 

This optimization can be applied to a subquery which is part of a SELECT , WHERE , JOIN , or HAVING clause; contains one or more aggregate functions but no GROUP BY  clause; is not correlated; and does not use any nondeterministic functions. 

The optimization can also be applied to a table subquery which is the argument to IN , NOT IN , EXISTS , or NOT EXISTS , and which does not contain a GROUP BY . For example, the query SELECT * FROM t1 WHERE t1.b < 0 OR t1.a IN (SELECT t2.a + 1 FROM t2)  is rewritten as SELECT a, b FROM t1 LEFT JOIN (SELECT DISTINCT 1 AS e1, t2.a AS e2 FROM t2) d ON t1.a + 1 = d.e2 WHERE t1.b < 0 OR d.e1 IS NOT NULL . 

Starting with MySQL 8.0.24, this optimization can also be applied to a correlated scalar subquery by applying an extra grouping to it, and then an outer join on the lifted predicate. For example, a query such as SELECT * FROM t1 WHERE (SELECT a FROM t2 WHERE t2.a=t1.a) > 0  can be rewritten as SELECT t1.* FROM t1 LEFT OUTER JOIN (SELECT a, COUNT(*) AS ct FROM t2 GROUP BY a) AS derived ON t1.a = derived.a WHERE derived.a > 0 . MySQL performs a cardinality check to make sure that the subquery does not return more than one row ( ER_SUBQUERY_NO_1_ROW ). See Section 13.2.11.7, “Correlated Subqueries” , for more information. 

This optimization is normally disabled, since it does not yield a noticeable performance benefit in most cases; the flag is set to off  by default. 

For more information, see Section 8.9.2, “Switchable Optimizations” . See also Section 8.2.1.19, “LIMIT Query Optimization” , Section 8.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations” , and Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization” . 

• XML enhancements.  As of MySQL 8.0.21, the LOAD XML  statement now supports CDATA  sections in the XML to be imported. 

• Casting to the YEAR type now supported.  Beginning with MySQL 8.0.22, the server allows casting to YEAR . Both the CAST()  and CONVERT()  functions support single-digit, two-digit, and four-digit YEAR values. For one-digit and two-digit values, the allowed range is 0-99. Four-digit values must be in the 

