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?

score 24 · Answer 1 · answered Dec 02 '14 at 01:50

24

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

answered Dec 02 '14 at 01:50

Thorsten

3,102
14
14

Looks easy. But is there a way of doing it with PDO rather than mysqli? – Clox Dec 02 '14 at 01:59
5

@Clox I'd advise you to include PDO in your question or title if it's a requirement. – EternalHour Dec 02 '14 at 02:13
@Clox: I'm not aware of a similar PDO method, but you can always use async curl requests: https://github.com/barbushin/multirequest -> create two php-scripts, each performs one query and then call these scripts async with curl. I'm not saying this is the most elegant solution but it works. – Thorsten Dec 02 '14 at 02:18
6

@Clox the short and direct answer is that PDO doesn't support non-blocking, asynchronous queries as per http://php.net/manual/ru/mysqlinfo.api.choosing.php. – EdNdee May 23 '15 at 07:21
Per the answer below, is it not possible to use this to parallelize querying over the same connection? – tslater Mar 08 '17 at 23:27
what if it is just insert and doesn't care about result ? – neobie Jul 05 '21 at 12:58

Tomas Creemers · Answer 2 · 2014-12-02T03:01:14.143

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

Excellent, well explained answer! Just learnt something new :) — Darren, Dec 02 '14 at 03:47
I think this answer is messy considering that PDO doesn't actually support native asynchronous operations. You should work with MYSQLI instead. — EdNdee, May 23 '15 at 07:26

PHP asynchronous mysql-query

2 Answers2

Linked