Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.4Mb
PDF (A4) - 37.4Mb
PDF (RPM) - 36.9Mb
EPUB - 10.6Mb
HTML Download (TGZ) - 10.3Mb
HTML Download (Zip) - 10.4Mb
HTML Download (RPM) - 9.0Mb
Eclipse Doc Plugin (TGZ) - 11.2Mb
Eclipse Doc Plugin (Zip) - 13.4Mb
Man Pages (TGZ) - 204.7Kb
Man Pages (Zip) - 311.7Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  The MySQL Query Cache

9.10.3 The MySQL Query Cache

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. This is a typical situation for many Web servers that generate many dynamic pages based on database content.

The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.


The query cache does not work in an environment where you have multiple mysqld servers updating the same MyISAM tables.

The query cache is used for prepared statements under the conditions described in Section, “How the Query Cache Operates”.


The query cache is not supported for partitioned tables, and is automatically disabled for queries involving partitioned tables. The query cache cannot be enabled for such queries.

Some performance data for the query cache follows. These results were generated by running the MySQL benchmark suite on a Linux Alpha 2×500MHz system with 2GB RAM and a 64MB query cache.

  • If all the queries you are performing are simple (such as selecting a row from a table with one row), but still differ so that the queries cannot be cached, the overhead for having the query cache active is 13%. This could be regarded as the worst case scenario. In real life, queries tend to be much more complicated, so the overhead normally is significantly lower.

  • Searches for a single row in a single-row table are 238% faster with the query cache than without it. This can be regarded as close to the minimum speedup to be expected for a query that is cached.

To disable the query cache at server startup, set the query_cache_size system variable to 0. By disabling the query cache code, there is no noticeable overhead.

The query cache offers the potential for substantial performance improvement, but do not assume that it will do so under all circumstances. With some query cache configurations or server workloads, you might actually see a performance decrease:

  • Be cautious about sizing the query cache excessively large, which increases the overhead required to maintain the cache, possibly beyond the benefit of enabling it. Sizes in tens of megabytes are usually beneficial. Sizes in the hundreds of megabytes might not be.

  • Server workload has a significant effect on query cache efficiency. A query mix consisting almost entirely of a fixed set of SELECT statements is much more likely to benefit from enabling the cache than a mix in which frequent INSERT statements cause continual invalidation of results in the cache. In some cases, a workaround is to use the SQL_NO_CACHE option to prevent results from even entering the cache for SELECT statements that use frequently modified tables. (See Section, “Query Cache SELECT Options”.)

To verify that enabling the query cache is beneficial, test the operation of your MySQL server with the cache enabled and disabled. Then retest periodically because query cache efficiency may change as server workload changes.

User Comments
  Posted by Jon Gabrielson on February 6, 2006
states that:
>Unlike the case in MySQL 4.1, the Cluster storage engine in MySQL 5.0 supports MySQL' query cache.

Which combined with the statement above of:
>Note: The query cache does not work in an environment where you have multiple mysqld servers updating the same MyISAM tables.

seems to imply that the Cluster NBD knows how to correctly
flush the query cache for multiple mysqld servers.
  Posted by Adrian Marius Popa on March 26, 2009
Here is one article about the Query Cache
It's better explained what it really does and what it doesn't

  Posted by Vlatko Šurlan on July 5, 2010
A nice little treatise on MySQL Query Cache with some nasty gotchas and tricks that might shave a few days off of your 'what the heck is going wrong here' time:
  Posted by sapan mohanty on August 19, 2010
Why Should Enable Mysql Cache?
If you want to get optimized and speedy response from your MySQL server then you need to add following two configurations directive to your MySQL server:

The amount of memory (SIZE) allocated for caching query results. The default value is 0, which disables the query cache.

Set the query cache type. Possible options are as follows:
0 : Don't cache results in or retrieve results from the query cache.
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
Howto enable caching in MySQL

You can setup caching as follows:
$ mysql -u root –p

for more details :
  Posted by Nicolas Muller on March 14, 2013
Hi, we write a post talking about query cache in portuguese, if somebody need this information in that language ->
  Posted by Jose Luis Palacios on June 11, 2013
"mysqldump", with "--lock-all-tables" option, runs "FLUSH TABLES" / "FLUSH TABLES WITH READ LOCK" which purges entire query cache.

"mysqlcheck --optimize" runs "OPTIMIZE TABLE" which will invalidate the table and its referring queries from the query cache.
  Posted by Hayden James on July 10, 2014
Due to all the variations of MySQL setups, hardware specs and the fact that databases are all different in demand, query types, reads vs writes, etc, you will have to play around with what query cache config works for "you" and finding that query_cache_size sweet-spot as it were. A MySQL query cache size may be fast on one server but slow on another. Pay attn to prunes just as must as over sizing. I wrote more about this...
  Posted by Chris Wilson on August 15, 2014
The documentation above says that "The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed."

But this is not true in at least one case: loading data into the timezone tables does not invalidate cached queries using CONVERT_TZ.
Sign Up Login You must be logged in to post a comment.