How to Set a Maximum Execution Time For a MySQL Query

How set limit of execution time in select, but return the result in the end

If you are using PHP try unbuffered queries and manage the timeout in PHP.

https://www.php.net/manual/en/mysqlinfo.concepts.buffering.php

Set maximum execution time in MYSQL / PHP

You can make it by setting set_time_limit in you php code (set to 0 for no limit)

set_time_limit(0);

Or modifying the value of max_execution_time directly in your php.ini

;;;;;;;;;;;;;;;;;;;
; Resource Limits ;
;;;;;;;;;;;;;;;;;;;

; Maximum execution time of each script, in seconds
; http://php.net/max-execution-time
; Note: This directive is hardcoded to 0 for the CLI SAPI
max_execution_time = 120  

Or changing it with ini_set()

ini_set('max_execution_time', 120); //120 seconds

but note that for this 3rd option :

max_execution_time

You can not change this setting with ini_set() when running in safe
mode. The only workaround is to turn off safe mode or by changing the
time limit in the php.ini.

Source www.php.net

MySQL - can I limit the maximum time allowed for a query to run?

Update

As of MySQL 5.7, you can include a MAX_EXECUTION_TIME optimizer hint in your SELECT queries to instruct the server to terminate it after the specified time.

As far as I know, if you want to enforce a server-wide timeout, or if you care about queries besides SELECTs, the original answer is still your only option.

Original answer

There is no way to specify a maximum run time when sending a query to the server to run.

However, it is not uncommon to have a cron job that runs every second on your database server, connecting and doing something like this:

1. SHOW PROCESSLIST
2. Find all connections with a query time larger than your maximum desired time
3. Run KILL [process id] for each of those processes

how to set mysql max_execution_time property use go

Execute SET max_execution_time=500 as a SQL statement. This limits the following SQL statements on the same connection to 0.5 seconds.

It is also possible as a SQL comment hint like SELECT /*+ MAX_EXECUTION_TIME(1000) */ field1, field2 FROM tbl ... like
this answer.
If you show details about your slow query in a new question performance improvements may be possible.

User-specific mysql timeout

You can define this using max_execution_time session variable (in milliseconds), something like:

SET SESSION max_execution_time=2000;

It should be done from a client for each session, so there is no way (currently) to assign this for a specific database user.

---

Related Topics