I want to migrate data between 2 InnoDB tables.

Currently I'm running this query:

INSERT INTO table_a SELECT * FROM table_b;

If the dataset grows, what's the best way to avoid CPU overload?


  • 3
    CPU won't be your problem, Disk i/o will.
    – Nin
    Sep 10, 2012 at 12:19
  • I guess you truncate the table you migrate to all the time? then you should consider only migrating the difference to the last migration (aka saving the timestamp when last migration was done and only transfer rows with timestamp greater than that)
    – Najzero
    Sep 10, 2012 at 12:19
  • Maybe you want to temporarily delete indexes before moving, then recreating them after the successful migration.
    – biziclop
    Sep 10, 2012 at 12:20
  • Yes, before the query execution I disable indexes. But still I notice disk/cpu overload.
    – Mich Dart
    Sep 10, 2012 at 12:22
  • 1
    What storage engine are you using? Innodb or myisam? Same for both tables?
    – atxdba
    Sep 11, 2012 at 14:14

2 Answers 2


Since you are using InnoDB I would like to suggest the following:


If you do INSERT, UPDATEs, and DELETEs, bulk load the table like this:

CREATE TABLE table_new LIKE table_a;
INSERT INTO table_new SELECT * FROM table_b;
ALTER TABLE table_a RENAME table_old;
ALTER TABLE table_new RENAME table_a;
DROP TABLE table_old;

If you do nothing but INSERTs and SELECTs, load new entries into the table. Assuming the primary key of table_a andtable_b is id, perform the load like this:

CREATE TABLE table_new LIKE table_a;
INSERT INTO table_new SELECT B.* FROM table_b B
INSERT INTO table_a SELECT * FROM table_new;
DROP TABLE table_new;

SUGGESTION #2 : Tune InnoDB for Multiple CPUs

Make sure you are using MySQL 5.5. If you have MySQL 5.1.38 or above, you must install the InnoDB Plugin. If you have MySQl 5.1.37 or prior, just upgrade to MySQL.

Once you have do that (or if you already have MySQL 5.5), you must tune InnoDB for Multiple CPUs. Rather than reinvent the wheel, here are my past posts on how and why to do so:

Give it a Try !!!

I could suggest other things such as buffers, log files, and so forth. I only addressed just these two concerns.

  • Let me understand your first suggestion. You are using a temp table (table_new) where data get saved. At the end, you perform an SQL query pretty similar to mine... Is yours more efficient? Will I have disk i/o problems?
    – Mich Dart
    Oct 20, 2012 at 16:21
  • What about the data inserted into A before A finishes getting copied to B? Won't B be incomplete? Jul 27, 2017 at 16:48

In this case, normally, the best solution is a mysqldump using the --tab option like this:

mysqldum --tab=/path/to/serverlocaldir --single-transaction <database> table_a

tab option produce 2 file, one file -table_a.sql- that contains only the table create statement and the oher file -table_a.txt- contains tab-separated data.

Now you can create your new table

create table table_b like table_a;

Then you simply load data in your new table via LOAD DATA without care for the table's name.

LOAD DATA INFILE '/path/to/serverlocaldir/table_a.txt' 

LOAD DATA is usually 20 times faster than using INSERT statements.

Hope this helps

  • This is a good solution, but requires a manual intervention or a shell-based command. I'll take it as valid option. Thank you
    – Mich Dart
    Oct 20, 2012 at 16:13

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.