Boost PHP Web Performance with MySQL Asynchronous Queries

Preface

Recently I studied Zhao Haiping's talk and learned that Facebook made MySQL queries asynchronous to improve performance. Because Facebook implemented this early, they had to hack PHP, but with PHP 5.5 the feature is available natively.

For a web site, the database is often the performance bottleneck; queries can dominate request latency. Making MySQL queries asynchronous allows multiple SQL statements to run simultaneously, dramatically shortening total query time.

Why is async faster than sync?

In synchronous mode a typical MySQL query blocks until the result returns. The example compares two SELECT SLEEP(1) statements, each taking about 1000 ms on the server.

Synchronous execution flow

1. Send first query (≈1 ms). 2. Server returns first result (≈1000 ms). 3. Send second query (≈1 ms). 4. Server returns second result (≈1000 ms). Total ≈2002 ms.

Asynchronous execution flow

Step 1: Send first query (≈1 ms).

Step 2: While waiting for the first result, send the second query (≈1 ms).

Step 3: Receive both results together (≈1000 ms).

Comparison analysis

Asynchronous queries are faster because multiple statements execute in parallel on the server, reducing overall response time. The benefit grows with longer‑running SQL statements.

How to implement MySQL asynchronous queries?

The key is to separate sending the request from receiving the result, which mysqlnd supports.

Relevant mysqlnd functions: mysqlnd_async_query – send query request. mysqlnd_reap_async_query – fetch query result.

mysqli wraps these; pass MYSQLI_ASYNC to query() to enable async.

Why use coroutines?

PHP 5.5 provides yield and generators, allowing us to write async code in a familiar function style. Example implementation:

<?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 shows both queries complete in about 1 s instead of 2 s.

Note

If the MySQL server is heavily loaded, parallel execution may worsen performance because each connection spawns a separate server thread, increasing system load.

References

Facebook Asynchronous MySQL – How Facebook Queries Databases.