MySQL – Best way to remove all data from a table
There are multiple ways in which to remove all data from a MySQL Table.
The first is to use the DROP TABLE command, which will remove the entire table with data. This basically removes the entire table, you will not be able to recover the structure or data.
The second is DELETE * FROM Table which will remove the data, leaving the auto-increment values alone, as well as the rest of the table structure. This method is very slow on large tables using Innodb. This is because each record has to be written to the log. In general, you should never use this method to remove all data from a table.
The third method is to issue a TRUNCATE TABLE command which will quickly remove all the data from the table, reset the auto-increment values (but leave them as auto-increment columns, so they will just start at 1 and go up from there again).
Lastly, you can use the nuclear option, which is as follows :
mysqldump -ppassword YourDBName YourTblName –no-data dumpfile | mysql dumpfile
This will drop the table, and then recreate it all from scratch.
