PHP PDO Caching

Question

Hi there,

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 */
}

Answer 1

There is the MySQL query cache. But in general you should definitely keep the identifier for the prepared statement and re-use it.

Answer 2

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.

Answer 3

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.

Answer 4

Thanks all for your comments, very useful.

• I realise the cost of preparing the statement is small, but caching it is so easy to do if I need to (and it seems I do).
• Yes, I will definitely be caching the results of queries where possible, but most of these prepares are bulk inserts.
• Thanks for the MySQL query cache; I am unfortunate enough to be working with a set of code using the old mysql (not even mysqli) library and am looking into how I can best bring things up to date.