Should I cache my Queries?
Regarding MySQL databases I have heard this question several times and I have asked it myself several times. The query cache of MySQL is a very old feature, which does not mean that it is bad. Yet, you rarely hear people advocating the use of the cache anymore.
What does the Query Cache?
MySQL's query cache stores SELECT statements as text and the corresponding result sent to the client. If the server now receives the exact SELECT statement again, it can send back the result and does not have to do any large calculations. This is actually the basic concept of a cache, and thus nothing out of the ordinary. You remember the task you got and the result you got. This can save an extreme amount of work in some scenarios. The Internet today without caches would be much slower. Also, according to the documentation of MySQL, this advantage shows up. Here is a speed improvement of 238% if you query single rows per select. The overhead in the worst case, however, should only be 13%. The machine used for this benchmark had 2x 500MHz processing power. Therefore the benchmark might not be completely up-to-date. But that still sounds good, if you consider what effect a few percent acceleration can have on a database.
Problems with Caching
With these improvements, by enabling a feature, you wonder why it's not popular anymore. With every caching system there are a some difficulties and this is also true for the MySQL Query Cache. The most important question is how long is an entry in the cache valid? You could define that an entry is valid for, say, x hours and then needs one has to renew it. But what do you do if the data changes before that? Anyone who now receives the data from the cache will see an outdated version. In many use cases, this is not a problem. Whether you see now or in 10 minutes that someone has changed his profile picture on Facebook does not change that much. With SQL database, however, it often looks different. We use those type of, because they guarantee that data that has been changed once cannot be read again in its old form. This is also referred to as the strict consistency of a database, which is part of the ACID property of SQL databases.
To solve this problem, the MySQL Query Cache uses a simple implementation. All entries in the query cache for a table are deleted when any change is made to the table. So if you have a database with several hundred write operations per second, which in this context would not even be considered to be a high volume, the query cache is completely useless. That means it represents only an overhead of 13% and a performance advantage of almost 0%.
Another good argument to disable the query cache is that it is deprecated since MySQL 5.7.20. The major Version 8.0 dropped the query cache completely. So if you never worked with MySQL before version 8.0, you probably never came in contact with the query cache. On the other hand, if you did use it, you will have to do without it after the migration to MySQL 8.0 anyway. It is safe to assume that this feature was not removed from MySQL because it was helpful recently.
In summary, if you use the query cache, check if it brings an advantage. Most likely, it only brings overhead and no real benefits. Then you should disable it. If you plan to migrate to 8.0, it will be dropped anyway. The query cache was an interesting idea, but its implementation is no longer up to date. Moreover, it brings more problem than it solved.