fastest way to copy a table in mysql

Question

which one is the fastest way to completely copy a table on mysql ?

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 theres another way ?

EDIT: I'm worried about the indexes being re-created, how does mysql proceed executing these statements ?

PS. can't use command-line tools like mysqldump, must be on-the-fly.

Answer 1

As with most questions like this, I would suggest you try it on your own particular installation, against the particular tables you wish to copy.

Answer 2

Try SELECT INTO, and use a variable as a go-between.

You'll have to create the receiving table, first, to have the same structure as the source table.

Best thing is, it's internal so it's fast. You'll lose your indexes, though.

Answer 3

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

Answer 4

You shouldn't be concerned in the speed of this operation.
As it's obviously shouldn't be done on a regular basis, and for the single case it isn't so important

Answer 5

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 should specify these before the SELECT statement: "

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

You can specify indices and data types (to avoid datatype conversion) in with both CREATE TABLE LIKE and CREATE TABLE SELECT. Which one is faster will depend on your setup.

Answer 6

CREATE TABLE copy LIKE original;

This creates all the indexes the original table had. At least it works this way in mysql 5.1.39.