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);
精彩评论