6

Currently, I am trying to copy data from TABLE1 to TABLE2.

In terms of insertions performance, would it be the same or faster if I would to do

  • BULK INSERT manually (i.e BULK insert every 10K records into TABLE2 via INSERT INTO TABLE2 VALUES (1,2), (5,5), ...), versus
  • INSERT INTO TABLE2 SELECT * FROM TABLE1
3
  • How many records are in your table1?
    – Vérace
    Commented Jul 14, 2020 at 13:27
  • @Vérace billions with index Commented Jul 14, 2020 at 17:10
  • I'd do an experiment with a subset of my table_1! On a test system add a WHERE clause and see how it pans out - I thought that the MVCC architecture meant that it doesn't matter if it's 1 record being updated or 1 billion! But see @RolandoMYSQLDBA's answer!
    – Vérace
    Commented Jul 14, 2020 at 20:48

3 Answers 3

4

You have to go with the BULK INSERT.

WHY NOT INSERT INTO TABLE2 SELECT * FROM TABLE1 ???

Running INSERT INTO TABLE2 SELECT * FROM TABLE1 requires a single transaction.

Imaging how populated an undo log will be to perform a single rollback.

If that transaction fails and rolls back, you create lots of table fragmentation.

Why BULK INSERT manually ???

This takes a lot of pressure off the InnoDB Storage Engine for holding large undo information.

EXAMPLE : mysqldump

Have you ever noticed when reloading a mysqldump, hundreds or thousands of rows at a time are being inserted ? If you grep a mysqldump like this:

grep "^INSERT" dump.sql

You will see many lines with INSERTs. Each INSERT is an extended insert by default. That allows 100's of rows to be inserted per INSERT command. So, the principle you already suggested of BULK INSERT 10K rows at a time is perfectly acceptable.

4
  • 2
    what if I were to use a script that does INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE id >= X and id < Y (where X and will be incremented by 10K after each cycle/transaction). is this actually the same as doing BULK INSERT of 10K records (aka INSERT INTO TABLE2 VALUES (?,?),(?,?)..) ? Commented Jul 14, 2020 at 17:16
  • @CloudNineHorizon Exactly !!! Just as.a mysqldump does it, you writing it with id ranges is the same thing in principle. Commented Jul 14, 2020 at 18:26
  • Essentially, you are mixing the two methods, which is perfectly fine. You spare the InnoDB Storage Engine by having it hold 10K of rows in the undo logs instead of all the rows (billions you said in the comment) Commented Jul 14, 2020 at 18:28
  • The answer starts with an unclear question, even with "???", although the sentence is meant like "!!!". When I read it the first time, I thought you were saying that insert into table select... is the way to go, that is what "Why not doing ..." means in everyday life: it tells you to do it. In everyday life, it means to say: tell me what that stands against it, I cannot think of even one. The answer should not start with a question mark. At best, it should be: "Why you should not INSERT INTO TABLE2 SELECT * FROM TABLE1". Commented Sep 14, 2022 at 15:44
2

Do it in chunks. That is, walk through the source, copying 1K rows from the source to the destination. And COMMIT. Do the walking based on the PRIMARY KEY for maximal read efficiency.

I cover a lot of the issues here: http://mysql.rjweb.org/doc.php/deletebig (It discusses DELETEs, but the underlying principles work for SELECT.)

0

Warning: As you see by the helpful remarks of the professionals, do not follow this answer. It was a guess, vaguely remembered this, and I was hoping for such remarks to see whether this is wrong. And it is. I leave it for everyone to know what is wrong. If you ask for it, I can also delete it.


WRONG:

Since nobody has answered it up to now like this, I dare to fill the gap.

"batch insert", not "bulk insert"

First of all, "bulk insert" might better be replaced by "batch insert". In TSQL, "bulk insert" is the command to import from a file, see BULK INSERT in MYSQL.

mysqldump cannot be an example for how insert works inside a db

I dare to say that the question has nothing to do with for example a db that was dumped and gets loaded since reading from a file is always without the risks of locks and can therefore be done in another way than a query inside the db, there can never be any case of duplicates, everything can be done without locks in many processes at a time.

To make this clearer: if you have db locks so that one operation does not allow another before its ending although you would normally not need to wait for it to end since you already know that there are no duplicates or FK checks, you can get around the locks by dumping a query at first in a file and afterwards by reloading from that file again. Then you have the same power that a mysqldump and load has. Might be good as a trick for other things as well, not just for the question MySQL Create Table as SELECT, but for the question here, it is clear that you need a trick, and if you need a trick for it outside of the db, the db itself does not have this trick at hand. I did not read any other trick to get this done from inside a db, for example to save the data to another temp table (instead of the file trick) and then make a full table select from that temp table to get around the locks. Never seen or heard. This long text shall say that the accepted answer might not hit the point.

Faster

This shortly answers the question. As far as I can remember, a "manual batch insert" was always slower than a "full table select insert". I remember this from a large migration of an old db into a newly structured db that was done without mysqldump.

Batch is not always batch

And it is clear that speed will differ between selecting a full table and selecting batches of it, even if the full table gets selected in batches as well in the end.

Python example as a hint

This happens in other programming languages as well. Taken from When writing csv from CF to bucket: 'with open(filepath, "w") as MY_CSV:' leads to "FileNotFoundError: [Errno 2] No such file or directory:", in Python:
instead of the pd.to_csv() with a chunksize parameter of 5000 which needed 62s for 700k rows to be loaded and stored into a csv, the csv module's writer that used batches as well took more than the 9 minutes (stopped then, it might have lasted much longer).

Logs overhead and fragmentation risks

The question is about speed and not about logs or fragmentation risks, and I even doubt that logs and fragmentation risks are worse off with a "full table insert" than with other ways.

Indirect hints from row-wise steps

There is another point: INSERT INTO TABLE2 SELECT * FROM TABLE1 cannot be run together with ON DUPLICATE DELETE since that would slow it down to a row-wise workflow - it just cannot do it.
To get this done, you have to use VALUES() instead:

INSERT INTO {tbl_name}({attributes}) 
VALUES {insert_placeholders of the batch}
ON DUPLICATE KEY UPDATE {update_placeholders}

This shows that the query that the OP calls "bulk insert" (and is a "batch insert" I guess) can do row-wise workflows. Only to be able to do it shows that the workflow is coded in another way. If you instead delete all of the rows that need to be replaced or to be deleted at first and only afterwards insert anything new with a "full table insert", you get around any row-wise workflow. It is then like

replace into some_table
select somecolumn from othertable

Taken from MySQL insert on duplicate key; delete?, just without any row-wise workflow.

Wrap up

WRONG: I am guessing here a lot, do not trust this answer. I just dare to say that INSERT INTO TABLE2 SELECT * FROM TABLE1 has the minimum overhead, and is always the fastest choice, and is often even by far the fastest, and this regardless of the memory, since it uses the best batch sizes without any need to tell it. And I just guess that it blocks other queries around that table until its task is done, which might sometimes lead to a locked table alert but will be the shortest way to go.

2
  • 1
    INSERT INTO ... SELECT will be notoriously slow on large InnoDB tables due to the way InnoDB handles such transactions, as already listed in other answers. I have experience trying this, it's not pretty. If you have a few million rows, batches by primary key would work waaay faster (to the point that INSERT INTO ... SELECT may just OOM your server). Commented Jan 3, 2023 at 10:05
  • 1
    Agree with @SergeyKudriavtsev - I shot myself in the foot this way just last night 😂
    – John Rix
    Commented Jan 30 at 23:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.