3

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? Jul 14 '20 at 13:27
  • @Vérace billions with index Jul 14 '20 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! Jul 14 '20 at 20:48
1

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.

3
  • 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 (?,?),(?,?)..) ? Jul 14 '20 at 17:16
  • @CloudNineHorizon Exactly !!! Just as.a mysqldump does it, you writing it with id ranges is the same thing in principle. Jul 14 '20 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) Jul 14 '20 at 18:28
1

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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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