which one is fast load data infile or normal insertion?

Question

I have a table with 40 million records, and I want to copy this 40M into 80M like this-

INSERT INTO cdr (id,dataPacketDownLink, dataPacketUpLink,dataPlanEndTime,dataPlanStartTime,dataVolumeDownLink,dataVolumeUpLink,
dataplan,dataplanType, createdOn, deviceName, duration, effectiveDuration, HOUR, eventDate, msisdn, QUARTER, validDays, dataLeft,completedOn,evedate)SELECT null,dataPacketDownLink, dataPacketUpLink,dataPlanEndTime,dataPlanStartTime,dataVolumeDownLink,dataVolumeUpLink,
dataplan,dataplanType, createdOn, deviceName, duration, effectiveDuration, HOUR, eventDate, msisdn, QUARTER, validDays, dataLeft,completedOn,eve_date FROM cdr;

is it correct to use this one or I should use SELECT INTO OUTFILE and LOAD DATA INFILE for fast insertion? Or is there any better procedure than above two?

Thank you.

Answer 1

To avoid the big TEMPORARY TABLE created implicitely in this operation (inserting into the table being selected), you can try this option:

CREATE TABLE cdr2 AS
SELECT dataPacketDownLink, dataPacketUpLink,dataPlanEndTime,dataPlanStartTime,dataVolumeDownLink,dataVolumeUpLink,
dataplan,dataplanType, createdOn, deviceName, duration, effectiveDuration, HOUR, eventDate, msisdn, QUARTER, validDays, dataLeft,completedOn,evedate 
FROM cdr
UNION ALL
SELECT dataPacketDownLink, dataPacketUpLink,dataPlanEndTime,dataPlanStartTime,dataVolumeDownLink,dataVolumeUpLink,
dataplan,dataplanType, createdOn, deviceName, duration, effectiveDuration, HOUR, eventDate, msisdn, QUARTER, validDays, dataLeft,completedOn,evedate 
FROM cdr;

ALTER TABLE cdr2 ADD COLUMN (id INT(10) NOT NULL UNIQUE AUTO_INCREMENT)

once you checked that everything is ok in cdr2, you may:

• DROP TABLE cdr
• RENAME TABLE cdr2 TO cdr

Don't forget to add the missing indexes to the new table.