Pete Freitag Pete Freitag

The MySQL Query Cache

Updated on November 16, 2023
By Pete Freitag
databases

I have been reading about MySQL's Query Caching features in the High Performance MySQL book (ISBN 0596003064), and on the web. I have also been playing around with it on my own. I have concluded that it is a pretty cool feature! You will need MySQL 4.0.1 or higher to play...

I think what I like best about it is that the cache expires automatically when the table is modified (inserts, updates, delete's, etc). So it may not be terribly harmful to just enable the cache, and see what happens.

The High Performance MySQL book states that the Query Cache identifies cacheable (is that a word?) queries by looking for SEL in the first three characters of the SQL statement. However in my testing I found that whitespace or comments before the SELECT statement did not have any effect on caching. Perhaps the JDBC driver trims whitespace and comments before sending the SQL to the server.

Enabling MySQL Query Cache

Edit your my.cnf and set query_cache_type equal to 1, and set the query_cache_size to some value (here we have set it to 25mb)

query_cache_type = 1 
query_cache_size = 26214400

If either query_cache_type or query_cache_size are set to zero caching will not be enabled. If you have lots of RAM on your server you may want to increase the size of the cache accordingly. There are some more settings you can tweak but these will get you going.

Note you can also edit these settings using MySQL Administrator. They can be found under Health > System Variables > Memory > Cache

Cache Hints

You can also set query_cache_type = 2 - with this setting queries are only cached if you pass the hint SQL_CACHE to them, for example:

SELECT SQL_CACHE something FROM table

Alternativly, if you have query_cache_type = 1, you can tell MySQL that you don't want a specific query to be cached. This is highly recommended because you don't want to fill up the cache with highly dynamic queries (such as a search form). This is done with the hint SQL_NO_CACHE.

SELECT SQL_NO_CACHE stuff FROM table

Making the hints database independent

If your like me, you cringe at the thought adding database server specific SQL code to your queries. The High Performance MySQL Book has a tip that will allow you to use the hints and not break compatibility:

SELECT /*! SQL_NO_CACHE */ stuff FROM table

This trick will also work with the SQL_CACHE hint. And if you are really like me you will miss that ! in there, don't forget that or it won't work.

MySQL Query Cache and Prepared Statements

Some very good news is that MySQL Query Cache does seam to work well with prepared statements. In ColdFusion if you use the CFQUERYPARAM tag your using prepared statements. ColdFusion's builtin query caching mechanism does not allow queries with CFQUERYPARAM to be cached. They can be cached with MySQL Query Cache however.

So if you have some code such as this:

SELECT stuff FROM table 
WHERE name = <cfqueryparam value="#url.name#">

The SQL statement looks like this:

SELECT stuff FROM table
WHERE name = ?

Buy MySQL is smart enough to cache this query:

SELECT stuff FROM table
WHERE name = 'bob'


mysql cache caching performance databases

The MySQL Query Cache was first published on June 21, 2005.

If you like reading about mysql, cache, caching, performance, or databases then you might also like:

Discuss / Follow me on Twitter ↯

Comments

Pete,

I'm using prepared statements in CFMX 7 & MySQL 5.0.x and any query using the cfqueryparam tag is not being cached.

I posted this earlier today on Jay Pipes website (http://jpipes.com/index.php?/archives/88-Trains,-Podcasts,-and-A-MySQL-performance-Tip.html),

"I was interested to see if prepared statements are cached in CFMX, so after a few tests I found:

The query cache in MySQL 5.0.x does not cache prepared statements in CFMX X using the cfqueryparam tags.

Com_stmt_close 82
Com_stmt_execute 82
Com_stmt_prepare 82
Qcache_hits 0
Qcache_inserts 0
Qcache_not_cached 90
Qcache_queries_in_cache 0 "

Any thoughts on why this is?

Sean
by Sean Chighizola on 07/26/2006 at 3:39:26 PM UTC
In order for JDBC prepared statements to be cached by MySQL, you need to add the following flags to the JDBC connection string: &useServerPrepStmts=false&cachePrepStmts=true&prepStmtCacheSize=4096&prepStmtCacheSqlLimit=4096

You can play around with the prepared statement cache size, but 4096 works for me in a heavy-use production java system.

Disclaimer: I have no idea if this will work correctly with the prepared stmts that CF creates.
by Dave Ross on 01/02/2007 at 2:58:57 PM UTC
The caching machanism described is good yet not always usable.

I have a situation in which a long query (almost a minute) runs and while it relies on tables that are being updated, I wouldn't mind giving the user outdated (to some set tolerance) results of it.

Is there something of the sort that can be done in MySQL?

It's possible for me to programatically (PHP) create some caching table to the hold results but it seems a little awkward.

Any ideas?

Thanks
by Amir on 08/24/2007 at 4:41:45 AM UTC
The MySQL 5.0 manual specifically states that queries using prepared statements are not cached "[b]ecause comparison of a query against those in the cache occurs before parsing..."

http://dev.mysql.com/doc/refman/5.0/en/query-cache-how.html
by Geoffrey Lee on 01/19/2008 at 4:04:24 AM UTC
The query cache limitations are always improving with new releases.
see this post for new improvements:
http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache
by Ilan Hazan on 12/16/2009 at 3:38:52 AM UTC
Hey all
When i query -> SHOW VARIABLES LIKE 'query_cache_type'

It says ON, and when I open my.cnf and look for query_cache_type variable. its not there.

Any idea?
how can I set it to 2? shal I directly edit the file. please help.
by Dhanesh Mane on 07/01/2010 at 6:23:15 AM UTC
hi,

plz tell me about my.cnf file location on local system xampp folder when i execute SHOW STATUS LIKE ?%qcache%?; then show all stuts 0 so where i changes for cache size or type
by pradeep kumar jangir on 03/17/2011 at 2:36:40 AM UTC
hi all,

i enable query cache type and set size of cache and query execute first time take some time but second time work vastly

but one problem :

i execute first query then execute another query that take first time more time and work fast then i execute first query that take more time like first query execute first time

why plz tell me any solution for it.

regards,

Pradeep Kumar Jangir
by pradeep kumar jangir on 03/18/2011 at 1:11:28 AM UTC