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.

Related Articles

Designing for Digital and Print

Having consistent messaging, look, and feel is essential to providing a seamless experience for users. Translating that across digital and print can be a challenge. ...

Rob McFarlane
By Rob McFarlane
Oct 29th, 2018

All The Tests: PINT’s Overview of Web Testing

To help you get started with testing, we’ve compiled some of the basics: Types of Tests you’ll want to consider and the concept of The Testing Pyramid. ...

Preston Resenbeck
By Preston Resenbeck
Sep 18th, 2018

Tell us about your project

Please fill out your information and submit