We've upgraded to MySQL 5.6 and start seeing the loading of db server increased significantly, and finally found out the query_cache_type is defaulted to off start from 5.6.

We enabled it again and see the loading decrease, why this value is being disabled by default start from MySQL 5.6? I cannot see the problem in enabled it.

3 Answers 3


You need the history of InnoDB to understand why. Here it goes:


InnoDB and the query cache are in a constant state of war. InnoDB tends to be very heavy-handed when inspecting changes in the InnoDB Buffer Pool and then crosschecking the Query Cache for the same changes.


Before MySQL 5.0, the query cache was disabled for InnoDB. Now, InnoDB interacts with it. To simplify matters, you can just disable the Query Cache by setting the query_cache_size to 0.

According to the MySQL Documentation on query_cache_time

If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution.


Setting query_cache_size to 0 is not a one-size-fits-all solution.

The reason for the war, in the first place, is overhead. InnoDB will always inspect changes. A bigger query cache will make InnoDB work that much harder. Disabling the query cache let's the InnoDB and Query Cache be happy. However, you (the Developer/DBA) might be a casualty of that war by means bad query performance, even with such a peace treaty in place.

Depending on the following

  • Workload
  • Frequency of Changes
  • Frequency of reading the same data

you should set query_cache_size to whatever number you feel increases performance (This being tantamount to starting an underground movement).


In case you are wondering where I came up with this war story, please see my old post

Read it carefully because I learned this from Pages 209-215 of High Performance MySQL (2nd Edition)

I have recommended disabling the query cache to others before

NOTE : I realize the question was about the query_cache_type. It does have an effect on the query cache. Disabling the cache squashes InnoDB's dominance over it. Setting the query_cache_type manually simply forces the Developer/DBA to think carefully about the type of queries the query cache will encounter.

  • Hi, I've read all your links. Actually I tried to turn off query cache again and we see the loading increase significantly again..so we need to turn on again. I am not saying what you say is wrong, maybe just our application is read heavy and query cache is very useful to reduce the loading..(our site is running WordPress)
    – Yoga
    Commented Jun 9, 2014 at 14:21
  • 4
    If only more SO posts read like this (thanks for the fun analogy)! I bet Rolando's lucky kids get told MySQL bedtime stories like this every night! ;)
    – rinogo
    Commented Sep 9, 2015 at 20:20
  • 2
    "Pages 209-215 of High Performance MySQL (2nd Edition)" refers to a chapter called "The MySQL Query Cache", from "When the Query Cache Is Helpful" and to the end. This corresponds to pages 320-329 in the 3rd Edition. Commented Jan 12, 2016 at 15:04
  • @PeterV.Mørch this is why I said in my answer Depending on the following Workload, Frequency of Changes, Frequency of reading the same data you should set query_cache_size to whatever number you feel increases performance (This being tantamount to starting an underground movement). For MySQL 5.x, tuning the query cache is not for the faint-of-heart. You have to know your data. The need for tuning goes away in MySQL 8.0 anyway. Commented Jun 13, 2020 at 17:02

I have a blog post explaining why this is here.

The short version: The query cache causes scalability issues on multi-core machines. So it is now disabled by default.


To complete the answers, Oracle's push for "replacing" the query cache functionality is the memcached integration.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.