I have around 40 million rows in a MySQL table and I want to copy this table to another table in same database. What is the most efficient way of doing this? How much time will it take (approx.)?
MySQL Table Copy – How to Copy Data Between Tables in Same Database?
bulk-insertinsertMySQL
Related Question
- MySQL – Copy Table Comments from One Database to Another
- Mysql – Fastest way to insert data into thesql from csv
- MySQL – INSERT and DELETE in Single Operation to Move Data Between Tables
- Mysql – INSERT … SELECT on a huge MySQL table
- MySQL InnoDB – How to Copy Subset of Columns from One InnoDB Table to Another Without Locking
- Mysql – Migrating 300 GB thesql database from one server to another cleanly with minimum downtime
- MySQL – How to Backup Database from One Cloud Server to Another
- MySQL – How to Copy Table from One Server to Another
Best Answer
Suppose you have
mydb.mytb
and you want to createmydb.mytbcopy
I have five(5) approaches to doing this copy
APPROACH #1
In the
mysql
client, run the followingAPPROACH #2
APPROACH #3
APPROACH #4
APPROACH #5
ANALYSIS
If you want to copy
mydb.mytb
to an already existing tablemydb.mytbcopy
, and the two tables have identical structures:APPROACH #6
Like #APPROACH 1, #APPROACH 6 would have a single transaction of 40 million rows
APPROACH #7
This approaches does not drop the table. It simply generates the INSERTs
EPILOGUE
I cannot give you a time estimate since I do not know the make up of the DB Server, table structure, index layout, and things like these.
GIVE IT A TRY !!!