Geek asks and answers

How to delete a very large mysql table?

Whenever I try to delete a large table using

truncate table the_huge_table;

and wait a few minutes, I see that nothing is happening. On the other hand, I don’t want to delete the whole table because I’m not sure how to regenerate it, so I wonder what is the best way to easily empty this monster?

+3
source share
3 answers

SHOW CREATE TABLE the_huge_table will show you how to recreate the table if you release it.

Another option is to clone the table structure:

CREATE TABLE cloned LIKE the_huge_table;
RENAME TABLE the_huge_table TO drop_me, cloned TO the_huge_table;
DROP TABLE drop_me;
+9
source 
mysqldump --no-data dbname tablename > /tmp/backup.sql
mysql -e 'drop table tablename' dbname
mysql dbname < /tmp/backup.sql
+4
source

Copy the structure to a new, empty table.
Remove the old table.
Rename the blank copy.

CREATE TABLE copy_huge_table LIKE the_huge_table;
DROP TABLE the_huge_table;
RENAME TABLE copy_huge_table TO the_huge_table;
+1
source

More articles:


All Articles