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