Cache database query and result set

Question

I am creating a java desktop application.

I am using mysql database as back end.

In my application some query was get executed more than one time, so i want to cache the query result and use it for the next time.

For caching i try prepared statement but i don't get any positive result.

Is any other way to cache the query and query result set?

Answer 1

Please check the version of mysql that you are using. It seems Before MySQL 5.1.17, prepared statements do not use the query cache. http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html

It also mentions some conditions under which query cache will not work. I think the alternative to that is to use memcache, but I don't know how good it is. But one thing for sure all these caching methods donot work well if you have frequent updates. You don't really have much control on the cache I guess, it gets invalidated when ever there is an update on actual table(to avoid dirty reads) So I suppose yours is not so frequently updated table while in production. And if that is the case you can very well think of using application level caching with ofcourse cache invalidation service/criteria in place.