How Has MariaDB's Handling of ORDER BY in Subqueries Changed, and What's the Recommended Solution?

MySQL/MariaDB Query Optimization Change: Order By in Subqueries

When querying with MySQL 5.5 or earlier, a subquery's ORDER BY clause was applied before the outermost query's SELECT. However, in MariaDB 10.0.14, this behavior has changed, and subquery ORDER BY clauses are now ignored when no LIMIT is present.

To address this, MariaDB recommends applying the ORDER BY clause to the outermost query or using LIMIT in the subquery.

Documented Behavior

MariaDB's documentation states that subqueries in the FROM clause are unordered sets of rows, and ORDER BY clauses are generally ignored in this context. The following quote from the documentation provides more detail:

"A "table" (and subquery in the FROM clause too) is - according to the SQL standard - an unordered set of rows. Rows in a table (or in a subquery in the FROM clause) do not come in any specific order. That's why the optimizer can ignore the ORDER BY clause that you have specified. In fact, SQL standard does not even allow the ORDER BY clause to appear in this subquery (we allow it, because ORDER BY ... LIMIT ... changes the result, the set of rows, not only their order)."

Therefore, it is suggested to treat subqueries as unordered sets and to place ORDER BY clauses in the outermost queries or use LIMIT as necessary.

The above is the detailed content of How Has MariaDB's Handling of ORDER BY in Subqueries Changed, and What's the Recommended Solution?. For more information, please follow other related articles on the PHP Chinese website!