MySQL tuning – MySQL query cache:

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)

For linux /etc/my.cnf

1st: set query_cache_type to 1.
(There are 3 possible settings: 0 (disable / off), 1 (enable / on) and 2 (on demand).
2′nd: set query_cache_size to your expected size. I’d prefer to set it at 20MB.

query-cache-type = 1
query-cache-size = 20M

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_min_res_unit     4096

query_cache_size     20971520

query_cache_type     ON

query_cache_wlock_invalidate     OFF

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

SHOW STATUS LIKE ‘%qcache%’

You will see this result:

Variable_name     Value

Qcache_free_blocks     135

Qcache_free_memory     14719568

Qcache_hits     17381

Qcache_inserts     4096

Qcache_lowmem_prunes     0

Qcache_not_cached     1159

Qcache_queries_in_cache     2198

Qcache_total_blocks     4701