Home / Speed Up Queries / Question No: 159

I need some help about MySQL query cache feature. I want to know, what is query cache and how to use it?

MySQL server from version 4.0.1, has introduced Query Cache. When the Query Cache is enabled, the Query Cache stores the each SELECT query text and its corresponding result. Later on, if an identical query is received, MySQL trieves the results from the query cache instead of parsing and executing that query.

When to use Query Cache

The query cache is extremely useful when your application executes a particular query again and again and tables don't change very often. MySQL caches the result, thereby avoiding the overhead of running through data over and over and thereby increase the execution time. In case your tables change very often or if your queries are textually different every time, the query cache may result in a slowdown instead of a performance improvement.

How to enable Query Cache

Query caching can be enabled by setting the server variables query_cache_type, query_cache_size, and query_cache_limit. If any of these three variables is set to 0, query caching will be disabled.

1. query_cache_type This variable has three status which can be set as follow:
   1. query_cache_type = 0 // disabled
   2. query_cache_type = 1 // enabled (except SELECT SQL_NO_CACHE ... queries)
   3. query_cache_type = 2 // enabled but works on demand (cache only SELECT SQL_CACHE ... queries)
2. query_cache_size The memory allocated to store results. In case of 0, the query cache is disabled. query_cache_size needs a minimum size of about 40KB to allocate its structures.
3. query_cache_limit The results bigger than this are not cached. (Default 1M).

How to set Query Cache Type

query_cache_type can be set at both GLOBAL level and SESSION (user) level. Setting the GLOBAL query_cache_type value determines query cache behavior for all clients that connect after the change is made. Each client can also control cache behavior for his own connection by setting the SESSION query_cache_type value. Take the following two examples, one for GLOBAL level and second for SESSION level:

SET GLOBAL query_cache_type = 1; //Enable query cache for all clients
SET SESSION query_cache_type = 0; //Disable query cache for current client

How to set Query Cache Size

query_cache_size is global variable that accepts numberic value and considers that as bytes. query_cache_size value can be set by SET GLOBAL statement like below:

SET GLOBAL query_cache_size = 46080; //45KB

Some Sample of Query Caching

After enabling query cache, if you do not want the result of a particular query to be cached, you can specify by SQL_NO_CACHE paramter like below:

SELECT SQL_NO_CACHE book_id, book_name FROM books WHERE auhor_id = 9;

In case of above query, the result of the query will not be cached. Similarly, you can enable query caching (on demand) by specifiying SQL_CACHE in your query like below:

SELECT SQL_CACHE book_id, book_name FROM books WHERE auhor_id = 9;

After execution of the above query, the result will be added to the cache memory and will be used if the same query is executed again.

How to check Query Cache Status

You can check query cache status variables by SHOW STATUS statement like below:

SHOW STATUS LIKE "Qcache%";

You can defragment the query cache by the following command:

FLUSH QUERY CACHE;

Some Facts about Query Caching

Query cache works in case-sensitive mode. So the following queries will be cached separately:

SELECT * FROM books WHERE auhor_id = 9;
select * from books WHERE auhor_id = 9;

In some cases queries are not cached. Any query making use of the following functions will not be cached:

• User-Defined Functions
• GET_LOCK
• MASTER_POS_WAIT
• CURRENT_TIMESTAMP
• CURTIME
• ENCRYPT (with one parameter)
• UNIX_TIMESTAMP (without parameters)
• CONNECTION_ID
• RELEASE_LOCK
• NOW
• CURDATE
• CURRENT_TIME
• LAST_INSERT_ID
• USER
• FOUND_ROWS
• LOAD_FILE
• SYSDATE
• CURRENT_DATE
• DATABASE
• RAND
• BENCHMARK

Also the following type of queries will not be cached

• queries contains user variables
• queries references the mysql system database
• queries like below:

  SELECT ... IN SHARE MODE
  SELECT ... FOR UPDATE
  SELECT ... INTO OUTFILE ...
  SELECT ... INTO DUMPFILE ...
  SELECT * FROM AUTOINCREMENT_FIELD IS NULL

Before a query result is fetched from the query cache, MySQL checks that the user has SELECT privilege for all databases and tables involved. If this is not the case, the cached result is not used.

How to clear Query Cache

MySQL clear the query cache automatically whenever a change is made to the table. So any statement like INSERT, UPDATE, DELETE, TRUNCATE, ALTER, DROP TABLE or DROP DATABASE will clear the cache. However, MySQL also provides RESET QUERY CACHE command to clear query cache manually.