We are going to devide this post in four section. Here are the sections:
- How Query Cache Works
- How to use the Query Cache
- Query Cache Configuration
- Query Cache Status & Maintenance
Let’s go into each step one by one.
How Query Cache Works
When you perform any query then MySql will cache the query statement as well as the result returned from the query. So after that if client request the same query then MySql will return the result from the cache rather than executing and fetching data again from the database.
Note: Query Cache will cache the [code]SELECT[/code] statements only.
Query cache will be beneficial when your site have a data which is updated very often and in that cache your server will receive the identical query multiple times. For example, fetching the list of countries will change very often so it will be beneficial to use the Query Cache for such queries.
How to use the Query Cache
There are two options to use the Query Cache using the SELECT Statements.
SQL_CACHE
The result of the query will be cached if it is cacheable and the value of the [code]query_cache_type[/code] system variable is set to [code]ON[/code] or [code]DEMAND[/code].
[cc lang=”mysql”]
SELECT SQL_CACHE FROM table_name;
[/cc]
SQL_NO_CACHE
The query result will not cached.
[cc lang=”mysql”]
SELECT SQL_NO_CACHE FROM table_name;
[/cc]
Query Cache Configuration
Here now you will see that how to configure the MySql Query Cache. First of all you will need to check the query cache is available or not. Availability of the Query Cache can be checked by performing the below query.
[cc lang=”mysql”]
SHOW VARIABLES LIKE ‘have_query_cache’;
[/cc]
If above query returns the YES in front of the have_query_cache then you have a query cache available.
Now you have to check the query cache type. To check the query_cache_type run below query:
[cc lang=”mysql”]
SHOW VARIABLES LIKE ‘query_cache_type’;
[/cc]
There are three possible values for this setting.
OFF or 0 : Query cache is disabled and no one query results will be cached.
ON or 1 : Query cache is enables and queries can be caches without placing the SQL_CACHE
DEMAND or 2 : This indicates that Query Cache is enabled but it will cache the results only when you use SQL_CACHE in query.
Now after enabling the Query Cache you need to make sure that query_cache_size must be set. To check the query_cache_size use the below query:
[cc lang=”mysql”]
SHOW VARIABLES LIKE ‘query_cache_size’;
[/cc]
If this values is set to 0 then you will not able to cache the query result even if Query Cache is set to ON. To set the Query Cache site run below query.
[cc lang=”mysql”]
SET GLOBAL query_cache_size = 100000;
[/cc]
If you set the query cache size to some small number then it will return the warning. So to actually make the data cachable you need to give some high value for the query_cache_size.
Query Cache Status & Maintenance
Now after enabling the Query Cache, You have to maintain that also. We need to FLUSH the Query Cache data on some interval. We can FLUSH the cache data using [code]FLUSH QUERY CACHE[/code] Statement. FLUSH will not remove any query result from the cache.
To clear the Query Cache we can use the [code]RESET QUERY CACHE[/code]. This will remove all queries and results from the cache. [code]FLUSH TABLES[/code] also do the same thing.
To view the status of the Query Cache [code]SHOW STATUS[/code] can be used. We can run below for the same:
[cc lang=”mysql”]
SHOW STATUS LIKE ‘Qcache%’;
[/cc]
