How to Enforce Execution Time Limits for MySQL Queries?

Enforcing Execution Time Limits for MySQL Queries

When executing MySQL queries, you may encounter scenarios where excessive execution times become undesirable. To mitigate this, MySQL introduced a mechanism to set maximum execution limits for certain queries.

How to Set Execution Time Limits

Starting with MySQL 5.7.4, you can specify execution time limits in milliseconds using the MAX_EXECUTION_TIME hint:

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM table;

where 1000 represents the maximum time in milliseconds. However, this hint only works for read-only SELECT statements.

Renamed Variable in MySQL 5.7.8

In MySQL 5.7.8, the hint was renamed to max_execution_time. Accordingly, the modified query would become:

SELECT /*+ max_execution_time(1000) */ * FROM table;

Limitations

It's important to note that this execution time limit only applies to the outermost SELECT statement. If the query contains nested or subqueries, the limit may not be inherited. Additionally, this functionality is not available for other operations like INSERT, UPDATE, or DELETE statements.

Conclusion

By utilizing the MAX_EXECUTION_TIME hint, you can control the maximum time MySQL spends executing certain read-only SELECT queries. This helps prevent runaway queries from monopolizing server resources and ensures better performance in scenarios where execution time is critical.

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