Re: How to use Query Cache with Perl DBI

[David Shrewsbury]

Although prepare() may be called, I don't think the driver will necessarily prepare the statement unless there are parameter markers. For example, executing this basic script multiple times:

$dbh= DBI->connect("dbi:mysql:database=test;host=localhost", "", "");
$dbh->selectall_arrayref("select * from testaa");

I can see the Qcache_hits increase after the first call. Qcache_inserts also gets incremented. However, if you force server side prepares, like so:

$dbh= DBI->connect("dbi:mysql:database=test;host=localhost:mysql_server_prepare=1", "", "");
$dbh->selectall_arrayref("select * from testaa");

Then the query cache is not used at all.

If you turn on debugging when running the script, like:

DBI_TRACE=2 perl mytest.pl

you can see that in the first example, the prepare is actually skipped and the mysql_st_internal_execute() function is used. If you force server side prepares, as in the second example, you'll see that mysql_stmt_prepare() is now called. So although prepare() may be called, the statement is not necessary prepared on the server.

-Dave