Emptying mysql tables
There are actually two ways to wipe a mysql table either using delete or using truncate – which is better and why? So far my most commonly used form to wipe a table has simple been by using:
DELETE FROM tablename;
TRUNCATE tablename;
Besides the fact that it seems to be faster (even on small tables with few rows) – which in itself is a reason to favour this method - it also has another nice feature, which may be desirable. It resets the AUTO_INCREMENT field if it exists, and let the counter start over from 1.
Deleting rows in the table with the first statement will let the AUTO_INCREMENT counter continiue from where it was before the rows were deleted.
- Delete speed (Mysql 5)
- Truncate syntax (Mysql 5)
- Delete syntax (Mysql 5)