Fastest way to copy a table in mysql?

sql

data-integrity

mysql-performance

database-optimization

byNikita Barsukov·Oct 21, 2024

For a rapid duplication of a MySQL table, utilize the following succinct command:

CREATE TABLE new_table AS SELECT * FROM old_table;

In one swoop, this line replicates the old_table's structure and data into new_table. However, it doesn't come with index preservation, which is a crucial aspect in maintaining the database performance. Feel free to drop a LIKE and use:

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

These twin beasts CREATE TABLE ... LIKE and INSERT INTO ... SELECT ensures your table structure, indexes, and data are all copied intact.

Understanding the Whys and How-tos

Preserving Indexes (because they matter)

CREATE TABLE ... LIKE is your friend when you want to preserve both structure and indexes from old_table to new_table.

When Speed Matters (it always does, right? 😎)

Improve your data copying speed by disabling key checks. MySQL will thank you for it.

SET unique_checks=0; -- Life is more fun without checks
SET foreign_key_checks=0; -- You don't need my approval. Just go!
ALTER TABLE new_table DISABLE KEYS; -- Don't. Touch. My. Keys.
INSERT INTO new_table SELECT * FROM old_table; -- Let the games begin
ALTER TABLE new_table ENABLE KEYS; -- Keys, you may rise again
SET unique_checks=1; -- Back to the checks and balances
SET foreign_key_checks=1; -- Alright, who has my foreign keys?