The MySQL Query Cache

databases

high performance mysql book coverI have been reading about MySQL's Query Caching features in the High Performance MySQL book, 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'


Related Entries

36 people found this page useful, what do you think?

Trackbacks

Trackback Address: 390/94DDFA6BAA7A76F2A3442443F16873A2

Comments

On 06/23/2005 at 7:17:36 AM EDT Philippe wrote:
1
I really liked this article! By the way, I like your site :-)

On 06/23/2005 at 11:03:41 AM EDT Pete Freitag wrote:
2
Philippe, Thanks for the kind words!

By the way if you like an article (or don't like it) you should use the new rating system I have added. That way I will be able to see which articles people like best, and which ones they do not.

On 07/26/2006 at 5:39:26 PM EDT Sean Chighizola wrote:
3
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

On 01/02/2007 at 4:58:57 PM EST Dave Ross wrote:
4
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.

On 07/01/2007 at 11:17:18 PM EDT Parth Patil wrote:
5
Hi Pete, Only older version of mysql 4.0 and versions before that look at the first 3 chars of the select query. So if you are using a newer version of mysql 4.0 or later whitespaces before select won't cause any problems.

On 08/24/2007 at 6:41:45 AM EDT Amir wrote:
6
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

On 01/19/2008 at 6:04:24 AM EST Geoffrey Lee wrote:
7
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

On 12/16/2009 at 5:38:52 AM EST Ilan Hazan wrote:
8
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

On 02/06/2010 at 8:03:56 AM EST itgoran wrote:
9
Useful article.

On 03/24/2010 at 8:12:06 AM EDT Julien Teisseire wrote:
10
Hi, many thanks for your article because I was making a mistake configuring the query_cache_size and your example is perfectly describing the real life need !

On 03/26/2010 at 12:46:08 PM EDT Ujjal wrote:
11
<b>GOOD ONE</b>

On 07/01/2010 at 8:23:15 AM EDT Dhanesh Mane wrote:
12
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.

On 07/31/2010 at 6:17:37 AM EDT dhananjay wrote:
13
good article

On 02/27/2011 at 6:08:47 PM EST AlediaApami wrote:
14
Comments finally working:)

On 03/17/2011 at 4:36:40 AM EDT pradeep kumar jangir wrote:
15
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

On 03/18/2011 at 3:11:28 AM EDT pradeep kumar jangir wrote:
16
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

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?