There are lot many solutions to cache your database queries. Some use client level cache, some server side cache. However, MySQL itself provides a cache mechanism which is called “Query Cache” in which the queries being fired at the database are cached. Clearly saying, the result sets are cached against those query sets.
The following two queries will be cached differently in the MySQL cache, because query cache matches byte by byte.
SELECT * from tablename;
select * from tablename;
Query cache never provides you stale data, so whenever a table is updated the cache is burst and fresh results are provided. The data is cached in different ways, MySQL supports follow cache types.
The variable query_cache_type accepts the numeric value out of 0,1,2. These values have following meaning.
0 – Don’t cache results in or retrieve results from the query cache. (Default value)
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
Now let us see how you can enable query cache on your server. There are two ways of doing so.
Enabling cache via my.cnf
Locate the configuration file for MySQL (named my.cnf ). In debian systems like Ubuntu its located inside /etc/mysql/my.cnf. Now edit the following values in the file.
query_cache_size = 278875340
query_cache_type=1
query_cache_limit=1049600
Enabling cache via MySQL command line
Login to MySQL prompt using the following command.
mysql –h localhost -u root –ppassword
At the command line type the following command to enable query cache.
mysql> SET GLOBAL query_cache_size = 16771072;
;this will set the query size to 10MB.
Now let us verify if the changes have been done or no.
mysql> SHOW VARIABLES LIKE ‘query_cache_size’;
If everything went well, the result of the above query will be :
+——————+—————–+
| Variable_name | Value |
+——————+—————–+
| query_cache_size | 16771072 |
+——————+—————–+
1 row in set (0.00 sec)
You should see some significance drop in result fetching time of database. However, query cache should be used where you require more select commands than insert command. And you need to fire some common queries. For example, you need to show a blog post to 10,000 users everyday, then query cache is the best way.
Stay Digified !!
Sachin Khosla
