[MySQL] Enabling MySQL Query Cache to Speed Up Query Performance

If you ever have managed some website/portal that has any meaningful traffic, you already know what can it do to your MySQL server performance if you don’t optimise your queries and enable php server side caching. One of the things that you can do at DB level is to enable mysql_query_cache to speed up DB performance.

A Good or not so good thing about mysql_query_cache is that the cache expires automatically once the table is modified (inserts, updates, delete, etc). If there are these operations are veing performed in every table in your website, it might not help much but it can’t harm your website either so go ahead and just enable the cache and see what difference it makes.

How to Enable mysql_query_cache

To enable mysql_query_cache, you need to edit your my.cnf and set query_cache_type to 1, and set the query_cache_size to some value. This is set it to 64Mb in the following example. Default MySQL location for this file is /etc/my.cnf on your Linux server.

How to Check if mysql_query_cache is Enabled Already

 
mysql> show variables like 'query%';

You will see something like this:

 
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 67108864|
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+

Please give special attention to query_cache_type variable which should be set to ON after making my.cnf changes, the query_cache_size variable should not be set to zero. Zero value means your query cache is practically disabled since it has no memory at its disposal to use.

When Enabling mysql_query_cache Can Help Most

Following are conditions which can best exploit the benefits of the MySQL query cache, although the query cache can be effective in most situations, but it will help greatly in following cases:

1. Identical queries are issued by the same or multiple clients on a repetitive basis.
2. The underlying data being accessed is static or semi-static in nature.
3. Queries have the potential to be resource-intensive and/or build brief, but complexly computed result sets.

Hope that helps.

Cheers!

Related posts:

1. Tips to Speed up Your Website & Reducing Load on Web Server
2. Copying MySQL Database From One Server To Another Remote MySQL DB Server
3. Enabling PHP Short Tags in php.ini file or using .htaccess file
4. [MySQL] Setting date data type default value NULL instead of 0000-00-00
5. Setting different cache age for different feeds using Magpie RSS – PHP RSS Parser