CODEWITHSUNDEEP
phpmysqlasynchronous
Clox
Clox

Reputation: 1943

PHP asynchronous mysql-query

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?

Upvotes: 14

Views: 28690

Answers (2)

Tomas Creemers
Tomas Creemers

Reputation: 2715

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:

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;
}

Upvotes: 13

Thorsten
Thorsten

Reputation: 3122

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

Upvotes: 26

Related Questions