What will happen if I kill a huge MySQL InnoDb DELETE Query?

Question

I'm currently running a DELETE query that is taking a lot longer than expected (already 10hrs!). I would like to kill it through phpmyadmin processes, however am concerned about what might happen. Will the roll-back he automatically does take a lot of time also? Current query status shows "updating".

Answer 1

It depends on the stage your query is in right now. But generally rollback takes about equal time, sometimes even more than the original operation.

As per point 2 of this document, it's not really advisable.

Also, be sure to verify your MySQL version as it has a VERY nasty bug with delete/update queries rollback in some versions as per this article

Restarting/killing the MySQL process won't help as rollback will resume upon restart.

Answer 2

Thumb rule is:

Just let it rollback by its own and don't even think about restarting the DB as it will resume after restart but worse is your DB won't be accessible meanwhile.

Yes rollback of huge data (i.e. millions of rows) will be considerably slower than its commit operation and even more slow if parallel InnoDB commits are happening in same database.