Cache query result to improve porfermance in mysql

Cache query result to improve porfermance in mysql

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries.

The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.

The cache expires automatically when the table is modified (inserts, updates, delete's, etc)

Enabling MySQL Query Cache

Edit your my.cnf and set query_cache_type equal to 1, and set the query_cache_size to some value (here we have set it to 25mb)

query_cache_type = 1
query_cache_size = 26214400
If either query_cache_type or query_cache_size are set to zero caching will not be enabled. If you have lots of RAM on your server you may want to increase the size of the cache accordingly.

Cache Hints

You can also set query_cache_type = 2 - with this setting queries are only cached if you pass the hint SQL_CACHE to them, for example:
SELECT SQL_CACHE something FROM table

Alternativly, if you have query_cache_type = 1, you can tell MySQL that you don't want a specific query to be cached. This is highly recommended because you don't want to fill up the cache with highly dynamic queries (such as a search form). This is done with the hint SQL_NO_CACHE.
SELECT SQL_NO_CACHE stuff FROM table

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


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%’;