PHP PDO execute to abort when MySQL query taking more than X seconds?

Question

Is there a feature in PHP PDO or in MySQL that automatically aborts and returns with an empty result set when it's executing more than, say, 2 seconds?

Did some searches and nothing helpful was found. I think this would probably be great for user experience, especially with trivial stats data that are not so important.

Tried to come up with a way in PHP to do this but not sure how to monitor the execution time of PDO:execute() when it's not returned yet.

Any idea?

Answer 1

You can do this my setting PDO::ATTR_TIMEOUT, like this:

$db = new PDO(
    "mysql:host=$host;dbname=$dbname", 
    $username, 
    $password,
    array(
      PDO::ATTR_TIMEOUT => 10
    )
  );

This is cause a timeout after 10 seconds.

Answer 2

There is a global variable named MAX_EXECUTION_TIME in MySQL. You can set:

SET GLOBAL MAX_EXECUTION_TIME = 1200;

Or you can use PDO library's ATTR_TIMEOUT option, which the unit is seconds (Manual here).

PDO::ATTR_TIMEOUT: Specifies the timeout duration in seconds. Not all drivers support this option, and its meaning may differ from driver to driver. For example, sqlite will wait for up to this time value before giving up on obtaining an writable lock, but other drivers may interpret this as a connect or a read timeout interval. Requires int.