Improve MySQL performance with Query Cache

If you want to optimize and speed up responses from MySQL, The MySQL query cache can help.

To enable query cache set the follwoing global variable

mysql> SET GLOBAL query_cache_size = 16777216;

The query_cache_size is defined as Byte, the above command will setup cache size 16Mb:

Now set the cache option

mysql> query_cache_type=OPTION

Set the query cache type. Possible options are as follows:
0 : Don't cache results in or retrieve results from the query cache.
1 : Cache all query results except for those that begin with SELECT S_NO_CACHE.
2 : Cache results only for queries that begin with SELECT SQL_CACHE

You can see all query cache related variable

mysql> SHOW VARIABLES LIKE '%query_cache%';

If you want to enable query cache permanently, open the my.cnf
/etc/my.cnf (Red Hat) or /etc/mysql/my.cnf (Debian) file

Append the following config

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

Now restart mysql

service mysqld restart