What's the difference between delete from table_a and truncate table table_a in MySQL?

Question

What's the difference between DELETE FROM table_a and TRUNCATE TABLE table_a in MySQL?

Answer 1

• Truncate is much faster
• Truncate resets autoincrements
• Truncate is not transaction safe - it will autocommit
• Delete doesn't have to remove all rows

Truncate Documentation
Delete Documentation

Answer 2

Delete allows you to use a WHERE clause so only certain rows are deleted. Truncate will remove all rows as well as resetting any auto_increment columns you may have.

Answer 3

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.

Answer 4

Truncate resets the id count, if it auto increments

Answer 5

Not sure about MySQL (which is why my answer is wiki) but I can tell you one difference in at least one other DBMS. The delete from command honors transactions (allowing rollback) and triggers, making it a much slower way of clearing out a table. Truncate, on the other hand, just annihilates the rows without the possibility of rollback and without running any triggers on the deleted rows.

Answer 6

If there are no foreign keys, truncate table drops & recreates the table, which is much faster than deleting individual rows.

A truncate also resets any auto increment counters.

ON DELETE triggers do not fire when a table is truncated.

More information can be found in the MySQL online documentation.

Answer 7

Data deleted using delete is stored in temporary table hence you can use transaction with delete command whereas truncate command directly deletes the data hence you cannot recover using transaction(rollback). This is why truncate is faster than delete. Delete allows to use where clause whereas Truncate doesn't.

Answer 8

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.