How to Limit Query Execution Time in MySQL?

Limiting Query Execution Time in MySQL

Question: Is there a way to cap the maximum duration of a query in MySQL?

Expert Answer:

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

Original Answer:

Prior to MySQL 5.7, enforcing a server-wide timeout for queries and non-SELECT operations was not possible. However, a common workaround is to create a cron job that runs continuously on your database server and performs the following steps:

1. Execute a SHOW PROCESSLIST query to retrieve a list of running processes.
2. Identify any connections where the query execution time exceeds your desired time limit.
3. Issue a KILL [process id] command for each such process to terminate it.

This technique allows you to enforce a time limit for queries on your server, ensuring that long-running queries do not monopolize resources indefinitely.

The above is the detailed content of How to Limit Query Execution Time in MySQL?. For more information, please follow other related articles on the PHP Chinese website!