Use timeouts to avoid long-running SELECT queries from taking down your Database

<<Back to MySQL & MariaDB Main Page

You may be wondering if users can slow down or even DOS a MySQL or MariaDB database with an ugly request. Then you are right. Yes it can happen.

If you are running MySQL 5.7 or above you can fix this problem by setting max_execution_time to a value within which you expect your query to finish. Of course if the query takes longer than the configured max_execution_time it will be aborted.

You can configure timeout at session level and at global level.
The timeouts only apply to read-only SELECT queries.
The value is specified in milliseconds

If your query takes to long, it fails with the following error:

To be clear, the execution time limit specified is really a “soft hint”, because the query interruption may not happen precisely at the specified execution time limit. There can be a minor delay between the timer expiration and the actual query interruption.
Finally, the maximum execution time can also be set for a specific SELECT statement using the MAX_EXECUTION_TIME hint directly in the query.
For Example

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ...