CODEWITHSUNDEEP
sqlmysql
Anon
Anon

Reputation: 927

Fastest way to copy a table in mysql?

I want to copy a table in 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 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.

Upvotes: 86

Views: 102247

Answers (10)

Anil Chahal
Anil Chahal

Reputation: 2613

Best way to copy structure and all entries from one table to another table (by creating new table) is this query...

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

Upvotes: 5

ctbrown
ctbrown

Reputation: 2361

The fastest way using MyISAM tables while preserving indexes) and maybe other storage engines is:

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

You want to disable your keys for your database load 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 pointed out in the comments.)

Upvotes: 50

Zigri2612
Zigri2612

Reputation: 2310

To copy with indexes and triggers do these 2 queries:

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

To copy just structure and data use this one:

CREATE TABLE tbl_new AS SELECT * FROM tbl_old;

Upvotes: 12

Julian
Julian

Reputation: 4666

Maybe you could take a look at SHOW CREATE TABLE.

Steps to take:

  • Go to phpmyadmin

  • Go to SQL

Execute this query

SHOW CREATE TABLE `the_old_table`;
  • Click options Check "Full texts" and press Go.

The result is a full CREATE TABLE statement. Edit the query until you are happy.

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

Upvotes: 1

Thor
Thor

Reputation: 1

CREATE TABLE copy SELECT * FROM original;

Is a fast way but maybe not the quickest cause of indexes.

Upvotes: 0

ceteras
ceteras

Reputation: 3378

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

CREATE TABLE copy LIKE original;

This creates all the indexes the original table had.

It works this way in mysql 5.1.39.

Upvotes: 51

Aravind
Aravind

Reputation: 21

if you are using MyISAM you can also copying and renaming the induvidual files . .MYD, .MYI, .FRM files in the backend

Upvotes: 2

dnagirl
dnagirl

Reputation: 20456

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.

Upvotes: 14

amphetamachine
amphetamachine

Reputation: 30595

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.

Upvotes: 3

Draco Ater
Draco Ater

Reputation: 21226

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

Upvotes: 6

Related Questions