I’m writing this down to warn you and to burn it into my memory.
For one of my projects I am using MySQL with InnoDB tables. Which are great, by the way. So I have this really huge table – about 8 GBytes with few million rows – that I want to empty. Without much thinking I start DELETE FROM mytable. And I wait. And wait. And wait. Then I go for a coffee. I come back and wait. Then I wait for some more.
Eventually, in about one hour, I got bored. So, I terminate the query. That was without much thinking too. Can you guess why?
Right. MySQL uses transactions for InnoDB tables. Interrupted query triggered a rollback operation. So, not only I just spent an hour waiting for the stuff to get deleted, but it won’t be deleted afterall.
That made me angry. So, I decided that I should restart MySQL service. I thought that killing MySQL process will make it forget about the whole rollback thing. It didn’t work.
In fact, it made the situation even worth. MySQL service refused to start until the rollback would finish. Or maybe it was repairing table, I am not sure. But it seemed more like a rollback. So for the next thirty minutes, it was fixing stuff, while none of other projects could connect to the database. Other projects were mostly websites that I host on my home server. Very nice.
Of course, while it was working, I was searching for the solution on the Web, mostly in MySQL manuals and forums. I came across a couple of other requests that were exactly like mine, but there were no answers. I then went to #mysql channel on IRC, where the guys told me that they feel sorry for me (how kind), but there was no solution, except to wait. Dang!
After the service came up, I simply removed the table (DROP TABLE mytable) and re-created it empty (CREATE TABLE mytable (...)).  I also found another way of emptying the table, which is TRUNCATE TABLE mytable, although it would behave exactly the same as DELETE FROM TABLE mytable on my version of MySQL (4.something).
If transaction is enabled as a general rule for all kind off databases:
delete from where ...works almost the same way like you are creating a new table with all records from the deleted set because the database writes the previous state off the record(with all the data inside it) into transaction log(what is most probably also some kind of the table).
So with delete you created extra 8Gb in the transaction log.
And in addition if you have referancial integrity constraints for a table database also check if it’s allowed to delete record at all. So before actual delete it checks using internal selects all referenced tables. All these done record by record.
If you use TRANCATE TABLE you are avoiding all this checks and writes to a transaction log.
Rinat,
Thanks for the info. The truth is out there and it looks like you almost got it right. One exception is for MySQL. Here is a quote from the manual: