Query caching can be turned on and off in my.cnf/ini, or dynamically with a procedure like this...
CREATE PROCEDURE setqcache( pcache bool )
BEGIN
IF NOT pcache THEN
IF @prev_query_cache_type IS NOT NULL THEN
SET @prev_query_cache_type = @@LOCAL.query_cache_type;
END IF;
IF @prev_query_cache_size IS NULL THEN
SET @prev_query_cache_size = @@GLOBAL.query_cache_size;
END IF;
SET LOCAL query_cache_type = 0;
SET GLOBAL query_cache_size = 0;
RESET QUERY CACHE;
ELSE
IF @prev_query_cache_type IS NOT NULL THEN
SET LOCAL query_cache_type= @prev_query_cache_type;
END IF;
IF @prev_query_cache_size IS NOT NULL THEN
SET GLOBAL query_cache_size= @prev_query_cache_size;
END IF;
END IF;
SELECT
pcache AS 'Query Caching',
@@LOCAL.query_cache_type AS QCacheType, @prev_query_cache_type AS 'PrevType',
@@GLOBAL.query_cache_size AS QCacheSize,@prev_query_cache_size AS 'PrevSize';
END
Some MySQL DBAs recommend turning it off and leaving it off for most MyISAM and InnoDB instances, because writes flush relevant cache entries. In many an OLTP system, writes are coming in continually, so a lot of effort is wasted flushing the cache with no useful result. In an OLAP system, whether the cache helps depends on how often queries repeat.
If you are going to use the query cache, see http://dev.mysql.com/doc/refman/5.6/en/query-cache-configuration.html. |
|