mathiasfk mathiasfk - 4 months ago 16
MySQL Question

Load data infile again or insert from table?

I'll have two structurally identical tables, one with old and new data, and one temporary table only with the new data, with which I'll make some further processing.

Is it better/faster to

load data local infile
twice, or should I load only into the temporary table, and then
select *
and insert into the other table?

Answer

When you say should I... that implies that you have some criteria. Do you need to make the import run faster? Or make it easy to write the code? Or preserve data changes you did in the temporary table?

I would probably favor insert into <finaltable> select * from <temptable>.

  • It's very easy to code.
  • If you had made any changes to the data in the temp table (even implicit changes to conform to data type formats as you loaded the data), those changes will be preserved.
  • It probably runs fast. Though performance always varies depending on your system hardware, your concurrent query load, etc. Run your own speed test.

But there's no right answer to this question. It depends what you are trying to accomplish.