Is query cache used for prepared statements?
Posted by: Peter Moore
Date: January 25, 2005 05:59PM


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();

Connector/J 3.1.6
MySQL Server 4.1.9
JVM 1.5

JDBC connection URL: jdbc:mysql://

Thanks for any help,

Peter Moore

Options: ReplyQuote

Written By
Is query cache used for prepared statements?
January 25, 2005 05:59PM

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.