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 32,061 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

Bookmark and Share

Tags for Difference Between Truncate and Empty (Delete From) in Mysql

Mysql | Database | Web Server Admin | Usage | Software | Truncate

Comments for this Posting

Posted by photo recovery

on 31/7/10

Be careful with delete and replace:
If you run a replace on existing keys from the table A and table B references C with a forgein key constraint ON DELETE CASCADE, then table A will be updated - but table B will be emptied due to the DELETE before INSERT.

Sorry, comments are closed for this posting.

Please Email Kevin if you have any questions. Thanks!

Meet Site Avenger - Hosted Content Management System

Powered By: Site Avenger | Site Production: Saco Design