CODEWITHSUNDEEP
sqlmysqlperformancetruncate
ripper234
ripper234

Reputation: 230366

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

(I've tried this in MySql)

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

Upvotes: 16

Views: 12587

Answers (6)

Pervez Choudhury
Pervez Choudhury

Reputation: 2912

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

Upvotes: 10

davek
davek

Reputation: 22925

For MySql 5 using InnoDb as the storage engine, TRUNCATE acts just like DELETE without a WHERE clause: i.e. for large tables it takes ages because it deletes rows one-by-one. This is changing in version 6.x.

see

http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html

for 5.1 info (row-by-row with InnoDB) and

http://blogs.mysql.com/peterg/category/personal-opinion/

for changes in 6.x

Editor's note

This answer is clearly contradicted by the MySQL documentation:

"For an InnoDB table before version 5.0.3, InnoDB processes TRUNCATE TABLE by deleting rows one by one. As of MySQL 5.0.3, row by row deletion is used only if there are any FOREIGN KEY constraints that reference the table. If there are no FOREIGN KEY constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one."

Upvotes: 1

jvermolen
jvermolen

Reputation: 52

Truncate is on a table level, while Delete is on a row level. If you would translate this to sql in an other syntax, truncate would be:

DELETE * FROM table

thus deleting all rows at once, while DELETE statement (in PHPMyAdmin) goes like:

DELETE * FROM table WHERE id = 1
DELETE * FROM table WHERE id = 2

Just until the table is empty. Each query taking a number of (milli)seconds which add up to taking longer than a truncate.

Upvotes: -1

Eric Sabine
Eric Sabine

Reputation: 1165

Your question was about MySQL and I know little to nothing about MySQL as a product but I thought I'd add that in SQL Server a TRUNCATE statement can be rolled back. Try it for yourself

create table test1 (col1 int)
go
insert test1 values(3)
begin tran
truncate table test1
select * from test1
rollback tran
select * from test1

In SQL Server TRUNCATE is logged, it's just not logged in such a verbose way as DELETE is logged. I believe it's referred to as a minimally logged operation. Effectively the data pages still contain the data but their extents have been marked for deletion. As long as the data pages still exist you can roll back the truncate. Hope this is helpful. I'd be interested to know the results if somebody tries it on MySQL.

Upvotes: 6

Otávio Décio
Otávio Décio

Reputation: 74300

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

Upvotes: 30

Dillie-O
Dillie-O

Reputation: 29755

...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.

Upvotes: 23

Related Questions