On the Internet, caching is everything. Domain name servers cache your DNS records, proxies and browsers tries to cache your content, your’s server’s OS cache frequently used files into the memory… Although these are all different types of caching, they all have one thing in common, they improves overall performance of the system.

If you want to improve performance of your web site and you seek for the solution on blogs or forums, suddenly you will see keyword “caching” popping up all over you. I guess that the first stage of “caching” on any database driven web site would be to enable database query cache and see how does it perform.

Configuration

MySQL supports query caching since version 4.x. It caches query results into the servers memory so the next time when you execute the same query, results will be served directly from the memory and MySQL doesn’t have to search for your records in the database again.

Key parameters that you should add to your my.ini file to enable query cache are:
query_cache_type=1
query_cache_size=64M
query_cache_limit=5M

• query_cache_type (0) cache is disabled; (1) cache is enabled for all queries except the ones that contain SQL_NO_CACHE keyword; (2) cache is enabled on demand, it will cache only queries which contain SQL_CACHE keyword.
• query_cache_size specify the dedicated memory which will be used to store queries, 64 megabytes in this case
• query_cache_limit specify the maximum size of query result to be stored into the cache; if you have specified 5M limit and you call SELECT * FROM table which retrieves six megabytes of data, the result won’t be cached into the memory.

You can see your current query cache settings by typing the following query:

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 5242880 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 67108864 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
6 rows in set (0.00 sec)

Practice

First lets try one query with query cache disabled. We can use SET GLOBAL to set the variable value during runtime, without editing my.ini and restarting the MySQL. I will perform some full text search with sorting on indexed field:

mysql> SET GLOBAL query_cache_type=0;

mysql> SELECT item_id, date FROM items
WHERE MATCH (name) AGAINST ('notebook' IN BOOLEAN MODE)
ORDER BY date DESC LIMIT 5;
+---------+------------+
| item_id | date |
+---------+------------+
| 212098 | 2007-12-04 |
| 212108 | 2007-12-04 |
| 211738 | 2007-08-04 |
| 211633 | 2007-05-11 |
+---------+------------+
5 rows in set (2.78 sec)

Now lets execute the same query again:

mysql> SELECT item_id, date FROM items
WHERE MATCH (name) AGAINST ('notebook' IN BOOLEAN MODE)
ORDER BY date DESC LIMIT 5;
+---------+------------+
| item_id | date |
+---------+------------+
| 212098 | 2007-12-04 |
| 212108 | 2007-12-04 |
| 211738 | 2007-08-04 |
| 211633 | 2007-05-11 |
| 211624 | 2007-05-11 |
+---------+------------+
5 rows in set (0.15 sec)

You might be deceived with this query time even without caching. Basically, your OS have cached some chunks of data into the working memory, so the MySQL accessed data much faster and provided you the results very quickly. But this is just my desktop machine, not the real production web server, and you don’t know for granted how the OS would handle your table files.

Now lets enable query cache again and repeat the query:

mysql> SET GLOBAL query_cache_type=1;
mysql> SELECT item_id, date FROM items
WHERE MATCH (name) AGAINST ('notebook' IN BOOLEAN MODE)
ORDER BY date DESC LIMIT 5;
+---------+------------+
| item_id | date |
+---------+------------+
| 212098 | 2007-12-04 |
| 212108 | 2007-12-04 |
| 211738 | 2007-08-04 |
| 211633 | 2007-05-11 |
| 211624 | 2007-05-11 |
+---------+------------+
5 rows in set (0.11 sec)

This time the query is executed in 0.11 seconds, but now it is cached for further use which you can see by executing:

mysql> SHOW STATUS LIKE 'qcache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 5232592 |
| Qcache_hits | 0 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 7 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+---------+

So the next time when we execute the same query…
+---------+------------+
| item_id | date |
+---------+------------+
| 212098 | 2007-12-04 |
| 212108 | 2007-12-04 |
| 211738 | 2007-08-04 |
| 211633 | 2007-05-11 |
| 211624 | 2007-05-11 |
+---------+------------+
5 rows in set (0.00 sec)

…we will get the same results in 0.00 seconds, and each time after it will be the same since the result is served directly from the cache. Lets check the Qcache status again and we will see that now we have one hit on our query cache.

mysql> SHOW STATUS LIKE 'qcache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 5232592 |
| Qcache_hits | 1 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 7 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+---------+

Lets explain some of the Qcache status variables:

• Qcache_not_cached display the number of the queries which are not cached which also includes queries like USE, UPDATE, INSERT, etc.
• qcache_lowmem_prunes is the number of queries which are removed from the cache due to the lack of available memory for query cache. If there is not enough memory for some query to be cached, MySQL will try to keep most frequent used queries in the cache and it will remove the ones that haven’t been used lately to free the memory.
• Qcache_hits is ofcourse the number of times that cached results have been used instead of executing full query, and you can use it as one of the parameter to determine the efficiency of query cache for your project.

Limitations and exceptions

MySQL query is a little bit dummy. It does not analyze your query to much, it will just use your entire query as the key to access cached results. Pay attention to the letter casing and spacing in your queries, for example, following queries will be treated as different and each query result will be cached separately, although those queries are the same.

SELECT * FROM table;
select * from table;

Anyway, experienced developers that already use some coding and naming standard won’t have any problem with this obstacle.

Also, keep in mind that your query have to provide the same result each time to be cached, so the queries which use functions like RAND(), CURDATE() etc. will not be cached.

Query invalidation

Query will be deleted from cache and invalidated each time when the one of the tables from query change. MySQL won’t analyze cached records and weather some of the records have been changed, no. Any update/insert of the table will cause invalidation of already cached queries next time.

Lets take a look on this example:

SELECT * FROM categories WHERE category_id < 15

This query will be cached, it will cache first fifteen records from the table categories.

UPDATE categories SET name='Games' WHERE category_id=16;

Although it is obliviously that none of the records from previous select query is not changed with this update query since we have changed the record with id 16, previous query will be invalidated since the table categories have been updated.

This is one of the great disadvantages of MySQL query cache specially if you frequently update and insert records. However, in some cases you can overcome this obstacle and I will show you one example in some of the posts to come.

Here you can find the great post about other limitations of the MySQL query cache and MySQL query cache efficiency.

Conclusion

You can enable MySQL query cache and see how does it performs in your case. Although it is not the most efficient way of caching and does have some disadvantages, you can easily turn it on and off without changing your source code so it might be the easiest way to improve your web site performance.

You can find out more about query cache in MySQL documentation.