title: Why executes MariaDB all subqueries in select-statement before order by keyword, even if they are not necessary?
body: <p>we switched our database from mySQL8 to MariaDB10 a week ago and now we have massive performance problems. We figured out why: we are working with subqueries in select statements and <code>ORDER BY</code> pretty often. Here is an example:</p>
  <pre><code>SELECT id, (SELECT id2 FROM table2 INNER JOIN [...] WHERE column.foreignkey = table.id) queryResult
  FROM table
  WHERE status = 5
  ORDER BY column
  LIMIT 10
  </code></pre>
  <p>imagine, there are 1.000.000 entries in <code>table</code> which are affected if <code>status = 5</code>.</p>
  <p><strong>What happens in mySQL8:</strong> <code>ORDER BY</code> and <code>LIMIT</code> execute and after that the subquery (10 rows affected)</p>
  <p><strong>What happens in MariaDB10:</strong> the subquery executes (1.000.000 rows affected) and after that <code>ORDER BY</code> and <code>LIMIT</code></p>
  <p>Both queries are returning 10 rows but under MariaDB10 it is incredible slow because of that. Why is this happing? And is there an option in MariaDB which we should activate to avoid this? I know from mySQL8 that select subqueries will be executed when they are mentioned in <strong>ORDER BY</strong>. But if not they will be executed when the resultset is there.</p>
  <p>Info: if we do this, everything is fine:</p>
  <pre><code>SELECT *, (SELECT id2 FROM table2 INNER JOIN [...] WHERE column.foreignkey = outerTable.id) 
  FROM (
      SELECT id
      FROM table
      WHERE status = 5
      ORDER BY column
      LIMIT 10
  ) outerTable
  </code></pre>
  <p>Thank you so much for any help.</p>