MySQL efficiently copy all records from one table to another

mysql sql insert mysql5
crmpicco picture crmpicco · Jun 25, 2012 · Viewed 38.3k times · Source

Is there a more-efficent, less laborious way of copying all records from one table to another that doing this:

INSERT INTO product_backup SELECT * FROM product

Typically, the product table will hold around 50,000 records. Both tables are identical in structure and have 31 columns in them. I'd like to point out this is not my database design, I have inherited a legacy system.

Answer

Marcus Adams picture Marcus Adams · Jun 25, 2012

There's just one thing you're missing. Especially, if you're using InnoDB, is you want to explicitly add an ORDER BY clause in your SELECT statement to ensure you're inserting rows in primary key (clustered index) order:

INSERT INTO product_backup SELECT * FROM product ORDER BY product_id

Consider removing secondary indexes on the backup table if they're not needed. This will also save some load on the server.

Finally, if you are using InnoDB, reduce the number of row locks that are required and just explicitly lock both tables:

LOCK TABLES product_backup WRITE;
LOCK TABLES product READ;
INSERT INTO product_backup SELECT * FROM product ORDER BY product_id;
UNLOCK TABLES;

The locking stuff probably won't make a huge difference, as row locking is very fast (though not as fast as table locks), but since you asked.

Related questions

How to copy a row and insert in same table with a autoincrement field in MySQL?

In MySQL I am trying to copy a row with an autoincrement column ID=1 and insert the data into same table as a new row with column ID=2. How can I do this in a single query?

Read More
MySQL Error 1264: out of range value for column

As I SET cust_fax in a table in MySQL like this: cust_fax integer(10) NOT NULL, and then I insert value like this: INSERT INTO database values ('3172978990'); but then it say `error 1264` out of value for column …

Read More
How to do a batch insert in MySQL

I have 1-many number of records that need to be entered into a table. What is the best way to do this in a query? Should I just make a loop and insert one record per iteration? Or is there …

Read More