PHP asynchronous mysql query

Question

My application first requests 2 large data sets, then does some work on the first data set and “uses” it on the second.

If possible, I would like it instead to only execute the first set synchronously, and the second asynchronously, to work on the first set, and then wait for the second set to complete, if it was not already, and finally use the first data set on it.

Is it possible somehow?

Answer 1

It is 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 the query be fully processed within one connection before the next query is run . This includes selecting all results .

It is possible, however, that:

• select results one at a time instead of all at once
• running multiple queries by creating multiple connections

By default, PHP will wait until all the results are available, and then internally (in the mysql driver) they will immediately select all the results. This is true even if you use, for example, PDOStatement::fetch() to import them into your code one line at a time. When using PDO, this can be prevented by setting the \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to false . This is useful for:

• speeding up the processing of query results: your code can start processing as soon as one line is found , and not only after each line is found.
• works with result sets that are potentially larger than the memory available for PHP (built-in PHP memory_limit or RAM).

Remember that often the speed is limited by the storage system with characteristics that mean that the total processing time for two requests is longer when they are launched simultaneously than when they are started one by one.

An example (which can be done completely in MySQL, but to show 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; }