PHP PDO: How long are prepared mysql queries cached?

Question

How to take advantage of prepared statements for performance? I understand that something like this might benefit if I put it in a loop:

SELECT `Name` FROM `Hobbits` WHERE `ID` = :ID;

I've read that looping with prepared statements is faster than looping without, but otherwise prepared statements would slightly decrease performance. So - how big may that loop be?

If I run a complex SQL query at the beginning of my code and repeat it with one different parameter at the end - will the second query run faster? (We are using a single connection for each page load). Is there a limit on cached queries, so I better repeat my queries right away?

What about executing the entire script twice with the exact same parameters (reload the page or 2 users)?

Answer 1

A prepared query is given to the SQL server, which parses it and possibly already prepares an execution plan. You're then basically given an id for these allocated resources and can execute this prepared statement by just filling in the blanks in the statement. You can run this statement as often as you like and the database will not have to repeat the parsing and execution planning, which may bring a speed improvement.

As long as you do not throw away the statement, there's no hard timeout for how long the statement will "stay prepared". It's not a cache, it's an allocated resource on the SQL server. At least as long as your database driver uses native prepared statements in the SQL API. PDO for example does not do so by default, unless you set PDO::ATTR_EMULATE_PREPARES to false.

At the end of the script execution though, all those resources will always be deallocated, they do not persist across different page loads. Beyond that, the SQL server may or may not cache the query and its results for some time regardless of the client script.

Answer 2

How long are prepared mysql queries cached?

This is not actually "a cache". Prepared statement lasts as little as during script execution.

If I run a complex SQL query at the beginning of my code and repeat it with one different parameter at the end - will the second query run faster?

The more complex a query, the less effect you will see. Frankly, prepared statement saves you only parsing, while if execution involves temporary or filesort, or table scan - prepared statement would speed up none of them.

On the other hand, for the simple primary-key lookups, which involve no complex query parsing nor building sophisticated query plans, the benefit would be negligible to none.

So - how big may that loop be?

The more iterations it gets - the more benefit. However, in a sane web-application one have to avoid looping queries at all.

What about executing the entire script twice with the exact same parameters (reload the page or 2 users)?

As I said above, there will be no benefit from a prepared statement at all. A classical query cache, however, most likely would fire.

How to take advantage of prepared statements for performance?

Noway. Not in web-serving PHP, at least. In a some long-running cli-based script - may be.

However, prepared statements ought to be used anyway, for the purpose of producing syntactically correct queries.