Implementing Asynchronous MySQL Queries in PHP Using Coroutines

Recently I studied a talk by Zhao Haiping that described how Facebook made MySQL queries asynchronous to improve performance, originally requiring PHP hacks, but modern PHP 5.5 can achieve the same without hacks.

For most web applications the database is the main bottleneck; a single query can dominate request latency. Making MySQL queries asynchronous allows multiple statements to run in parallel, dramatically reducing total query time.

Why is asynchronous faster than synchronous?

In a synchronous flow, two SELECT SLEEP(1) statements are executed sequentially, each taking about 1000 ms on the server, resulting in roughly 2000 ms total.

In an asynchronous flow, the second query is sent while the first is still waiting for a result, so both queries are processed concurrently and the overall time drops to just over 1000 ms.

How to implement asynchronous MySQL queries?

The key is to separate sending the request from receiving the result. The mysqlnd extension provides mysqlnd_async_query to send a query and mysqlnd_reap_async_query to fetch the result. The mysqli wrapper exposes this via the MYSQLI_ASYNC flag.

Below is a full example that uses PHP generators (coroutines) to issue two asynchronous queries and fetch their results without blocking.

<?php
function f1(){
    $db = new db();
    $obj = $db->async_query('select sleep(1)');
    echo "f1 async_query 
";
    yield $obj;
    $row = $db->fetch();
    echo "f1 fetch
";
    yield $row;
}

function f2(){
    $db = new db();
    $obj = $db->async_query('select sleep(1)');
    echo "f2 async_query
";
    yield $obj;
    $row = $db->fetch();
    echo "f2 fetch
";
    yield $row;
}

$gen1 = f1();
$gen2 = f2();

$gen1->current();
$gen2->current();
$gen1->next();
$gen2->next();

$ret1 = $gen1->current();
$ret2 = $gen2->current();

var_dump($ret1);
var_dump($ret2);

class db{
    static $links;
    private $obj;
    function getConn(){
        $host = '127.0.0.1';
        $user = 'demo';
        $password = 'demo';
        $database = 'demo';
        $this->obj = new mysqli($host,$user,$password,$database);
        self::$links[spl_object_hash($this->obj)] = $this->obj;
        return self::$links[spl_object_hash($this->obj)];
    }
    function async_query($sql){
        $link = $this->getConn();
        $link->query($sql, MYSQLI_ASYNC);
        return $link;
    }
    function fetch(){
        for($i=1;$i<=5;$i++){
            $read = $errors = $reject = self::$links;
            $re = mysqli_poll($read,$errors,$reject,1);
            foreach($read as $obj){
                if($this->obj === $obj){
                    $sql_result = $obj->reap_async_query();
                    $sql_result_array = $sql_result->fetch_array(MYSQLI_ASSOC);
                    $sql_result->free();
                    return $sql_result_array;
                }
            }
        }
    }
}
?>

Running the script yields output similar to:

$time php ./async.php
f1 async_query
f2 async_query
f1 fetch
f2 fetch
array(1) { ["sleep(1)"]=> string(1) "0" }
array(1) { ["sleep(1)"]=> string(1) "0" }
real    0m1.016s
user    0m0.007s

The total real time is about 1.0 s, confirming that the two queries ran in parallel instead of the 2 s required by a purely synchronous approach. Note that this is a demonstration; production code would need additional error handling and resource management.

Important Considerations

If the MySQL server is already heavily loaded, spawning many parallel connections can increase contention because each connection creates a separate server thread. Excessive threads may degrade overall system performance, so asynchronous execution should be used judiciously.