MySQLi prepared statements

Lessons learned:

• Prepared statements are 13 percent faster than normal statements with escaping
• Prepared statements are 8 percent faster than normal statements without escaping
• To get improvements, you need at least 10000 inserts for 1 statement
• Using insert...set is 0.5-1 percent faster than insert...values

Here is the code:

$db = new mysqli('127.0.0.1', 'root', '', 'test');
$db->query('create table if not exists prep (i1 int, i2 int, s1 varchar(255)) engine=myisam');
$db->query('truncate table prep');

$start = microtime(true);
$stmt = $db->prepare('insert into prep (i1,i2,s1) values (?,?,?)');
$i=0; $j=0; $s=null;
$stmt->bind_param('iis', $i, $j, $s);
for ($i=0; $i<100000; $i++) {
  $j = $i*2;
  $s = 'hello world'.$i;
  $stmt->execute();
}
echo 'prep values '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);
$db->query('truncate table prep');

$start = microtime(true);
$stmt = $db->prepare('insert into prep set i1=?, i2=?, s1=?');
$i=0; $j=0; $s=null;
$stmt->bind_param('iis', $i, $j, $s);
for ($i=0; $i<100000; $i++) {
  $j = $i*2;
  $s = 'hello world'.$i;
  $stmt->execute();
}
echo 'prep set '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);
$db->query('truncate table prep');

$start = microtime(true);
for ($i=0; $i<100000; $i++) {
  $db->query("insert into prep (i1,i2,s1) values (".$i.",".($i*2).",'hello world".$i."')");
}
echo 'no escape values '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);
$db->query('truncate table prep');

$start = microtime(true);
for ($i=0; $i<100000; $i++) {
  $db->query("insert into prep set i1=".$i.", i2=".($i*2).", s1='hello world".$i."'");
}
echo 'no escape set '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);
$db->query('truncate table prep');

$start = microtime(true);
for ($i=0; $i<100000; $i++) {
  $arr = [$i, $i*2, 'hello world'.$i];
  foreach ($arr as &$item) if (!is_numeric($item)) $item = $db->real_escape_string($item);
  $db->query("insert into prep (i1,i2,s1) values ('".$arr[0]."','".$arr[1]."','".$arr[2]."')");
}
echo 'escape string values '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);
$db->query('truncate table prep');

$start = microtime(true);
for ($i=0; $i<100000; $i++) {
  $arr = [$i, $i*2, 'hello world'.$i];
  foreach ($arr as &$item) if (!is_numeric($item)) $item = $db->real_escape_string($item);
  $db->query("insert into prep set i1='".$arr[0]."', i2='".$arr[1]."', s1='".$arr[2]."'");
}
echo 'escape string set '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);
$db->query('truncate table prep');

$start = microtime(true);
for ($i=0; $i<100000; $i++) {
  $arr = [$i, $i*2, 'hello world'.$i];
  foreach ($arr as &$item) $item = $db->real_escape_string($item);
  $db->query("insert into prep (i1,i2,s1) values ('".$arr[0]."','".$arr[1]."','".$arr[2]."')");
}
echo 'escape values '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);
$db->query('truncate table prep');

$start = microtime(true);
for ($i=0; $i<100000; $i++) {
  $arr = [$i, $i*2, 'hello world'.$i];
  foreach ($arr as &$item) $item = $db->real_escape_string($item);
  $db->query("insert into prep set i1='".$arr[0]."', i2='".$arr[1]."', s1='".$arr[2]."'");
}
echo 'escape set '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);
$db->query('truncate table prep');

$start = microtime(true);
for ($i=0; $i<100000; $i++) {
  $arr = array_map([$db, 'real_escape_string'], [$i, $i*2, 'hello world'.$i]);
  $db->query("insert into prep (i1,i2,s1) values ('".$arr[0]."','".$arr[1]."','".$arr[2]."')");
}
echo 'escape map values '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);
$db->query('truncate table prep');

$start = microtime(true);
for ($i=0; $i<100000; $i++) {
  $arr = array_map([$db, 'real_escape_string'], [$i, $i*2, 'hello world'.$i]);
  $db->query("insert into prep set i1='".$arr[0]."', i2='".$arr[1]."', s1='".$arr[2]."'");
}
echo 'escape map set '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);

prep values 13.08
prep set 13.02
no escape values 14.31
no escape set 14.18
escape string values 15.06
escape string set 14.99
escape values 15.10
escape set 15.04
escape map values 15.51
escape map set 15.45