Saturday, 21 July 2012

MySQL or MySQLi or PDO

Lessons learned:
  • MySQLi is 3-4 times slower than MySQL when fetching less then 500 datasets
  • MySQLi is 2-4 times faster than MySQL when fetching more than 500 datasets
  • PDO is 2-5 times slower than MySQL/MySQLi
  • Unbuffered queries are 15-40 percent faster than buffered queries in MySQLi
  • Unbuffered queries are 10-25 percent faster than buffered queries in MySQL for less than 10000 datasets
  • Unbuffered queries are 3-7 percent slower than buffered queries in MySQL for more than 10000 datasets
  • Unbuffered queries are 0-5 percent faster than buffered queries in PDO
  • Non thread safe versions of PHP on win32 are 50 percent faster than thread safe versions

Here is the test script:

$table = 'test1.test2';
benchmark($table, 100);
benchmark($table, 500);
benchmark($table, 1000);
benchmark($table, 5000);
benchmark($table, 10000);
benchmark($table, 50000);
benchmark($table, 100000);

function benchmark($table, $size) {
mysql_connect('127.0.0.1', 'root', '');
mysql_query('drop table if exists '.$table);
mysql_query("CREATE TABLE $table (id int(11) AUTO_INCREMENT,
str1 varchar(255), str2 varchar(255), PRIMARY KEY (id)) ENGINE=INNODB");
mysql_query("begin");
for ($i=0; $i<$size; $i++) {
mysql_query("insert into $table values(null, 'hello$i', 'world$i')");
}
mysql_query("commit");
// warm up mysql cache
$db = new PDO('mysql:host=127.0.0.1', 'root', '');
foreach ($db->query('select * from '.$table) as $vals) $test = $vals;


$start = microtime(true);
mysql_connect('127.0.0.1', 'root', '');
$result = mysql_query('select * from '.$table);
while ($row = mysql_fetch_assoc($result)) $test = $row;
echo $size.' mysql-buffered '.number_format(microtime(true)-$start, 5)."\n";

$start = microtime(true);
mysql_connect('127.0.0.1', 'root', '');
$result = mysql_unbuffered_query('select * from '.$table);
while ($row = mysql_fetch_assoc($result)) $test = $row;
echo $size.' mysql-unbuffered '.number_format(microtime(true)-$start, 5)."\n";

$start = microtime(true);
$db = mysqli_connect('127.0.0.1', 'root', '');
foreach (mysqli_query($db, 'select * from '.$table) as $row) $test = $row;
echo $size.' mysqli-buffered '.number_format(microtime(true)-$start, 5)."\n";

$start = microtime(true);
$db = mysqli_connect('127.0.0.1', 'root', '');
foreach (mysqli_query($db, 'select * from '.$table, MYSQLI_USE_RESULT)
as $row) $test = $row;
echo $size.' mysqli-unbuffered '.number_format(microtime(true)-$start, 5)."\n";

$start = microtime(true);
$db = new PDO('mysql:host=127.0.0.1', 'root', '');
foreach ($db->query('select * from '.$table) as $vals) $test = $vals;
echo $size.' pdo-buffered '.number_format(microtime(true)-$start, 5)."\n";

$start = microtime(true);
$db = new PDO('mysql:host=127.0.0.1', 'root', '',
array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false));
foreach ($db->query('select * from '.$table) as $vals) $test = $vals;
echo $size.' pdo-unbuffered '.number_format(microtime(true)-$start, 5)."\n";
}
Tests were made with PHP 5.3.10, MySQL 5.5.29, Kernel 3.2.0, 64bit, 3.4GHz (QEMU), values in seconds:

100 mysql-buffered 0.00011
100 mysql-unbuffered 0.00015
100 mysqli-buffered 0.00041
100 mysqli-unbuffered 0.00032
100 pdo-buffered 0.00043
100 pdo-unbuffered 0.00041
500 mysql-buffered 0.00034
500 mysql-unbuffered 0.00045
500 mysqli-buffered 0.00033
500 mysqli-unbuffered 0.00024
500 pdo-buffered 0.00058
500 pdo-unbuffered 0.00058
1000 mysql-buffered 0.00059
1000 mysql-unbuffered 0.00064
1000 mysqli-buffered 0.00037
1000 mysqli-unbuffered 0.00030
1000 pdo-buffered 0.00090
1000 pdo-unbuffered 0.00096
5000 mysql-buffered 0.00288
5000 mysql-unbuffered 0.00290
5000 mysqli-buffered 0.00077
5000 mysqli-unbuffered 0.00054
5000 pdo-buffered 0.00340
5000 pdo-unbuffered 0.00341
10000 mysql-buffered 0.00564
10000 mysql-unbuffered 0.00580
10000 mysqli-buffered 0.00123
10000 mysqli-unbuffered 0.00079
10000 pdo-buffered 0.00665
10000 pdo-unbuffered 0.00656
50000 mysql-buffered 0.04469
50000 mysql-unbuffered 0.04609
50000 mysqli-buffered 0.01915
50000 mysqli-unbuffered 0.01735
50000 pdo-buffered 0.04679
50000 pdo-unbuffered 0.04587
100000 mysql-buffered 0.08471
100000 mysql-unbuffered 0.09054
100000 mysqli-buffered 0.03967
100000 mysqli-unbuffered 0.03343
100000 pdo-buffered 0.09257
100000 pdo-unbuffered 0.09148

No comments:

Post a Comment