Ok, I tried your test too. I'm using MySQL 5.0.21 for the test.
There's an optional connection parameter in DBD::mysql, "mysql_server_prepare=1". For example:
$dbh= DBI->connect("dbi:mysql:database=test;host=localhost:mysql_server_prepare=1", "", "");
If you don't set this parameter as you connect, the MySQL client emulates prepared statements, even though the 5.0 server supports server-side prepared statements.
Thus the statement it sends to the server is static; it contains no parameter placeholders. Parameters have been substituted into the SQL statement in the client, before sending the SQL string to the server for parsing and execution.
In this case, executing the same SQL statement multiple times with the same parameter values results in query cache hits. That is, "SHOW STATUS LIKE 'Qcache_hits';" shows an increasing value each time you execute the statement. Except for the first time you execute the statement with a given parameter value, because it has to cache the result once before it can read it from the cache on subsequent executions.
If you do set "mysql_server_prepare=1" when you do the DBI connection, you get a different behavior. You can execute the same SQL statement with the same parameter value until you're blue in the face, but MySQL will not use the query cache to cache the results of a query that contains parameters.
Regards,
Bill K.
Edited 1 time(s). Last edit at 09/03/2006 11:48AM by Bill Karwin.