There is no doubt that the MySQL is one of the most popular open source database servers out there. It is a free, powerful and reliable tool to store most types and sets of data. One thing worth mentioning however, is that it’s default configuration wont do you any good in terms of performance.

The MySQL has all sort of variables that will help increase the speed of data retrieval – for example, temporary tables, memory caches, key buffers and buffer pools. However, the default values of all the performance related settings are set to run stable on typical hardware from around 2000, so in 2011 a DBA may want to look at some of them and tune the MySQL for better performance.

Now, one of the values that is quite often mentioned as a great performance improvement is the query_cache. Enabling it is the most common suggestion – but is also the first tricky part! Tricky, because there’s no query_cache=on variable in MySQL. There are others, query_cache_size and query_cache_type, which seem to do the job, as well as the query_cache_limit. The important bit is that if the query_cache_size is set to zero, the query cache is DISABLED – regardless of what any of the other query_cache_ variables are set to.

But there is also another tricky part in the query cache. The whole system was designed for MySQL 4.0, when the processors had single core only and the memory was counted in megabytes. In today’s world it is effective only in certain scenarios, while highly ineffective in most real world applications due to read-locks and mutexes.

So DBA can’t believe blindly to the output of tuning scripts that aim to aid the process, or to the advice given by countless internet articles. As usual in database world, and in IT world in general, the answer to the question “should I turn query cache on or leave it off ?” is, “it depends”, and decisions have to be made after some system testing and benchmarking.