Jan 022010
How to check and kill long running MySQL query
Answer:
1. Get into the MySQL shell, type 'SHOW PROCESSLIST' to see which query is blocking
mysql> SHOW PROCESSLIST;
2. It will print out all the current executing queries, and you will find the thread Id.
+-----+-------------+--------------------+-------+---------+-------+
| Id | User | Host | db | Command | Time| State | Info
+-----+-------------+--------------------+-------+---------+-------+-
| 3928 |root |192.168.1.5:59202 |db1 |Query | 84 | Locked |
select * from table1 where name like '%test%'
3. Remember this Id, to kill it, use the "KILL" command
mysql> KILL 3928;
Query OK, 0 rows affected (0.05 sec)
