MySQL/MySQLi/PDO crap speed test

100 mysql-buffered 0.00051
100 mysql-unbuffered 0.00050
100 mysqli-prepared 0.00150
100 mysqli-buffered 0.00191
100 mysqli-unbuffered 0.00187
100 pdo-buffered 0.00169
100 pdo-unbuffered 0.00170
500 mysql-buffered 0.00133
500 mysql-unbuffered 0.00172
500 mysqli-prepared 0.00132
500 mysqli-buffered 0.00271
500 mysqli-unbuffered 0.00281
500 pdo-buffered 0.00220
500 pdo-unbuffered 0.00290
1000 mysql-buffered 0.00227
1000 mysql-unbuffered 0.00328
1000 mysqli-prepared 0.00172
1000 mysqli-buffered 0.00308
1000 mysqli-unbuffered 0.00391
1000 pdo-buffered 0.00423
1000 pdo-unbuffered 0.00466
5000 mysql-buffered 1.51175
5000 mysql-unbuffered 0.01328
5000 mysqli-prepared 0.00209
5000 mysqli-buffered 3.01241
5000 mysqli-unbuffered 1.51294
5000 pdo-buffered 3.01339
5000 pdo-unbuffered 3.01633
10000 mysql-buffered 1.52150
10000 mysql-unbuffered 0.02688
10000 mysqli-prepared 0.00140
10000 mysqli-buffered 4.51979
10000 mysqli-unbuffered 7.52161
10000 pdo-buffered 4.52521
10000 pdo-unbuffered 6.02893




<?php


$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) {
    //$ipAddress = '127.0.0.1';
    $ipAddress = '10.0.2.2';

    $password = "password";
    $runPDO = true;


    $start = microtime(true);
    mysql_connect($ipAddress, 'root', $password);
    echo "Connect ".number_format(microtime(true)-$start, 5)."\n";

    $start = microtime(true);

    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");

    echo "table create ".number_format(microtime(true)-$start, 5)."\n";


    if ($runPDO) {
        // warm up mysql cache
        $db = new PDO('mysql:host='.$ipAddress, 'root', $password);
        foreach ($db->query('select * from '.$table) as $vals){
            $test = $vals;
        }
    }

    $start = microtime(true);
    mysql_connect($ipAddress, 'root', $password);
    $result = mysql_query('select * from '.$table);
    if (!$result) {
        echo "Mysql error: ".mysql_error();

        exit(0);
    }

    while ($row = mysql_fetch_assoc($result)){
        $test = $row;
    }
    echo $size.' mysql-buffered '.number_format(microtime(true)-$start, 5)."\n";

    $start = microtime(true);
    mysql_connect($ipAddress, 'root', $password);
    $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);


    $mysqli = new MySQLi($ipAddress, "root", $password);
    $query = 'select str1, str2 , id from '.$table;
    $statement = $mysqli->prepare($query);
    $statement->bind_result($str1, $str2, $id);
    while ($statement->fetch()) {
    }
    $statement->close();
    //$mysqli->close();


    echo $size.' mysqli-prepared '.number_format(microtime(true)-$start, 5)."\n";


    $start = microtime(true);
    $db = mysqli_connect($ipAddress, 'root', $password);
    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($ipAddress, 'root', $password);
    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";

    if ($runPDO) {
        $start = microtime(true);
        $db = new PDO('mysql:host='.$ipAddress, 'root', $password);
        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='.$ipAddress, 'root', $password,
            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";
    }

}



?>