How to Set Query Timeout in MySQL

MySQL is a powerful database querying language that allows you to perform simple to complex operations on your data. Often, complex queries take a long time to run and can block your other queries from running quickly, by blocking system resources. So it is advisable to set a query timeout value so that long running queries are automatically terminated after a specific amount of time. In this article, we will learn how to set query timeout in MySQL.

How to Set Query Timeout in MySQL

Starting MySQL 5.7, you can easily set a timeout value for your MySQL queries. There are two ways to do this.

1. Within SQL query

You can include the query timeout value within your SQL query itself. Let us say you have the following SQL query.

mysql> SELECT status, count(*) FROM articles GROUP BY status ORDER BY status;

You can add the query timeout hint /*+ MAX_EXECUTION_TIME(1000) */ as shown below. You need to specify the timeout value in milliseconds. Here is an example to set timeout value of 1000 milliseconds.

mysql> SELECT /*+ MAX_EXECUTION_TIME(1000) */ status, count(*) FROM articles GROUP BY status ORDER BY status;

2. Session Wide or Global Timeout

The above method sets timeout values for the said query. If you want to set session wide or global timeout, then you need to use the following SQL queries. Please note, the timeout values are in milliseconds. The first query below sets session-wide query timeout while the second query sets the global timeout.

SET SESSION MAX_EXECUTION_TIME=2000; 

SET GLOBAL MAX_EXECUTION_TIME=2000;

In all the above cases, once the query timeout value is exceeded, you will see the following error message.

ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

In this article, we have learnt several ways to set query timeout in MySQL. The first approach sets query timeout for the specific query. The next method sets query timeout for all queries within a given SQL session. The last query sets the timeout for all queries in all sessions. You can use any of these methods as per your requirement. Please remember to specify the timeout value in milliseconds. It is a good practice to set query timeouts so that malicious or long running queries don’t unnecessarily block your system resources and slow down other queries in your system.

How to Set Query Timeout in MySQL