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