Thursday, 16 October 2014

Difference between delete from table_a and truncate table_a in MySQL

Delete:
delete rows and space allocated by mysql
data can be roll back again
you can use it with WHERE clause
Truncate:
It is similar to delete. But the difference is you can't roll back data again and you cann't use WHERE clause with it.

---------------------------------------------------------------------------------------------
Truncate:
  • Works by deallocating all the data pages in the table.
  • Will delete all data - you cannot restrict it with a WHERE clause.
  • Deletions are not logged.
  • Triggers are not fired.
  • Cannot be used if any foreign keys reference the table.
  • Resets auto id counters.
  • Faster.
Delete:
  • Works by deleting row by row.
  • Can be restricted with a WHERE clause.
  • Deletions are logged in the transaction log (if you have logging on obviously) so the delete can be recovered if necessary (depending on your logging settings).
  • Triggers are fired.
  • Can be used on tables with foreign keys (dependant on your key cascade settings).
  • Slower.

0 comments:

Post a Comment