Are prepared statements cached server-side across multiple page loads with PHP?

Question

I learnt about prepared statements when making a JDBC-enabled Java application, and my app uses a connection pooling layer that assures me that prepared statements are cached server-side and this gives a performance benefit.

However, with PHP everything I've read says that they are only cached for the life of the page load. Generally I don't repeat the same query many times, but run several different queries, on a given page load, but will repeat them across multiple page loads.

As my PHP processes are persistent (i.e. they will serve hundreds of pages in their lifetime instead of just one, using PHP-FPM), I was wondering if they will re-use database connections, rather than spawning and killing them off for each hit.

1. Will using PHP-FPM with mysqli or PDO keep connections longer than a single page load?
2. If it doesn't, can I make it?
3. If it does, or I do #2, will this persist the caching of prepared statements longer than just one page load?

Edit:

Just to clarify, I'm not talking about the query cache, which is another beast entirely, or caching the output of queries. I want to cache the compiled prepared statement and its execution plan server-side.

Answer 1

When a request is served php "cleans" the instance and frees resources and other variables. This is done in several steps. Since fastcgi keeps the process alive after a request not all steps are executed and not all memory is freed. There is e.g. EG(persistent_list) which is used by mysql_pconnect(), pg_pconnect(), ... This list isn't emptied between requests as long as the process keeps alive (could be, depending on the actual implementation, but that would defy the purpose of EG(persistent_list)). If you use persistent connections your script might get a "re-used" connection established during a previous request.
To (re-)use a prepared statement directly you need the identifier for that statement (and that connection). When using (php-)postgresql this is simply a (connection-wise) unique string you pass to pg_execute(), so your script has no problem to gain access to the statement previously prepared by another instance (using the same connection).
Using mysqli or PDO-mysql you need a resource/object as statement identifier. That's kind of a problem since neither the mysqli nor the pdo extension seem to offer a way of storing the resource in EG(persist_list) between requests and you can't recreate it either. Unless php-fpm offers such a "service" it's seems impossible to re-use a mysql prepared statement directly.
All you can hope for is MySQL's server-side query cache. In recent versions (see link) it may recognize the statement when using prepared statements. But even then it doesn't re-use the actual prepared statement:

For a prepared statement executed via the binary protocol, comparison with statements in the query cache is based on the text of the statement after expansion of ? parameter markers. The statement is compared only with other cached statements that were executed via the binary protocol. That is, for query cache purposes, statements issued via the binary protocol are distinct from statements issued via the text protocol.

So, if I'm not mistaken, currently you can't re-use a mysql statement prepared during a previous request in php.

Answer 2

You're confusing what is happening at the PHP/Java tier with what's happenning in the database.

Yes, using prepared statements (usually) means that the execution plan is cached by the database itself (NOT the PHP/Java tier). However it does not follow that this always results in better performance - and an explanation of this would take several hundred pages. However I infer from what you've said elsewhere you are using MySQL as the DBMS which makes the discussion somewhat simpler (IIRC none of the storage engines implement histograms). Typically MySQL will be able to cache enough information about a schema to be able to generate a plan without any disk I/O. OTOH, using prepared statements mean a minimum of three round trips to the DBMS for each query (present statement, present params, retrieve results) while using inlined values eliminates on of these round trips. In the absence of histogram indexes, the value of the variables is irrelevant to the optimal plan detectable by the optimizer.

The fact that you are using PHP, or PHP-FPM or Java with single or persistent or pooled connections is irrelevant to whether or not prepared-statements are cached/re-used by the DBMS.

HTH

C.