6.9 MySQL Query Cache

From version 4.0.1, MySQL server features a Query Cache. When in use, the query cache stores the text of a SELECT query together with the corresponding result that was sent to the client. If an identical query is later received, the server will retrieve the results from the query cache rather than parsing and executing the same query again.

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

The query cache is extremely useful in an environment where (some) tables don't change very often and you have a lot of identical queries. This is a typical situation for many web servers that use a lot of dynamic content.

Below is some performance data for the query cache. (These results were generated by running the MySQL benchmark suite on a Linux Alpha 2 x 500 MHz with 2 GB RAM and a 64 MB query cache):

• If all of the queries you're performing are simple (such as selecting a row from a table with one row); but still differ so that the queries can not be cached, the overhead for having the query cache active is 13%. This could be regarded as the worst case scenario. However, in real life, queries are much more complicated than our simple example so the overhead is normally significantly lower.
• Searches after one row in a one row table is 238% faster. This can be regarded as close to the minimum speedup to be expected for a query that is cached.
• If you want to disable the query cache code set query_cache_size=0. By disabling the query cache code there is no noticeable overhead. (query cache can be excluded from code with help of configure option --without-query-cache)

Subsections

• 6.9.1 How The Query Cache Operates
• 6.9.2 Query Cache Configuration
• 6.9.3 Query Cache Options in SELECT
• 6.9.4 Query Cache Status and Maintenance

User Comments

To anybody out there needing a possible boost in database performance, I highly recommend setting the query_cache! We set the variable and noticed pages loading atleast 10 - 20 times faster in a production instance. This is a must if information is constantly being selected out and displayed.

Add your own comment.