MySQL – query_cache_type

[adsense id=”0514458240″ width=”468″ height=”60″]

This parameter can either be set at a global level (which implies that all queries take this on as a default parameter) or at a session level.

The query cache in MySQL stores both the SQL and the returned rows of the query. This is particularly useful if the same query and results are returned numerous times. The query cache is shared among the different sessions so the speed up in performance can be for all sessions. Any time a change occurs on a table i.e. an insert, update or delete the data becomes stale and the cache is flushed of the results.

The query_cache_type allows you to define the behaviour of your queries with the query cache.

Setting the value to:
0 – switches off the caching element, in which case the MySQL will always reprocess every execution of the SQL and obtains its results directly from the table.

1 – The caching occurs for all SQL except those that explicitly have indicated that no caching is to take place.

SELECT SQL_NO_CACHE c1, c2 FROM t1 WHERE c1 = 1;


2 – The caching is available only on demand i.e. where you explicitly state it in the SELECT statement.

SELECT SQL_CACHE c1, c2 FROM t1 WHERE c1 = 1;


To show the power of the caching, I have setup a table consisting of 1,000,000 rows which we will perform a group by operation on. The first operation will not cache the results and we will execute the same operation a second time and see the timing differences:


CREATE TABLE T_TEST (name VARCHAR(255), result VARCHAR(255));


We then generated 1,000,000 rows of random data with name set to either test1, test2, test3 or test4. We then performed the following operations against that data:


select SQL_NO_CACHE name, count(1) as occurrences
from T_TEST
group by name;


Each iteration takes on average 1.14 seconds to complete.

The next iteration uses the following code:


select SQL_CACHE name, count(1) as occurrences
from T_TEST
group by name;


Note that the SQL cache was flushed prior to the run. This was done by adding and removing an entry to the table which makes the cached data stale. The first iteration of this SQL statement took 1.43 seconds to complete but subsequent executions of the exact same SQL statement took 0.008 seconds due to the caching behaviour.