:: DEVELOPER ZONE
MySQL Reference Manual :: 5.12 The MySQL Query Cache
Search the MySQL manual:
- 5 Database Administration
- 5.1 The MySQL Server and Server Startup Scripts
- 5.2 The MySQL Instance Manager
- 5.3 Configuring the MySQL Server
- 5.4 The MySQL Server Shutdown Process
- 5.5 General Security Issues
- 5.6 The MySQL Access Privilege System
- 5.7 MySQL User Account Management
- 5.8 Disaster Prevention and Recovery
- 5.9 MySQL Localization and International Usage
- 5.10 The MySQL Log Files
- 5.11 Running Multiple MySQL Servers on the Same Machine
- 5.12 The MySQL Query Cache
Additional languages
Additional formats
From version 4.0.1 on, 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 the identical query is received later, the server
retrieves the results from the query cache rather than parsing and
executing the query again.
The query cache is extremely useful in an environment where you have tables that don't change very often and for which the server receives a lot of identical queries. This is a typical situation for many Web servers that generate a lot of 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 many mysqld
servers updating the same MyISAM tables.
Note: The query cache isn't 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 24.2.4, “C API Prepared Statements”.
Some performance data for the query cache follow. 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're 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.
Query cache capabilities can be excluded from the server entirely by
using the --without-query-cache option to
configure when compiling MySQL.
© 1995-2005 MySQL AB. All rights reserved.
User Comments
Warning: query failed: Unknown column 'user.firstname' in 'field list' in /data0/sites/live/web-main/lib/mysql-cxn.php on line 69
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /data0/sites/live/web-main/lib/docbook.php on line 245
Add your own comment.