title: Slow MySQL query is filling up my disk space
body: <p>this is the query I'm currently running (28 hours have passed!):</p>
  
  <pre><code>drop table if exists temp_codes;
  create temporary table temp_codes
      select distinct CODE from Table1;
  alter table temp_codes
      add primary key (CODE);
  
  drop table if exists temp_ids;
  create temporary table temp_ids
      select distinct ID from Table1;
  alter table temp_ids
      add primary key (ID);
  
  drop table if exists temp_ids_codes;
  create temporary table temp_ids_codes
      select ID, CODE
      from temp_ids, temp_codes;
  
  alter table temp_ids_codes
      add index idx_id(ID),
      add index idx_code(CODE); 
  
  insert into Table2(ID,CODE,cnt)
  select 
      a.ID, a.CODE, coalesce(count(t1.ID), 0)
  from 
      temp_ids_codes as a
      left join Table1 as t1 on (a.ID = t1.ID and a.CODE=t1.CODE)
  group by
      a.ID, a.CODE;
  </code></pre>
  
  <p>My table is this (Table1):</p>
  
  <pre><code>ID         CODE
  -----------------
  0001        345
  0001        345
  0001        120
  0002        567
  0002        034
  0002        567
  0003        567
  0004        533
  0004        008
  ......
  (millions of rows)
  </code></pre>
  
  <p>And i'm running the above query in order to get this (Table2):</p>
  
  <pre><code>ID  CODE    CNT
  1   008      0
  1   034      0
  1   120      1
  1   345      2
  1   533      0
  1   567      0
  2   008      0
  2   034      1
  ...
  </code></pre>
  
  <p>CNT are the counts of each Code for each ID..
  How can I implement this in the best way to improve performance and not use disk space?
  Thank you</p>