PHP asynchronous mysql-query

Question

My app first queries 2 large sets of data, then does some work on the first set of data, and "uses" it on the second.

If possible I'd like it to instead only query the first set synchronously and the second asynchronously, do the work on the first set and then wait for the query of the second set to finish if it hasn't already and finally use the first set of data on it.

Is this possible somehow?

Answer 1

It's possible.

$mysqli->query($long_running_sql, MYSQLI_ASYNC);

echo 'run other stuff';

$result = $mysqli->reap_async_query(); //gives result (and blocks script if query is not done)
$resultArray = $result->fetch_assoc();

Or you can use mysqli_poll if you don't want to have a blocking call

http://php.net/manual/en/mysqli.poll.php

Answer 2

MySQL requires that, inside one connection, a query is completely handled before the next query is launched. That includes the fetching of all results.

It is possible, however, to:

• fetch results one by one instead of all at once
• launch multiple queries by creating multiple connections

By default, PHP will wait until all results are available and then internally (in the mysql driver) fetch all results at once. This is true even when using for example PDOStatement::fetch() to import them in your code one row at a time. When using PDO, this can be prevented with setting attribute \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to false. This is useful for:

• speeding up the handling of query results: your code can start processing as soon as one row is found instead of only after every row is found.
• working with result sets that are potentially larger than the memory available to PHP (PHP's self-imposed memory_limit or RAM memory).

Be aware that often the speed is limited by a storage system with characteristics that mean that the total processing time for two queries is larger when running them at the same time than when running them one by one.

An example (which can be done completely in MySQL, but for showing the concept...):

$dbConnectionOne = new \PDO('mysql:hostname=localhost;dbname=test', 'user', 'pass');
$dbConnectionOne->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

$dbConnectionTwo = new \PDO('mysql:hostname=localhost;dbname=test', 'user', 'pass');
$dbConnectionTwo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$dbConnectionTwo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$synchStmt = $dbConnectionOne->prepare('SELECT id, name, factor FROM measurementConfiguration');
$synchStmt->execute();

$asynchStmt = $dbConnectionTwo->prepare('SELECT measurementConfiguration_id, timestamp, value FROM hugeMeasurementsTable');
$asynchStmt->execute();

$measurementConfiguration = array();
foreach ($synchStmt->fetchAll() as $synchStmtRow) {
    $measurementConfiguration[$synchStmtRow['id']] = array(
        'name' => $synchStmtRow['name'],
        'factor' => $synchStmtRow['factor']
    );
}

while (($asynchStmtRow = $asynchStmt->fetch()) !== false) {
    $currentMeasurementConfiguration = $measurementConfiguration[$asynchStmtRow['measurementConfiguration_id']];
    echo 'Measurement of sensor ' . $currentMeasurementConfiguration['name'] . ' at ' . $asynchStmtRow['timestamp'] . ' was ' . ($asynchStmtRow['value'] * $currentMeasurementConfiguration['factor']) . PHP_EOL;
}