MainelyDesign.com Blog

Difference Between Truncate and Empty (Delete From) in Mysql

Posted on 06/23/2010 at 02:30 pm by Kevin Wentworth
Viewed 33,293 times | 1 comment

I use Navicat for administering all of my MySQL databases.  It has a command that I always use- empty table.  I noticed today that all along there has been a command right below it for truncate table.  Hmmm.  I figured this would result in the same outcome and wondered what the difference is between emptying a mysql table and truncating the same table.

Use TRUNCATE TABLE to Start Fresh

After doing some research I found out that when you truncate a MySQL table, you are actually dropping the database table and re-creating the table from its SQL create statement.  This has one major implication, besides being faster (among other things), it means that all of your autoincrement fields will start over at 1.  I really like that outcome- nice and tidy.

Use DELETE FROM TABLE to Not Re-Use IDs

What I usually do (at least in CakePHP) is a deleteAll, which executes a DELETE FROM mysql_table_name which clears out all of the rows in a table.  The major difference- MySQL goes row by row, deleting each row, resulting in a performance hit vs. truncating the same data.  The consequence of only deleting data and not re-creating the table, is that your autoincrement value will remain unchanged.

I confess, the ID thing isn't that much of a big deal.  I guess I must really love sequentially numbered database tables.

Cheers,
-Kevin Wentworth