2

I've been trying to set limit of execution in the select, but using the MAX_EXECUTION_TIME the query break and doesn't return the results. I need something like this code below. This is like the limit but with time execution. I need the query to stop running but return the result which was obtained.

SELECT * FROM table WHERE name LIKE '%jean%'LIMIT 1 sec.

10 rows in 1 sec.

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
Jean Carlo Codogno
  • 78
  • 1
  • 10
  • 1
    Possible duplicate of [How to set a maximum execution time for a mysql query?](https://stackoverflow.com/questions/415905/how-to-set-a-maximum-execution-time-for-a-mysql-query) – Lucas Jun 25 '19 at 14:48
  • 1
    My question is different because I need the result when timeout. – Jean Carlo Codogno Jun 25 '19 at 15:07
  • Well, unfortunately you'll never get results after a timeout, by design. However, you make sure your queries don't hang forever which is always good. – Lucas Jun 25 '19 at 18:07

2 Answers2

1

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

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

fie
  • 407
  • 3
  • 10
-1

There are two way,

1.Use MAX_EXECUTION_TIME hint in SQL as below.

mysql> select /*+ MAX_EXECUTION_TIME(1000) */ count(*) from tab1 a,tab1 b where a.cust_id = 3839203;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

In the above SQL, there are no joins between both of table and having a huge amount of data. Hence it will take lots of time. But I have specified here to stop the query after running of 1000 ms or 1 sec.

  1. You can set a session parameter that accepts in milliseconds before running to this query. And once you want to reset the value you can specify the different values for it.

Before running your SQL

SET SESSION MAX_EXECUTION_TIME=1000;

Post running your SQL

SET SESSION MAX_EXECUTION_TIME= <<some different value or maximum value supported>>;
Vivek
  • 783
  • 5
  • 11
  • The MAX_EXECUTION_TIME is used to break the execution of the query, hence it won't gives any output if query didn't get processed within time. I don't think that there is any parameter like that which can give the processed output till execution terminates – Vivek Jun 25 '19 at 16:55