'PDO'에 해당되는 글 1건

<?php

error_reporting(E_ALL);

ini_set('display_errors', TRUE);

ini_set('display_startup_errors', TRUE);

set_time_limit(0);

$db_addr = '127.0.0.1';

$db_user = 'user';

$db_pass = 'pass';

$table = 'test.benchmark_table';

benchmark($table, 100000);

function benchmark($table, $rows) {

  global $db_addr;

  global $db_user;

  global $db_pass;

  $table_innodb1 = $table."_innodb1";

  $table_innodb2 = $table."_innodb2";

  $table_innodb3 = $table."_innodb3";

  $table_myisam1 = $table."_myisam1";

  $table_myisam2 = $table."_myisam2";

  $table_myisam3 = $table."_myisam3";

  mysql_connect($db_addr, $db_user, $db_pass);

  mysql_query('drop table if exists '.$table_innodb1);

  mysql_query('drop table if exists '.$table_innodb2);

  mysql_query('drop table if exists '.$table_innodb3);

  mysql_query('drop table if exists '.$table_myisam1);

  mysql_query('drop table if exists '.$table_myisam2);

  mysql_query('drop table if exists '.$table_myisam3);

  mysql_query("CREATE TABLE $table_innodb1 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=INNODB");

  mysql_query("CREATE TABLE $table_innodb2 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=INNODB");

  mysql_query("CREATE TABLE $table_innodb3 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=INNODB");

  mysql_query("CREATE TABLE $table_myisam1 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=MyISAM");

  mysql_query("CREATE TABLE $table_myisam2 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=MyISAM");

  mysql_query("CREATE TABLE $table_myisam3 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=MyISAM");

  mysql_query("begin");

  for ($i=0; $i<$rows; $i++) {

    mysql_query("insert into $table_innodb1 values (0, 'benchmark test $i', rand() * $rows * 100)");

    mysql_query("insert into $table_innodb2 values (0, 'benchmark test $i', rand() * $rows * 100)");

    mysql_query("insert into $table_innodb3 values (0, 'benchmark test $i', rand() * $rows * 100)");

    mysql_query("insert into $table_myisam1 values (0, 'benchmark test $i', rand() * $rows * 100)");

    mysql_query("insert into $table_myisam2 values (0, 'benchmark test $i', rand() * $rows * 100)");

    mysql_query("insert into $table_myisam3 values (0, 'benchmark test $i', rand() * $rows * 100)");

  }

  mysql_query("commit");

  mysql_conn1($db_addr, $db_user, $db_pass, $table_innodb1);

  mysql_conn2($db_addr, $db_user, $db_pass, $table_innodb2);

  mysql_conn3($db_addr, $db_user, $db_pass, $table_innodb3);

  mysql_conn1($db_addr, $db_user, $db_pass, $table_myisam1);

  mysql_conn2($db_addr, $db_user, $db_pass, $table_myisam2);

  mysql_conn3($db_addr, $db_user, $db_pass, $table_myisam3);

}

function mysql_conn1($db_addr, $db_user, $db_pass, $table) {

  $i = 0;

  $start = get_time();

  $db = mysql_connect($db_addr, $db_user, $db_pass);

  $result = mysql_query('select * from '.$table, $db);

  while ($row = mysql_fetch_assoc($result)) { $i++; $test = $row; }

  mysql_close($db);

  echo 'rows : '.$i.' '.$table.' mysql '.round(get_time()-$start, 5)."<br>";

}

function mysql_conn2($db_addr, $db_user, $db_pass, $table) {

  $i = 0;

  $start = get_time();

  $db = new mysqli($db_addr, $db_user, $db_pass);

  $result = $db->query('select * from '.$table);

  while ($row = $result->fetch_assoc()) { $i++; $test = $row; }

  $db->close();

  echo 'rows : '.$i.' '.$table.' mysqli '.round(get_time()-$start, 5)."<br>";

}

function mysql_conn3($db_addr, $db_user, $db_pass, $table) {

  $i = 0;

  $start = get_time();

  $db = new PDO('mysql:host='.$db_addr, $db_user, $db_pass);

  foreach ($db->query('select * from '.$table) as $row) { $i++; $test = $row; }

  $db = null;

  echo 'rows : '.$i.' '.$table.' pdo '.round(get_time()-$start, 5)."<br>";

}

function get_time() {

  $t = explode(' ', microtime());

  return ((float)$t[0] + (float)$t[1]);

}

?>

rows : 100000 redblue.benchmark_table_innodb1 mysql  0.39674
rows : 100000 redblue.benchmark_table_innodb2 mysqli 0.3815
rows : 100000 redblue.benchmark_table_innodb3 pdo    0.33571

rows : 100000 redblue.benchmark_table_myisam1 mysql  0.11391
rows : 100000 redblue.benchmark_table_myisam2 mysqli 0.11894
rows : 100000 redblue.benchmark_table_myisam3 pdo    0.12141

rows : 100000 test.benchmark_table_innodb1 mysql  0.2214
rows : 100000 test.benchmark_table_innodb2 mysqli 0.37862
rows : 100000 test.benchmark_table_innodb3 pdo    0.28196

rows : 100000 test.benchmark_table_myisam1 mysql  0.22379
rows : 100000 test.benchmark_table_myisam2 mysqli 0.19565
rows : 100000 test.benchmark_table_myisam3 pdo    0.28953