Archive

Posts Tagged ‘mysql queries’

Killing MySQL queries

May 10, 2011 Leave a comment

Problem: Opps query on a table is taking too long. Due to
a) Lack of index
b) Forgot to put a limit.
The table in question has over 20Million rows.

Solution:
1. Connect to mysql as your given users eg guest
mysql> show process list;
+———–+———+——————+——–+———+——-+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+———–+———+——————+——–+———+——-+——-+——————+
| 120 | guest | localhost:57031 | database1 | Query | 0 | NULL | show processlist |
| 156 | guest | localhost:52753 |database1 | Query | 40 | Copying to tmp table | SELECT * FROM table1 INNER JOI |
| 192 | guest | localhost:54919 | database1 | Sleep | 21491 | | NULL |
+———–+———+——————+——–+———+——-+——-+——————+

2. Identify the query you would like to stop.
mysql>kill 156;

3. Check to see that the process has gone away.
mysql> show processlist;
+———–+———+——————+——–+———+——-+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+———–+———+——————+——–+———+——-+——-+——————+
| 120 | guest | localhost:57031 | database1 | Query | 0 | NULL | show processlist |
| 192 | guest | localhost:54919 | database1 | Sleep | 21491 | | NULL |
+———–+———+——————+——–+———+——-+——-+——————+

The documentation for the KILL command is here http://dev.mysql.com/doc/refman/5.0/en/kill.html.

Advertisements