How to limit the sql execution time

Question

Some sql is not well written. Sometimes a search costs hours in applications. When a application(maybe a website) submit a query which run long time, I have to restart the mysql. How can I limit a sql query's execution time in the database side?

Answer 1

To auto kill a query in MySQL after a long execution time:

1. Create a stored procedure as:

   DECLARE CURSOR cur1 FOR SELECT ID 
                       FROM INFORMATION_SCHEMA.PROCESSLIST 
                       WHERE COMMAND = 'Query' AND TIME > 120;
   

   then inside curosr's loop do:

   FETCH ID INTO @var_kill_id;
   KILL QUERY @var_kill_id;
   

2. Create EVENT FOR EVERY 5 SECONDS and just CALL the above procedure inside it.

Note: KILL QUERY just kills the query and MySQL connection is not broken. see here.

Answer 2

Also if possible you can try Twitter's mysql fork that suport "max_statement_time" and kills a query exceding it, at a milisecond granularity.

See http://engineering.twitter.com/2012/04/mysql-at-twitter.html and https://github.com/twitter/mysql/wiki/Statement-Timeout

Original source.