MySQL PDO prepared faster than query? That's what this simple test shows

Question

Here's a simple test I ran to get a quick idea of the performance penalty I would pay for using MySQL PDO prepared statements vs. using a straight query. There are 2801 rows in the person table. MySQL version 5.5.28 and PHP version 5.3.15. Vanilla installations, with whatever the default parameters are. Tests run on an iMac with 8GB.

$pdo = new PDO('mysql:host=localhost;dbname=cwadb_local', 'root', "");
$start = microtime(true);
for ($i = 0; $i < 200; $i++) {
    $pdo->query("select * from person where name_last = 'smith' or true");
}
echo "<p>query: " . (microtime(true) - $start);

$start = microtime(true);
for ($i = 0; $i < 200; $i++) {
    $stmt = $pdo->prepare("select * from person where name_last = :last or true");
    $stmt->execute(array('last' => 'smith'));
}
echo "<p>prepare/execute: " . (microtime(true) - $start);

and this was the output:

query: 21.010436058044

prepare/execute: 20.74036192894

Which shows no penalty at all. Possibilities:

• Caching of the prepared statement is really working. (Notice I kept the prepare function inside the loop.)

• It's a bogus test because it's too simple.

• There's no theoretical reason why prepare/execute should be slower, and, tired of the constant criticisms, the MySQL/PDO/PHP developers have worked extra hard to make them faster in an attempt to get us all to shut up.

• Other?

It's been said many times here that using prepared statements is more secure than using query and, with the named parameters in PDO (Mysqli doesn't have them), dealing with the parameters is pretty convenient. But, it's just as often noted that there's a performance penalty if the statement has to be prepared each time it's executed.

So, can someone supply some tests that contradict my simple test? Or, shall we just now admit that there's no reason not to use prepared statements?

Answer 1

There is one little thing to mention. By default, PDO just emulate prepared statements.
And while in emulation mode, it runs the same old query without actually preparing a single statement :)

So, first of all,

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

to turn real prepared statements on.

it's just as often noted that there's a performance penalty

There is another little thing to mention.
Sadly, there are very little real knowledge in the world. And especially in the world of Q&A sites. People tend to repeat the information they had read and found reasonable. Without running any tests to proof or even without laying their hands on. So, "often noted" shouldn't be considered as a reliable source at all.

Back to the matter: though there should be some penalty, it should be insignificant most of time. If it is - you have to tune your system up.

Anyway, in the emulation mode you got it both "fast" and safe.

Update
Well, after running your tests on my data, I've got to say that there is something wrong with your database if you have 3 times difference on a large dataset.

For a lightning query

select title from Board where id = 1

results are

emulation   on      off
query      0.07    0.130
prepare    0.075   0.145

while for the quite burdensome query

select title from Board where id > 1

results are

emulation   on      off
query      0.96    0.96
prepare    0.96    1.00

So, as we can see, on a large dataset the difference become unnoticeable.

For the lightning query there is some difference, but, as it takes only 0,0003th faction of second (for a single query) - I'd say that's perfect example for the word "indifference".

For the equal results between query()/prepare() - I have only one idea - PDO uses prepare/execute for all queries, even those without bindings.

Now to the encoding problem.

Yes, weird GBK problem does affect PDO for versions prior 5.3.3. These versions had no way to set the proper encoding and were unavoidable vulnerable (in emulation mode). But since 5.3.3 PDO supports setting encoding in DSN, and now everything is all right with it.
For mysqli one have to use mysqli_set_charset() for this very purpose with the very same (impenetrable) result.

In my own class which is based on mysqli, I am using my own placeholder implementation and use no prepared statements at all. Not for performance reasons but for better reliability.

Answer 2

I have some issues with your methodology:

1. Unless there is absolutely nothing else running on the server at the same time as the script, which is unlikely, your rudimentary timer is subject to the whims of CPU scheduling. To address this, write two separate scripts and run them using *nix's time command, ie: time php myscript.php
2. Reversing the order of the scripts may generate the same results due to mySQL caching the query.
3. One test each does not a diagnosis make. Try running each script a few hundred, or few thousand, times and then average the results to get a more well-rounded result.

But there's still no reason to not use prepared statements in the case of a non-static query unless you like rigorously validating all of your inputs all of the time and still having the possibility of SQL injection.