MySQL PDO prepared faster than query? What this simple test shows

Question

Here is a simple test that I came across to get a quick idea of ​​the performance that I would pay for using prepared MySQL PDO instructions and using a direct query. There are 2801 rows in the person table. MySQL version 5.5.28 and PHP version 5.3.15. Vanilla installations, with any default settings. Tests run on an iMac with 8 GB.

$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 result:

 query: 21.010436058044 prepare/execute: 20.74036192894 

Which shows no penalty at all. Capabilities:

• Caching prepared statements really works. (Note that I kept the preparation function inside the loop.)

• This is a dummy test because it is too simple.

• There is no theoretical reason why preparation / execution should be slower, and tired of constant criticism, the developers of MySQL / PDO / PHP worked hard to make them faster, trying to force us all to close up.

• Other?

It has been said many times here that using prepared statements is safer than using a query, and with named parameters in PDO (Mysqli does not have them), processing parameters is quite convenient. But it was also often pointed out that there is a penalty for performance if an assertion needs to be prepared every time it is executed.

So, can someone put some tests that contradict my simple test? Or, let’s say, we now recognize that there is no reason not to use prepared statements?

Answer 1

There is one trifle. By default, PDO simply mimics prepared statements.
And although in emulation mode it starts the same old request without actually preparing one operator :)

So, first of all,

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

to include real prepared statements.

it was also often noted that there is a penalty for performance

There is one more trifle. Unfortunately, there is very little real knowledge in the world. And especially in the world of Q&A sites. People tend to repeat the information they read and find it reasonable. Without any proof test or even without their hands. Therefore, the “frequently noted” should not be regarded as a reliable source.

Let's get back to business: although there should be some kind of punishment, it should be insignificant in most cases. If so, you need to set up your system.

In any case, in emulation mode you received it both "fast" and safe.

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

To request lightning

 select title from Board where id = 1 

results

 emulation on off query 0.07 0.130 prepare 0.075 0.145 

but for a rather burdensome request

 select title from Board where id > 1 

results

 emulation on off query 0.96 0.96 prepare 0.96 1.00 

So, as we can see, on a large dataset the difference becomes imperceptible.

There is some difference for the lightning request, but since only 0.0003th fraction is required for one request (for one request), I would say that this is a great example for the word "indifference".

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

Now the problem is with the encoding.

Yes, the strange GBK issue affects PDOs for versions prior to 5.3.3. These versions did not have the ability to set the correct encoding and were inevitably vulnerable (in emulation mode). But since 5.3.3 PDO supports the encoding setting in the DSN, and now everything is fine with it.
For mysqli, mysqli_set_charset() must be used for this purpose with the same (impenetrable) result.

In my own mysqli-based class, I use my own placeholder implementation and don't use prepared statements at all. Not for performance reasons, but to increase reliability.

Answer 2

I have some problems with your methodology:

• If there is absolutely nothing on the server other than a script, which is unlikely, your rudimentary timer obeys the whims of processor scheduling. To solve this problem, write two separate scripts and run them using the * nix time command, time php myscript.php .: time php myscript.php
• Reverse script ordering can generate the same results due to caching mySQL query.
• One test, each of which does not make a diagnosis. Try running the script several hundred or several thousand times, and then compare the results to get a more rounded result.

But there is still no reason not to use prepared statements in the event of a non-static query if you do not want to strictly check all your inputs all the time and still have the option of SQL injection.