title: Clarification of join order for creation of temporary tables
body: <p>I have a large query in mysql that involves joining multiple tables together. It's too slow, so I've done "explain" and see that it's creating a temporary table, which I suspect of taking most of the execution time.  I found some related information:</p>
  
  <ol>
  <li><a href="http://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html" rel="nofollow noreferrer">The mysql docs</a> describe conditions when a temporary table <em>might</em> be created. ("The server creates temporary tables under conditions <em>such as these</em>..." [Emphasis added])</li>
  <li>This related SO question <a href="https://stackoverflow.com/questions/13633406/using-index-using-temporary-using-filesort-how-to-fix-this">Using index, using temporary, using filesort - how to fix this?</a>, which provides a link to the doc and applies it in a specific case.</li>
  <li>This related SO question <a href="https://stackoverflow.com/questions/17208501/order-of-join-conditions-important">Order of join conditions important?</a> that talks about the order in which joins are evaluated.</li>
  </ol>
  
  <p>My query does not appear to meet any of the conditions listed in the docs #1, <em>in the order that the joins were written by me.</em>  By experimentation, however, I find that if I remove my <code>order by</code> clause, the temporary table is not created.  That makes me look at this rule from the doc:</p>
  
  <blockquote>
    <p>Evaluation of statements that contain an ORDER BY clause and a different GROUP BY clause, or for which the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue.</p>
  </blockquote>
  
  <p>This is the same rule that played in the example at #2 above, but in #2, the OP explicitly had columns from multiple tables in the <code>order by</code> clause, so that's at least superficially different.</p>
  
  <p>Moreover, when I look at the output from <code>explain</code>, it appears that the table that I listed first is not used first by the optimizer.  Putting down a pseudo-query for example:</p>
  
  <pre><code>select * from A 
  join B on A.c1=B.c1
  join C on A.c2=C.c2
  where A.c3='value'
  order by A.c4
  </code></pre>
  
  <p>I would say that my <code>order by</code> clause does use only columns from the "first table in the join queue" <em>based on the order that I've written the query.</em>  On the other hand, the output from <code>explain</code> suggests that it first considers table B then A.</p>
  
  <p>So here are the questions:</p>
  
  <ol>
  <li>Does the quoted rule above for use of temporary tables refer to the order that I write the tables or the order that the software chooses to evaluate them?</li>
  <li>If it's the order that I write them, does this mean that the order of the joins does impact performance? (Seems to contradict the claims at #3 above.)</li>
  <li>If it's the order that the software chooses to evaluate them, is there any way to coerce or trick it into selecting and order that doesn't use the table?</li>
  </ol>