Why 'delete from table' takes a long time when 'truncate table' takes 0 time?

Question

(I've tried this in MySql)

I believe they're semantically equivalent. Why not identify this trivial case and speed it up?

Answer 1

truncate table cannot be rolled back, it is like dropping and recreating the table.

Answer 2

...just to add some detail.

Calling the DELETE statement tells the database engine to generate a transaction log of all the records deleted. In the event the delete was done in error, you can restore your records.

Calling the TRUNCATE statement is a blanket "all or nothing" that removes all the records with no transaction log to restore from. It is definitely faster, but should only be done when you're sure you don't need any of the records you're going to remove.

Answer 3

Delete from table deletes each row from the one at a time and adds a record into the transaction log so that the operation can be rolled back. The time taken to delete is also proportional to the number of indexes on the table, and if there are any foreign key constraints (for innodb).

Truncate effectively drops the table and recreates it and can not be performed within a transaction. It therefore required fewer operations and executes quickly. Truncate also does not make use of any on delete triggers.

Exact details about why this is quicker in MySql can be found in the MySql documentation: http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html