How to Enable MySQL Caching to Speed Up Your Web Application?

MySqlIf you want to improve the speed of your web application, then you need to enable query caching in your database. In order to get speedy and optimized response from the MySQL, you should add 2 configurations directive to the server:

Enable in configurations File

query_cache_size=SIZE
For the results of query catching, the amount of SIZE (memory) allocated.

You need to comment the query cache type. You can choose options 0, 1 or 2.

query_cache_type= OPTION

OPTION- 2 indicates that the results of query catching is just for queries that start with SELECT SQL_CACHE

OPTION- 1 indicates that the results of query catching is for all queries except for those that start with SELECT S_NO_CACHE

OPTION-0 indicates that no need to cache results from the query cache.

Enable Caching with Server Running

You should check that theinstallation of your MySQL has query caching support available.

MySQL > show variables such as ‘have_query_cache'; +——————+——-+| Variable_name   | Value |+——————+——-+| have_query_cache | YES   |+——————+——-+

After that, you have to see if query caching is enabled. In order to check it, you should check more variable. You can check all in one time for the variable query%

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             | 8388608 |

| query_cache_type             | ON     |

| query_cache_wlock_invalidate | OFF     |

| query_prealloc_size          | 8192   |

+——————————+———+

If the value of query_cache_size is 0 and you would like to change this value, then you have to run the following command.

For example, if you would like to allocate 8MB to the cache, then you will need to use value 1024 * 1024 * 8 = 8388608.

SET GLOBAL query_cache_size = 8388608;

You can set other options according to the same syntax:

SET GLOBAL query_cache_limit = 1048576;

SET GLOBAL query_cache_type = 1;

How do you know if it’s working or not? You need to put the following commands.

mysql> SHOW STATUS LIKE ‘Qc%';

+————————-+——–+

| Variable_name           | Value |

+————————-+——–+

| Qcache_free_blocks     | 65     |

| Qcache_free_memory     | 201440 |

| Qcache_hits             | 18868 |

| Qcache_inserts         | 2940   |

| Qcache_lowmem_prunes   | 665   |

| Qcache_not_cached       | 246   |

| Qcache_queries_in_cache | 492   |

| Qcache_total_blocks     | 1430   |

+————————-+——–+

8 rows in set (0.00 sec)

So, if you would like to improve the web application speed, then Query caching will be best for it. You can monitor the status with the use of above given methods.