title: Sqlite subselect much faster than distinct + order by
body: <p>I'm confused by the drastically different running times of the following two queries that produce identical output.  The queries are running on Sqlite 3.7.9, on a table with about 4.5 million rows, and each produce ~50 rows of results.</p>
  
  <p>Here are the queries:</p>
  
  <pre><code>% echo "SELECT DISTINCT acolumn FROM atable ORDER BY acolumn;" | time sqlite3 mydb
  sqlite3 mydb  8.87s user 15.06s system 99% cpu 23.980 total
  
  
  % echo "SELECT acolumn FROM (SELECT DISTINCT acolumn FROM atable) ORDER BY acolumn;" | time sqlite3 options
  sqlite3 mydb  1.15s user 0.10s system 98% cpu 1.267 total
  </code></pre>
  
  <p>Shouldn't the performance of the two queries be closer?  I understand that it may be the case that the query planner is performing the "sort" and "distinct" operations in different orders, but if so, does it need to?  Or should it be able to figure out how to do it fastest?</p>
  
  <p><strong>Edit</strong>: as requested here is the output of the "EXPLAIN QUERY PLAN" command for each query.  </p>
  
  <p>For the first (monolithic) query:</p>
  
  <pre><code>0|0|0|SCAN TABLE atable (~1000000 rows)
  0|0|0|USE TEMP B-TREE FOR DISTINCT
  </code></pre>
  
  <p>For the second (subquery) query:</p>
  
  <pre><code>1|0|0|SCAN TABLE atable (~1000000 rows)
  1|0|0|USE TEMP B-TREE FOR DISTINCT
  0|0|0|SCAN SUBQUERY 1 (~1000000 rows)
  0|0|0|USE TEMP B-TREE FOR ORDER BY
  </code></pre>