Prepared statement only prepared once?

Question

Connection to database:

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass,
               array(PDO::ATTR_PERSISTENT => true));

Preparation of statement:

$stmt = $db->prepare('SELECT * FROM foo',
                     array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));

Is the statement SELECT * FROM foo only prepared once and then stored on the MySQL server for all subsequent invocations of the PHP script? Or is it re-prepared every time the PHP script is called?

score 4 · Accepted Answer · edited May 23 '17 at 12:08

4

Unfortunately the answer is irrelevant.

Even if the prepared statement remained prepared throughout the lifetime of a persistent connection, PHP would have no way to reference it once the current script ends. Given that the statement handle would go out of scope eventually anyway, chances are that, even if the statement could persist, it probably does not. This is speculation, but it's very likely that PDO cleans up after itself here.

The real problem is that PDO, by default, emulates prepared statements. That is, it does local escaping and substitution of placeholders. This means that the server never sees real prepares, binds and executes. If you want to use real prepares, turn emulation off.

edited May 23 '17 at 12:08

Community

1
1

answered Dec 28 '12 at 11:12

Charles

50,943
13
104
142

I guess I better prepare the statement outside of PDO, using MySQL. – feklee Dec 28 '12 at 11:13
While that's possible, keep in mind that pconnect works *per process*. If you're using Apache or FastCGI, each worker process gets it's own independent (persistent) connection. A statement prepared in one worker's connection won't be present in the connections of others. Please make sure you aren't engaging in micro-optimizations here. MySQL is not an advanced database with a good query planner, and does not greatly benefit from reused prepared statements in the same way that other databases used to. – Charles Dec 28 '12 at 11:14
You write: *"PDO, by default, emulates prepared statements."*. This does not seem to be the case (anymore?). According to the [PDO manual](http://php.net/manual/en/ref.pdo-mysql.php): *"PDO_MYSQL will take advantage of native prepared statement support present in MySQL 4.1 and higher. If you're using an older version of the mysql client libraries, PDO will emulate them for you."* – feklee Dec 28 '12 at 16:18
1

@feklee, that's a subtle lie by the documentation. See [PHP bug #61969](https://bugs.php.net/bug.php?id=61969) (fix the docs) and [bug #54638](https://bugs.php.net/bug.php?id=54638) (fix the implementation), as well as [the accompanying php-internals discussion](http://marc.info/?t=130417648000003&r=1&w=4). – Charles Dec 28 '12 at 18:10

score 1 · Answer 2 · answered Dec 28 '12 at 11:54

Though you could trick PHP into preserving prepared statements between requests within single persistent connection (using "text" prepared statements instead of "binary" ones and couple other hacks), it will require too much efforts but revenue would be barely noticeable.

Anyway, for the performance it's quite irrelevant. If you're in the real need for speeding your queries, consider using HandlerSocket which can greatly increase speed of some queries.

Prepared statement only prepared once?

2 Answers2