Fastest way to copy a table in mysql?

Question

I want to copy a table to MySQL. What is the fastest way? Like this?

CREATE TABLE copy LIKE original; INSERT INTO copy SELECT * FROM original; 

or

 CREATE TABLE copy SELECT * FROM original; ALTER TABLE copy ADD PRIMARY KEY (id); 

or is there another way?

EDIT: I'm worried about re-creating indexes, how does mysql continue to execute these statements?

PS. cannot use command line tools like mysqldump should be on the fly.

Answer 1

This immediately copies the table structure, but not the data:

 CREATE TABLE copy LIKE original; 

This creates all the indexes on the original table.

It works this way in mysql 5.1.39 .

Answer 2

The fastest way to use MyISAM tables when storing indexes), and possibly other storage mechanisms:

 CREATE TABLE copy LIKE original; ALTER TABLE copy DISABLE KEYS; INSERT INTO copy SELECT * FROM original; ALTER TABLE copy ENABLE KEYS; 

You want to disconnect your keys to load the database, and then recreate the keys at the end.

Similarly, for InnoDB :

 SET unique_checks=0; SET foreign_key_checks=0; ..insert sql code here.. SET unique_checks=1; SET foreign_key_checks=1; 

(As stated in the comments.)

Answer 3

From the manual:

"CREATE TABLE ... SELECT does not automatically create any indexes for you. This is done intentionally to make the statement as flexible as possible. If you want to have indexes in the created table, you must indicate them before the SELECT statement:"

 CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo; 

You can specify indexes and data types (to avoid data type conversion) using both CREATE TABLE LIKE and CREATE TABLE SELECT . Which one will be faster will depend on your setup.

Answer 4

Does create table mynewtable (select * from myoldtable) in mysql? If so, you can try.

Answer 5

The best way to copy the structure and all records from one table to another table (by creating a new table) is this query ...

CREATE TABLE new_table LIKE old_table; INSERT new_table SELECT * FROM old_table;

Answer 6

To copy using indexes and triggers, run the following 2 queries:

 CREATE TABLE newtable LIKE oldtable; INSERT newtable SELECT * FROM oldtable; 

To copy only structure and data, use the following command:

 CREATE TABLE tbl_new AS SELECT * FROM tbl_old; 

Answer 7

Try SELECT INTO and use variable as an intermediate.

You will have to create a receive table, first, have the same structure as the original table.

Best of all, it's internal, so it's fast. However, you will lose your indexes.

Answer 8

if you use MyISAM, you can also copy and rename induvidual files. .MYD, .MYI, .FRM files in the backend

Answer 9

Perhaps you can take a look at SHOW CREATE TABLE .

Actions:

• Go to phpmyadmin

• Go to SQL

Run this request

 SHOW CREATE TABLE `the_old_table`; 

• Click Options Check Full Texts and click Go.

The result is a complete CREATE TABLE statement. Edit the request until you are happy.

Resource: http://dev.mysql.com/doc/refman/5.7/en/show-create-table.html

Answer 10

 CREATE TABLE copy SELECT * FROM original; 

This is a quick way, but perhaps not the fastest reason for indexes.