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

Static Site vs CMS Site

Wondering if your site should be CMS-based or static? Learn more about the benefits and drawbacks of these site structures. ...

Chris Coley
By Chris Coley
Mar 4th, 2019

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

Tell us about your project