Using the query_cache

from the Artful MySQL Tips List

Before version 8.0, query caching can be turned on and off in my.cnf/ini, or dynamically with a procedure like this...
CREATE PROCEDURE setqcache( pcache bool )
  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;
    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;
    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';
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 ...

Last updated 6 Apr 2021

Return to the Artful MySQL Tips page