- Documentation Library
- Table of Contents
- MySQL 6.0 Reference Manual
- MySQL 5.1 Reference Manual
- MySQL 5.0 Reference Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
- 7.5 Optimizing the MySQL Server
- 7.5.1 System Factors and Startup Parameter Tuning
- 7.5.2 Tuning Server Parameters
- 7.5.3 Controlling Query Optimizer Performance
- 7.5.4 The MySQL Query Cache
- 7.5.5 Examining Thread Information
- 7.5.6 How Compiling and Linking Affects the Speed of MySQL
- 7.5.7 How MySQL Uses Threads for Client Connections
- 7.5.8 How MySQL Uses Memory
- 7.5.9 How MySQL Uses Internal Temporary Tables
- 7.5.10 How MySQL Uses DNS
[+/-]
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.
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 before MySQL 5.1.17. If you are using server-side prepared statements, consider that these statements will not be satisfied by the query cache. As of 5.1.17, the query cache is used under the conditions described in Section 7.5.4.1, “How the Query Cache Operates”.
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.
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.
User Comments
The query cache is shared by all sessions.
http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-limitations.html
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.
Unfortunately, by default, the Connector/J library uses the C API prepared statement method to execute prepared statements. This means that if you are using the prepared statement methods in Connector/J your SELECT statements are not being cached in the query cache. If you have a read-intensive Java application, you can use the emulated prepared statement option of the Connector/J by adding the "useServerPrepStmts=false" to your JDBC URL. This will allow the statements to be cached by the query cache.
I tried this with Connector/J and found that the parameter is called useServerPreparedStmts, not useServerPrepStmts. Just a quick note to those who wish to use the cache.
Prepared statements have been recently turned off by default in all MySQL connectors. (JDBC, .NET, DBD::mysql, etc)
!!IMPORTANT!!
REF : http://lists.mysql.com/commits/28777
BUG#21074
If you run a large system DO NOT implement query caching without reading this bug.
We had huge problems with query caching, and in the end only solved them by setting our cache size to 128M.
TIP: BIGGER IS NOT NESECARILLY BETTER!
Add your own comment.