PHP MySQLi Multiple Inserts

Question

I'm wondering if prepared statements work the same as a normal mysql_query with multiple VALUES.

INSERT INTO table (a,b) VALUES ('a','b'), ('c','d');

VS

$sql = $db->prepare('INSERT INTO table (a,b) VALUES (?, ?);

If I use the prepared statement in a loop, is MySQL optimizing the insert in the background to work like it would in the first piece of code there, or is it just like running the first piece of code inside a loop with one value each time ?

Answer 1

If you use the prepared statement in a loop, it will be more efficient than running the raw query each time because of analysis that only needs to be done once with the prepared statement. So no, it is not the same, to that extent.

Answer 2

I went ahead and ran a test where one query uses a prepared statement, and the other builds the entire query then executes that. I'm probably not making what I'm wanting to know easy to understand.

Here's my test code. I was thinking prepared statements sort of held back execution until a $stmt->close() was called to optimize it or something. That doesn't appear to be the case though as the test that builds the query using real_escape_string is at least 10 times faster.

<?php

$db = new mysqli('localhost', 'user', 'pass', 'test');

$start = microtime(true);
$a = 'a';
$b = 'b';

$sql = $db->prepare('INSERT INTO multi (a,b) VALUES(?, ?)');
$sql->bind_param('ss', $a, $b);
for($i = 0; $i < 10000; $i++)
{
    $a = chr($i % 1);
    $b = chr($i % 2);
    $sql->execute();
}
$sql->close();

/*$sql = '';
for($i = 0; $i < 10000; $i++)
{
    $a = $db->real_escape_string(chr($i % 1));
    $b = $db->real_escape_string(chr($i % 2));
    $sql .= ",('$a','$b')";
}
$db->query('INSERT INTO multi (a,b) VALUES ' . substr($sql, 1));*/

echo microtime(true) - $start;


//$db->query('TRUNCATE TABLE multi');
$db->close();

?>