Fastest way to copy a table in mysql?
SqlMysqlSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
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.)
Solution 3 - Sql
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.
Solution 4 - Sql
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;
Solution 5 - Sql
Does create table mynewtable (select * from myoldtable) work in mysql? If so you can try it too.
Solution 6 - Sql
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 new_table SELECT * FROM old_table;
Solution 7 - Sql
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.
Solution 8 - Sql
if you are using MyISAM you can also copying and renaming the induvidual files . .MYD, .MYI, .FRM files in the backend
Solution 9 - Sql
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
Solution 10 - Sql
CREATE TABLE copy SELECT * FROM original;
Is a fast way but maybe not the quickest cause of indexes.