The MySQL Query Cache
I 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
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
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
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
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'
- 8 Ways to Save Bandwidth on your RSS Feed - July 12, 2007
- MySQL Optimization Hints - January 2, 2007
- Google Video: MySQL Performance Tuning Best Practices - May 11, 2006
- MySQL DELAY_KEY_WRITE Option - August 11, 2005
- Insert Delayed with MySQL - August 2, 2005
see this post for new improvements:
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 !
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.
how can I set it to 2? shal I directly edit the file. please help.
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
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.
Pradeep Kumar Jangir
- Updating Java on ColdFusion or Lucee
- ColdFusion returning empty response with server-error: true
- Careful applying CF11u16, CF2016u8, CF2018u2
- Sessions don't work in Chrome but do in IE
- csrfVerifyToken does not invalidate the token
- The cf_sql_ is optional in cfqueryparam
- Cookie Expires / Max-Age 1969-12-31T23:59:59.000Z
- Burst Throttling on AWS API Gateway Explained