I have a innodb table that counts circa 1 billion entries. I started a delete operation (with a small where condition on an indexed column) that should delete a lot entries, leaving circa 100 million. The delete operation runs for 2 days now and I got an error "Lost connetcion to mysql server during query" a few hours ago. The mysql processlist command shows that delete operation with status "Updating", so it seems like the query is still running though the command line has shown me this error. How can I trace that down? I basically don't know if it is still deleting or got stuck. I am afraid killing the process because I do not want to trigger a rollback.

  • Show us the query, the indexes defined on it and the execution plan.
    – user1822
    Commented Mar 31, 2014 at 18:02
  • Good point. Explain shows me that the query is done without using an index. Urgh. That explains why the query is so slow. The question is: Kill it or don't kill it. How can I decide? The query looks like "DELETE FROM table WHERE field='one' or field='two' or field='three'...
    – Starty
    Commented Mar 31, 2014 at 18:07
  • No, it's slow because it's trying to delete about 900M rows, and in a single transaction. That takes time. It has to delete the 900M rows and also update all the table's indexes. Commented Mar 31, 2014 at 18:09
  • 1
    And don't kill the query. Commented Mar 31, 2014 at 18:11
  • Yeah. The point is that the error "Lost connection to mysql server during query" upsets me, you know?
    – Starty
    Commented Mar 31, 2014 at 18:11

1 Answer 1


If it's showing in your processlist then it's still running.

In the future, for situations like this we're you're pruning 90%+ of the table consider this approach:

create table new_table like old_table;
insert into new_table (select * from old_table where <what you want to keep>)
rename table old_table to old_table_drop_prep, new_table to old_table;
drop table old_table_drop_prep;
  • This is indeed one of the best approaches.
    – ddaniel
    Commented Apr 1, 2014 at 5:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.