title: MySQL creating temp table then join faster than left join
body: <p>I have a <code>LEFT JOIN</code> that is very expensive:</p>
  
  <pre><code>    select X.c1, COUNT(Y.c3) from X LEFT JOIN Y on X.c1=Y.c2 group by X.c1; 
  </code></pre>
  
  <p>After several minutes (20+), it still does not finish. But I want all rows in X. So I really do need a <code>LEFT JOIN</code> at some point.</p>
  
  <p>It appears that I can hack my way around this to return the result set I am looking for by using a temp table in less than two minutes. I first trim down table Y so that it only contains rows in the join. </p>
  
  <pre><code>CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS 
  (select X.c1 as t, COUNT(Y.c2) as c from X 
  INNER JOIN Y where X.c1=Y.c2 group by X.c1);
  
  select X.c1, table2.c from X 
  LEFT JOIN table2 on X.c1 = table2.t; 
  </code></pre>
  
  <p>This finishes in under two minutes. </p>
  
  <p>My questions are:</p>
  
  <p>1) Are they equivalent?</p>
  
  <p>2) Why is the second so much faster (why doesn't MySQL do this kind of optimization), meaning, do I need to do these kinds mysql?</p>
  
  <p>EDIT: additional info: C1, C2 are <code>BIGINTS</code>. C1 is unique but there can be many C2s that all point to the same C1. As far as I know, I have not indexed any tables. X.C1 is an _id column that Y.c2 refers to. </p>