Reduce page load time using asynchronous database queries

Traditionally in MySQL, we execute queries sequentially i.e. queries will be executed one after other only. So if we are executing multiple queries then each query will be blocked until and unless the previous query is executed. This works to a limit, but as the system expands, this method ends up having a significant impact on overall system performance and latency.

Suppose you are developing a web application and you are in a situation to fetch a large amount of data from database before page load. In such situations your webpage is going to take a long time to load. For example if you are firing 4 database queries to fetch the required data, and each query is taking around 2 seconds (Oh yeah! a big query) your total query execution itself is going to cost you around 8 seconds if you execute those queries sequentially.

An ideal solution for this problem is to use asynchronous database queries to speed up the webpage.

Using asynchronous database queries you will be able to execute these 4 queries mentioned in the above example in 4 parallel threads. An example on how to do it using MySQLi and PHP is mentioned below.

<?php

$query[] = “YOUR FIRST QUERY”;
$query[] = “YOUR SECOND QUERY”;
$query[] = “YOUR THIRD QUERY”;
$query[] = “YOUR FOURTH QUERY”;

$linksArr = array();
$queryCount = count($query);
for ($i = 0; $i < $queryCount; $i++) {
$link = mysqli_connect(“dbHost”, “dbUser”, “dbPassword”, “dbName”);
$link->query($query[$i], MYSQLI_ASYNC);
$linksArr[] = $link;
}

$resultArr = array();
$processed = 0;
do {
$links = $errors = $reject = array();
foreach ($linksArr as $link) {
$links[] = $errors[] = $reject[] = $link;
}
if (!mysqli_poll($links, $errors, $reject, 1)) {
continue;
}
foreach ($links as $link) {
if ($result = $link->reap_async_query()) {
$resultArr[] = $result->fetch_all(MYSQLI_ASSOC);
if (is_object($result))
mysqli_free_result($result);
} else
die(sprintf(“MySQLi Error: %s”, mysqli_error($link)));
$processed++;
}
} while ($processed < count($linksArr));

foreach ($linksArr as $link) {
mysqli_close($link);
}

print_r($resultArr);

Above code will give you a full result set array which will have results of 4 queries you just executed. Each query will be starting in each db thread without waiting for the previous query result, thus saving total execution time. Using this method we can significantly reduce the query execution time to below 4 seconds instead of initial 8 seconds. Every solution will have a minor flaw and the inconvenience that we will be facing here is that we need to open 4 connections instead of one because of the lack of connection pooling in PHP.

NB : Some functions used above will only work with `mysqlnd` (mysql native driver) support.