Insert into table from temporary table takes a long time

Morning folks,

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!!

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?

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;

Edit: Is this what you mean by index?

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;

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

---

Ok yes the answer was to properly index my tables, what I didn't realise however was

INDEX(A,B,C);

Is different from

INDEX(A),INDEX(B),INDEX(C);