HostOnNet Blog

Speed Up Your Web Site With MySQL Query Caching

Looking for Linux Server Admin or WordPress Expert? We can help.

To speed up query, enable the MySQL query cache, before that you need to set few variables in mysql configuration file ( my.cnf or my.ini)

The first you make sure that your installation of MySQL actually has query caching support available.

mysql>  SHOW VARIABLES LIKE 'have_query_cache';


mysql> show variables like 'query%';
| Variable_name                | Value   |
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
7 rows in set (0.00 sec)


query_cache_size – This is the size of the cache in bytes. Setting this value to 0 will effectively disable caching.

query_cache_type – This value must be ON or 1 for query caching to be enabled by default.

query_cache_limit – This is the maximum size query (in bytes) that will be cached.

If the query_cache_size value is set to 0 or you just want to change it, you’ll need to run the following command, keeping in mind that the value is in bytes.

For instance, if you wanted to allocate 9 MB to the cache we’d use 1024 * 1024 * 9 = 9437184 as the value.

SET GLOBAL query_cache_size = 9437184;

Similarly, the other options can be set with the same syntax:

SET GLOBAL query_cache_limit = 1048576;

SET GLOBAL query_cache_type = 1;

You can use the SHOW STATUS command to pull all the variables that start with “Qc”

mysql> SHOW STATUS LIKE 'Qc%';

Enable in Config File

Next we need to update /etc/my.cnf configuration file for MySQL.

Open the file and then add these values if they don’t already exist in the file.

query_cache_size = 268435456

Query caching can significantly improve the speed of your web application, especially if your application does mostly reads. Monitor the status using the methods above and see how it works over time.

About Annie

I've been working in Technical Section for over 10 years in a wide range of tech jobs from Tech Support to Software Testing. I started writing blog for my future reference and useful for all.
Posted in MySQL

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.