PHP PDO Performance Query Comparisons

This last week I've taken an interest in PDO query performance, so naturally I thought I would share what I learned an discovered.

We'll be taking a look at two approaches, one using a prepared statement and another using query. For the sake of shareability all code samples will be self contained so you can copy/paste and try it yourself. The examples below use the sample database provided by MySQL that can be found here https://github.com/datacharmer/test_db.

Each example will iterate a little over 2.8 million records and echoing out each records details.

NOTE: Your results may vary depending on your hardware, dataset, and other variables.

Prepared Statement

This example will leverage: PDO::prepare, PDOStatement::execute, and PDOStatement::fetchAll.

<?php

function convert($size)
{
    $unit=array('b','kb','mb','gb','tb','pb');
    return @round($size/pow(1024,($i=floor(log($size,1024)))),2).' '.$unit[$i];
}

file_put_contents(__DIR__.'/prepared-memory-stats.txt', convert(memory_get_usage()) . ' | ' . microtime(true) . PHP_EOL, FILE_APPEND);

$dsn = 'mysql:dbname=employees;host=127.0.0.1';
$user = 'root';
$password = '';

try {
    $dbh = new PDO($dsn, $user, $password);
    $dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
    $stmt = $dbh->prepare('SELECT * FROM salaries');
    $stmt->execute();

    foreach ($stmt->fetchAll() as $row) {
        echo 'SALARY INFO: ' . implode(' | ', $row) . PHP_EOL;
    }
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
} catch (Exception $e) {
    echo 'General exception: ' . $e->getMessage();
}

file_put_contents(__DIR__.'/prepared-memory-stats.txt', convert(memory_get_usage()) . ' | ' . microtime(true) . PHP_EOL, FILE_APPEND);

The results found in prepared-memory-stats.txt from my machine are as follows:

396.25 kb | 1576850010.0514
457.65 kb | 1576850047.9115

Meaning that the used memory is roughly 61.4 kb and it took 37.8601 milliseconds.

Query

This example will leverage: PDO::query

<?php

function convert($size)
{
    $unit=array('b','kb','mb','gb','tb','pb');
    return @round($size/pow(1024,($i=floor(log($size,1024)))),2).' '.$unit[$i];
}

file_put_contents(__DIR__.'/query-memory-stats.txt', convert(memory_get_usage()) . ' | ' . microtime(true) . PHP_EOL, FILE_APPEND);

$dsn = 'mysql:dbname=employees;host=127.0.0.1';
$user = 'root';
$password = '';

try {
    $dbh = new PDO($dsn, $user, $password);
    $dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

    foreach ($dbh->query('SELECT * FROM salaries') as $row) {
        echo 'SALARY INFO: ' . implode(' | ', $row) . PHP_EOL;
    }
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
} catch (Exception $e) {
    echo 'General exception: ' . $e->getMessage();
}

file_put_contents(__DIR__.'/query-memory-stats.txt', convert(memory_get_usage()) . ' | ' . microtime(true) . PHP_EOL, FILE_APPEND);

The results found in prepared-memory-stats.txt from my machine are as follows:

395.18 kb | 1576849890.9595
439.13 kb | 1576849925.8591

Meaning that the used memory is roughly 43.96 kb and it took 34.8996 milliseconds.

Conclusion

Though my example is fairly simplistic it does show that leveraging PDO::query is more efficient by 17.44 kb and it took 2.9605 milliseconds less time to complete. That doesn't seem like much but the larger your dataset (2.8 million records is fairly small for a business applications) the greater the benefit. This is also isn't loading any additional classes or frameworks.

Hope you enjoyed this quick article!