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.
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:
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:
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 -
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:
You can verify that you’ve set up the cache properly using this command -
It should return an output like -
Now, you’re required to append other config directives. You can do this using the following command:
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.
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.
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=OPTIONThe 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”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 -pTo set query cache for, let’s say, 32 MB, you need to enter the following commands:
Code:
mysql> SET GLOBAL query_cache_size = 33554432;
Code:
mysql> SHOW VARIABLES LIKE ‘query_cache_size’;
Code:
| query_cache_size | 33554432 |
Code:
query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576After 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.
