INSERT INTO ... SELECT ... vs dumping/loading a file in MySQL

Question

What are the implications of using a INSERT INTO foo ... SELECT FROM bar JOIN baz ... style insert statement versus using the same SELECT statement to dump (bar, baz) to a file, and then insert into foo by loading the file?

In my messing around, I haven't seen a huge difference. I would assume the former would use more memory, but the machine that this runs on has 8GB of RAM, and I never even see it go past half used.

Are there any huge (or long term) performance implications that I'm not seeing? Advantages/disadvantages of either?

EDIT: This loading process happens on its own. Nothing else is querying the database at the time, so locking tables is not a big deal, sheer speed is.

Answer 1

One advantage of dump-and-load is that it does not lock the tables you are selecting from. If the select runs for a long time this can be rather important.

Answer 2

the issue is concurrent users. the INSERT-SELECT should hold locks to guarantee a duplicate and prevent any changes while you copy. The file method can have changes before you INSERT. If this matters depends on how you have FKs defined and what you are really doing this for. You may want to not have locks, so use a file.