Using the query_cache

from the Artful MySQL Tips List


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.7/en/query-cache-configuration.html
http://haydenjames.io/mysql-query-cache-size-performance

Return to the Artful MySQL Tips page