SQLite Forum

Should SQLite be able to optimize this query?
This optimization is now on trunk. The following description is intended to
serve as archival documentation.

If a subquery in the FROM clause has an ORDER BY, that ORDER BY is omitted
if all of the following conditions are also true:

  1.  There is no LIMIT clause in the subquery
  2.  The subquery was not one of the virtual subqueries added internally
      by SQLite for window-function processing
  3.  The subquery is not part of the FROM clause in an UPDATE-FROM statement
  4.  The outer query does not use any aggregate functions other than
      the built-in count(), min(), and/or max() functions.
  5.  Either the outer query has its own ORDER BY clause or else the
      subquery is one term of a join.
  6.  The omit-ORDER-BY optimization is not disabled by the

The omit-ORDER-BY optimization can be disabled (item 6 above) using the
following command in the CLI:

     .testctrl optimizations 0x40000