Turn on MySQL query cache to speed up query performance?

Many times developers looking for ways to speed up query, in mysql we can enable query cache to speed up query performance. Whenever query cache is enable, it will cache the query in memory and boost query performance.

As we know, speed is always the most important element in developing a website especially for those high traffic database driven website. You can try to turn on query cache to speed up query.

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

- 1st, set query_cache_type to 1. (There are 3 possible settings: 0 (disable / off), 1 (enable / on) and 2 (on demand).

query-cache-type = 1

- 2nd, set query_cache_size to your expected size. I’d prefer to set it at 20MB.

query-cache-size = 20M

If you set your query-cache-type = 2 ( on demand ), you would wan to modify your sql query to support cache.

SELECT SQL_CACHE field1, field2 FROM table1 WHERE field3 = ‘yes’

To check if your mysql server already enable query cache, simply run this query:-

SHOW VARIABLES LIKE ‘%query_cache%’;

You will see this result:-

+——————-+———+
| Variable_name | Value |
+——————-+———+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_size | 20971520 |
| query_cache_type | ON |
+——————-+———+
4 rows in set (0.06 sec)

To check if your MySQL query cache is working, simply perform a sql query for 2 times and check the query cache variable like below:-

SHOW STATUS LIKE ‘%qcache%’;

+————————-+———-+
| Variable_name | Value |
+————————-+———-+
| Qcache_queries_in_cache | 1 |
| Qcache_inserts | 3 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_free_memory | 20947592 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 4 |
+————————-+———-+

For the first time you execute your SQL query, the time it should take take be longer compare to the second time query. This is due to the MySQL query cache is working!

To know more about MySQL query cache click here

Technorati Tags: mysql tuning, mysql server tuning, mysql performance tuning, mysql, mysql server, mysql db, mysql db server, mysql query cache, query cache, boost performance

Tags: boost performance, mysql, mysql db, mysql db server, mysql performance tuning, mysql query cache, mysql server, mysql server tuning, mysql tuning, query cache

Posted at October 11th, 2006 by chua

If you think this article helps you to solve your problem and clear your headache, feel free to buy me a drink :)