Mysql – How to limit the sql execution time

MySQLsql

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?

Best Answer

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.