CODEWITHSUNDEEP
mysql
Bhavya Agarwal
Bhavya Agarwal

Reputation: 107

Stopping MySQL delete query before completion

I was trying to delete some rows from my MySQL table with 200 Million rows. While it works fine in the beginning, it slows down drastically after a few hours. I was hoping I can stop the query for now and restart it and hence make it happen faster. Will my database remain intact after that?

Upvotes: 1

Views: 10070

Answers (4)

user5321247
user5321247

Reputation: 1

By default, MySQL workbench is started in safe mode, and can’t update or delete, without aWHERE condition, see the error message.

To fix it, in menu, selects Edit -> Preferences -> SQL Queries, uncheck the Safe Updates check-box.

Upvotes: 0

peterm
peterm

Reputation: 92795

You can terminate your current delete operation by terminating the session in which it was started. For that you can use already mentioned KILL statement issued from other session, or terminate client process if your delete was issued from some client code (e.g. php).

It's not very clear what you mean by intact. If you mean integrity of your table then yes it should be intact. If you mean will it be rolled back then it depends on context in which delete was issued.

To delete the remaining rows after abruption in batches and assuming that you DELETE from one table (meaning you're not using multi-table syntax) you can try to use LIMIT clause.

DELETE 
  FROM table_name
 WHERE ...
 LIMIT 10000 -- or any other appropriate batch size

This way you'll be able to delete n (10000) rows a time.

Upvotes: 4

mamdouh alramadan
mamdouh alramadan

Reputation: 8528

you can use Mysql Command line Client like this

mysql> show processlist;
+----+--------+-----------------+--------+---------+------+-------+-------------
-----+
| Id | User   | Host            | db     | Command | Time | State | Info
     |
+----+--------+-----------------+--------+---------+------+-------+-------------
-----+
|  2 | adroit | localhost:49857 | dbname | Sleep   |  339 |       | NULL
     |
|  3 | adroit | localhost:49858 | dbname | Sleep   |  339 |       | NULL
     |
|  4 | root   | localhost:50175 | NULL   | Query   |    0 | init  | show process
list |
+----+--------+-----------------+--------+---------+------+-------+-------------
-----+
3 rows in set (0.00 sec)

mysql> kill 2;
Query OK, 0 rows affected (0.00 sec)

what is happening here is simply showing the process list of all processes running by mysql using show processlist command. then simply preform killcommand on the process Id as shown above.

Upvotes: 0

Filipe Silva
Filipe Silva

Reputation: 21657

See KILL chapter on the docs.

KILL [CONNECTION | QUERY] thread_id

Each connection to mysqld runs in a separate thread. You can see which threads are running with the SHOW PROCESSLIST statement and kill a thread with the KILL thread_id statement.

During UPDATE or DELETE operations, the kill flag is checked after each block read and after each updated or deleted row. If the kill flag is set, the statement is aborted. Note that if you are not using transactions, the changes are not rolled back.

Upvotes: 2

Related Questions