5.13. 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 extremely 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.
Note: The query cache does not
return stale data. When tables are modified, any relevant entries
in the query cache are flushed.
Note: The query cache does not
work in an environment where you have multiple
mysqld servers updating the same
MyISAM tables.
Note: The query cache is not used
for server-side prepared statements. If you're using server-side
prepared statements consider that these statement won't be
satisfied by the query cache. See
Section 25.2.4, “C API Prepared Statements”.
Some performance data for the query cache follows. These results
were generated by running the MySQL benchmark suite on a Linux
Alpha 2 x 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.
If you build MySQL from source, query cache capabilities can be
excluded from the server entirely by invoking
configure with the
--without-query-cache option.
