Is query cache used for prepared statements?
Hi,
I know this is kind of an odd question, but when using a prepared statement, is the query cache still used?
I understand that idealy, you keep a reference to your prepared statement, and just re-bind / re-execute it. I'm in the lucky situation of trying to fix some code that basically discards the statement after just one use. It's going to be some time before I can change that code (I'm stuck with a home-grown data abstraction layer), but performance is becoming an issue, so I was hoping to let the query cache mitigate this problem in the mean time. However, it doesn't appear to be grabbing valid queries.
I have correctly set up my query cache on the server. If I flush the query cache and run one of the queries in question via the MySQL-client, Qcache_inserts increases by one, and subsequent manual runs of the query increase Qcache_hits. However, when the query is running via my application, Qcache_not_cached appears to increment each time. The query is a select. The first three characters are "sel" (there is no preceeding whitespace). I know that my application's SQL string for the call to prepare is the same as what I'm running manually, because I'm logging it.
The application runs inside Tomcat, and basically does the following for each query call:
--------------------------------------------------------------------
// Get conn object via Tomcat JDBC pool (i.e. DataSource)
//...
pStatement = conn.prepareStatement(sqlString);
// pStatement.setInt(), .setString(), etc
rSet = pStatement.executeQuery();
----------------------------------------------------------------------
Versions:
----------------------------
Connector/J 3.1.6
MySQL Server 4.1.9
JVM 1.5
---------------------------
JDBC connection URL: jdbc:mysql://10.1.1.6/pcs?profileSql=false&autoReconnect=true
Thanks for any help,
Peter Moore