MySQL Forums
Forum List  »  Perl

Re: How to use Query Cache with Perl DBI
Posted by: Bill Karwin
Date: September 03, 2006 11:45AM

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.

Options: ReplyQuote


Subject
Written By
Posted
September 01, 2006 10:20AM
Re: How to use Query Cache with Perl DBI
September 03, 2006 11:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.