I've been looking for an answer to this but haven't found it anywhere. Are calls to PDO::prepare() cached, or should I cache the result myself, i.e. if I do the following
function foo () {
$handle = PDO::prepare(...);
/* do stuff with the handle */
}
will the prepare() statement be cached by PDO so that it's quickly retrieved the second, third etc. times? Or is it better to do it myself, e.g.
function foo() {
static $handle = null;
if (!$handle) {
$handle = PDO::prepare(...);
}
/* do stuff with the handle */
}
There is the MySQL query cache. But in general you should definitely keep the identifier for the prepared statement and re-use it.
The query cache is gone in MySQL version 8.0, see
https://dba.stackexchange.com/questions/217577/why-mysql-remove-query-cache-in-8-0-version
https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/
Two subsequent calls to PDO::prepare() (even with the same SQL query) should return two different PDOStatement (or handles) to avoid collisions, especially between previous and current bindings you may apply to it. The cost of creating a PDOStatement with prepare() is low anyway. What you may want cached are the results returned from the same SQL query, either raw or built by prepare() and this is a feature of your DBMS (MySQL for instance), not PHP.
It depends on your database driver. With MySQL, PDO will create a native prepared statement by default. You can disable it, if you want to use an actual query cache.
If you absolutely must execute the same query repeatedly, then yes, you'll want to keep that handle around. If you're using emulated prepared statements, then it makes no difference at all.
Some time ago I've tried do it CPDO and it can be used as the standard PDO
