Introduction

Sometimes you may want to stop the execution of a slow query. To do this with regard to SELECT queries, you can use KILL and SHOW QUERIES commands.

KILL terminates the execution of a SELECT query by its id which you can find in SHOW QUERIES:

KILL <query id>

SHOW QUERIES returns information about all the queries running now. It outputs a table with the following structure:

• id: query id which can be used in KILL to terminate the query
• query: query statement or a part of it
• protocol: connection protocol, the possible values are sphinx , mysql , http , ssl , compressed and replication or combination (e.g. http,ssl or compressed,mysql )
• host: ip:port of the client

An example of usage

Let's run a slow SELECT query on it ( The query is intentionally unrealistic to make Manticore process it for a considerable period of time):

mysql -P9306 -h0 -e "SELECT * FROM t WHERE MATCH('a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|x|y|z|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|x|y|z|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|x|y|z');"

Now we'll run the query again, but this time we'll use SHOW QUERIES and KILL to stop it.

To start processing the query in the background, run the following command:

mysql -P9306 -h0 -e "SELECT * FROM t WHERE MATCH('a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|x|y|z|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|x|y|z|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|x|y|z');" &>> /dev/null &

Then run SHOW QUERIES to get the query's id:

mysql -P9306 -h0 -e "SHOW QUERIES"

Now let's retrieve the id from the output and run the KILL command with it:

query_id=$(mysql -P9306 -h0 -e "SHOW QUERIES" | grep 'SELECT' | awk '{print $2}') && echo $query_id

mysql -P9306 -h0 -e "KILL $query_id"

Lastly, let's run SHOW QUERIES again to make sure it's done:

mysql -P9306 -h0 -e "SHOW QUERIES"

As you see, the slow query has been successfully terminated.