MySQL Query Cache Part II/II

In the previous post we have seen the basic theory on MySQL Query Cache. Now In this second part, I provide you details on Query Cache configuration and monitoring.

First check, Query Cache is available or not.

Please execute below SHOW command:

To disable the query cache at server startup, set the query_cache_size system variable to 0. By default, the query cache is disabled.

Using query_cache_size you can set the size of Query Cache which must be larger when you are planning to hold much data into Cache but again this is a task of analysis of your usages.

Query Cache with SELECT :

In above two select statement you can find two identifiers.


The query result is cached if it is cacheable and the value of the query_cache_type system variable is ON or DEMAND.


The server does not use the query cache. It neither checks the query cache to see whether the result is already cached,nor does it cache the query result.

Using query_cache_type you can set type of query cache. It should be ON,OFF or DEMAND.

  • A value of 0 or OFF prevents caching or retrieval of cached results.
  • A value of 1 or ON enables caching except of those statements that begin with SELECT SQL_NO_CACHE.
  • A value of 2 or DEMAND causes caching of only those statements that begin with SELECT SQL_CACHE.

To monitor query cache performance, use SHOW STATUS to view the cache status variables:

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of