Learn how to stop long running SQL query in MySQL

Sometimes when you Run a SQL join query in phpMyAdmin, there will be a situation where the entire server will go down because of the long running SQL query.

To kill this long running SQL query, you need to access the MySQL server via command prompt using a client such as ‘Putty’ (for Unix Server).

In this example I’m not going to use a live server, so I’m taking XAMPP (localhost) in a windows machine. So, basically in a windows machine you can always access the Command Prompt by typing ‘cmd’ in Run dialog box.

So, lets begin:

1. Press Window Key + R to open the RUN dialog box and type ‘cmd’ to access the command prompt:

run-dialog

 

2. After the Command Prompt open, go to the hard drive where you have installed XAMPP (In my system I have installed in C: drive):

These are the following commands to access the MySQL server in XAMPP:

1. c:
2. c
3. cd xampp
4. cd mysql
5. cd bin
6. mysql -u root (where root is the username).

if username and password is changed then the command will be:

mysql -u yourusername -p yourpassword

see the screen shot:

command-prompt-mysql

You have successfully reached to the MySQL server via command prompt:

Lets see some of the MySQL commands:

1. show databases – this will give you the list of databases.

2. use test – where ‘use’ is the command and ‘test’ is the database name. ‘use’ is a command to access the particular database. 

3. then, usual SQL query you can just type and enter to see the results.

4. show processlist – Here is the important command. It will list out the number of running SQL queries.

 

5. Kill [ID]   –  where [ID] is the process no. This will kill the Long Running SQL query / Particular Process.
Lets see the below screen shot to learn HOW TO STOP THE LONG RUNNING SQL QUERY:

In the below screen, you can see there are 3 processes, Lets say the the third one (ID 20) is the query that is taking too much time (marked in big red mark), green mark shows the query is executing for 32 seconds. 32 seconds isn’t big, but lets say if it is 300 seconds (5 minutes).  So need to stop that query right? just write the Kill Command to stop the process.

Command: Kill 20;

command-prompt-mysql-01

Thats it.,

Add a Comment

Your email address will not be published. Required fields are marked *