Everything You Need to Know About MySQL Query Caching

Everything You Need to Know About MySQL Query Caching

Caching can significantly improve the performance of your website manifold. Your site visitors will experience faster loading speed and personalized content. But one caching mechanism in particular can greatly enhance the website performance which is Query Caching.

If you’re using MySQL, then there’s a feature called MySQL Query Cache that can help you increase the performance. Once you’ve cached the web pages, it will set in a memory cache such as Cassandra.
mysql.png


Although you can use Query Caching for literally any purpose and content, you’ll experience the most benefit in read-heavy scenarios. It is capable of invalidating large segments of cache with little changes made to data. The MySQL query cache is also a global one that can be shared by other sessions.

In this article, you’ll learn how to enable query caching in MySQL server.

Adding Configuration Directives

As the first towards setting up MySQL query caching, you need to add the below configuration directives:

Code:
query_cache_size=SIZE
query_cache_type=OPTION
The first directive which is the “query_cache_size” allows you to set the size for the cache. This is the size that will be allotted to for caching the content. The default value will always be zero, which means nothing will be cached. To enable caching, you’d have to increase the value.

The second directive is used to set the type of query caching. There are basically three types to choose from. Those are:

Code:
The Zero “0”
The one “1”
The two “2”
Zero directs the server to not cache the results, nor retrieve anything from query cache.

One instructs the server to cache every query result excluding those that begin with SELECT S_NO_CACHE.

If you set the value to two, it instructs the server cache results only for queries that start with SELECT SQL_CACHE.

How to Enable Query Caching in MySQL?

To enable query caching in MySQL, you need to do the following things:

Enter into the MySQL server as the root user using this command -
Code:
# mysql -u root -p
Provide the root password when you’re prompted to. This would open the MySQL monitor

To set query cache for, let’s say, 32 MB, you need to enter the following commands:
Code:
mysql> SET GLOBAL query_cache_size = 33554432;
You can verify that you’ve set up the cache properly using this command -
Code:
mysql> SHOW VARIABLES LIKE ‘query_cache_size’;
It should return an output like -
Code:
| query_cache_size | 33554432 |
Now, you’re required to append other config directives. You can do this using the following command:

Code:
query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576
As per the above command, the maximum cache size for a particular query result will be no more than 1048576 Kb. You can increase or decrease the limit as per your preference.

After you’ve set the query cache, you’ll see a notice in speed and performance. So that’s how you set query caching in MySQL. If you face problems executing the above tasks, you can contact your hosting provider for assistance.
Author
kumkumsharma
Views
4,254
First release
Last update
Rating
0.00 star(s) 0 ratings

More resources from kumkumsharma

Top