Utilizing MySQL Query Cache

If your application runs the same queries on a routine basis, caching those queries will be extremely beneficial to your overall performance. By enabling the query cache, you can take a query that takes 7 seconds to run the first time and cut it down to less than 1/2 for each additional request. If you’re running a dynamic website with backend content that doesn’t change very often then this can be a huge performance and scalability boost for you.

The query_cache_size variable is set to zero by default which means it’s completely disabled. There are 3 variables that set the options for the query cache: query_cache_type, query_cache_size, and query_cache_limit.

The first variable, query_cache_type=OPTION, can set the query cache to be either on, off, or on demand. Unless you want to explicitly define when to use query cache, you can set this value to ‘1’ or on.

The second variable, query_cache_size=SIZE, specifies the amount of memory allocated for caching query results. This should be set to a level that takes into consideration available system memory, the number of cached queries, and the size of those cached queries. Setting this value to something like 16M will probably be enough for your average website.

The third variable, query_cache_limit=SIZE, sets the maximum size for each individual query results. By default, it’s set to 1MB.

If you configure these values appropriately to your application, it can make a vast difference in database performance and the speed of your site.