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?

1) Awesome 2) Yes 3) "Who cares?" - use prepared statements. — user166390
Just for the sake of it, try running it again with more queries and try swapping the order as well (prepared first). — Supericy
I would have thought prepared statements would be expected to be faster than straight queries, on the basis that the SQL in the prepared statement is the same each time. It allows the query parsing to be cached in the database server, which isn't possible if you have constantly changing non-prepared WHERE clauses. — halfer
halfer: I don't follow your logic. Since the arguments to the functions are not changing, the opportunity to cache (client, server, wherever) is identical. — Marc Rochkind

Your Common Sense Your Common Sense · Accepted Answer · 2013-01-05T00:16:44

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.

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

2 Answers