title: Insert into table from temporary table takes a long time
body: <p>Morning folks,</p>
  
  <p>I have a temporary table with 135,000 rows and 24 columns, of those rows I need to insert about 8,000 of them into an 8 column table. If run my insert the first time round (i.e. when my 8 column table is empty) it runs in about 6 seconds. When I run the same query again (this time round it shouldn't insert anything as the rows have already been inserted) it takes 30 minutes!!</p>
  
  <p>I've been unable to re-create this with a small simplified sample, but here's some sql for you to run anyway. It's running the last insert when the programme table has entries which causes the problems. Can anyone shed some light as to why this might be?</p>
  
  <pre><code>CREATE TEMPORARY TABLE TVTEMPTABLE (
  PROGTITLE TEXT,  YR YEAR, DIRECTOR TEXT, GENRE TEXT
  );
  
  CREATE TABLE GENRE (
  GENREID INT NOT NULL AUTO_INCREMENT, GENRE TEXT, PRIMARY KEY(GENREID)
  ) ENGINE=INNODB;
  
  CREATE TABLE PROGRAMME (
  PROGRAMMEID INT NOT NULL AUTO_INCREMENT, GENREID INT NOT NULL, PROGTITLE TEXT, YR YEAR,
  DIRECTOR TEXT, PRIMARY KEY(PROGRAMMEID), INDEX (GENREID), FOREIGN KEY (GENREID) REFERENCES GENRE(GENREID)
  ) ENGINE=INNODB;
  
  INSERT INTO GENRE(GENRE) VALUES
  ('Consumer'),('Entertainment'),('Comedy'),('Film'),('Drama'),('Sport'),
  ('Sitcom'),('Travel'),('Documentary'),('Factual');
  
  INSERT INTO TVTEMPTABLE(PROGTITLE, YR, DIRECTOR, GENRE) VALUES
  ('Breakfast','2011','n/a','Consumer'),('Breakfast','2011','n/a','Consumer'),
  ('Wanted Down Under','2011','n/a','Entertainment'),('Wanted Down Under','2011','n/a','Entertainment'),
  ('Lorraine','2011','n/a','Comedy'),('Lorraine','2011','n/a','Comedy'),
  ('Supernanny USA','2011','n/a','Film'),('Supernanny USA','2011','n/a','Film'),
  ('Three Coins in the Fountain','2011','n/a','Drama'),('Three Coins in the Fountain','2011','n/a','Drama'),
  ('The Wright Stuff','2011','n/a','Sport'),('The Wright Stuff','2011','n/a','Sport'),
  ('This Morning','2011','n/a','Sitcom'),('This Morning','2011','n/a','Sitcom'),
  ('Homes Under the Hammer','2011','n/a','Travel'),('Homes Under the Hammer','2011','n/a','Travel'),
  ('LazyTown','2011','n/a','Documentary'),('LazyTown','2011','n/a','Documentary'),
  ('Jeremy Kyle','2011','n/a','Factual'),('Jeremy Kyle','2011','n/a','Factual');
  
  INSERT INTO PROGRAMME (
      PROGTITLE, GENREID, YR,
      DIRECTOR)
  SELECT
      T.PROGTITLE, MAX(G.GENREID),
      MAX(T.YR), MAX(T.DIRECTOR)
  FROM
      TVTEMPTABLE T
      INNER JOIN GENRE G ON G.GENRE=T.GENRE
      LEFT JOIN PROGRAMME P ON P.PROGTITLE=T.PROGTITLE    
  WHERE P.PROGTITLE IS NULL
  GROUP BY T.PROGTITLE;
  </code></pre>
  
  <p>Edit: Is this what you mean by index?</p>
  
  <pre><code>CREATE TEMPORARY TABLE TVTEMPTABLE (
  PROGTITLE VARCHAR(50),  YR YEAR, DIRECTOR TEXT, GENRE VARCHAR(50), INDEX(PROGTITLE,GENRE)
  );
  
  CREATE TABLE PROGRAMME (
  PROGRAMMEID INT NOT NULL AUTO_INCREMENT, GENREID INT NOT NULL, PROGTITLE VARCHAR(50), YR YEAR,
  DIRECTOR TEXT, PRIMARY KEY(PROGRAMMEID), INDEX (GENREID,PROGTITLE), FOREIGN KEY (GENREID) REFERENCES GENRE(GENREID)
  ) ENGINE=INNODB;
  </code></pre>
  
  <p>Edit 2: This is the result from the desc extended. After indexing (I may have done this wrong?). The insert still takes a long time</p>
  
  <p><img src="https://i.stack.imgur.com/zfYOC.jpg" alt="alt text"></p>