|
|
| Applications |
| Mailing Lists |
| Miscellaneous |
| Operating Systems |
| Programming |
This section describes how the query cache works when it is operational. Section 7.5.4.3, “Query Cache Configurationâ€, describes how to control whether it is operational.
Incoming queries are compared to those in the query cache before parsing, so the following two queries are regarded as different by the query cache:
SELECT * FROMtbl_nameSelect * fromtbl_name
Queries must be exactly the same (byte for byte) to be seen as identical. In addition, query strings that are identical may be treated as different for other reasons. Queries that use different databases, different protocol versions, or different default character sets are considered different queries and are cached separately.
The cache is not used for queries of the following types:
Queries that are a subquery of an outer query
Queries executed within the body of a stored function, trigger, or event
Before a query result is fetched from the query cache, MySQL
checks that the user has SELECT privilege
for all databases and tables involved. If this is not the
case, the cached result is not used.
If a query result is returned from query cache, the server
increments the Qcache_hits status variable,
not Com_select. See
Section 7.5.4.4, “Query Cache Status and Maintenanceâ€.
If a table changes, all cached queries that use the table
become invalid and are removed from the cache. This includes
queries that use MERGE tables that map to
the changed table. A table can be changed by many types of
statements, such as INSERT,
UPDATE, DELETE,
TRUNCATE, ALTER TABLE,
DROP TABLE, or DROP
DATABASE.
The query cache also works within transactions when using
InnoDB tables.
In MySQL 6.0, the results of a
SELECT query on a view is cached.
The query cache works for SELECT SQL_CALC_FOUND_ROWS
... queries and stores a value that is returned by a
following SELECT FOUND_ROWS() query.
FOUND_ROWS() returns the
correct value even if the preceding query was fetched from the
cache because the number of found rows is also stored in the
cache. The SELECT FOUND_ROWS() query itself
cannot be cached.
Prepared statements that are issued via the binary protocol
using mysql_stmt_prepare()
and mysql_stmt_execute() (see
Section 27.2.4, “C API Prepared Statementsâ€), are subject to
limitations on caching. Comparison with statements in the
query cache is based on the text of the statement after
expansion of ? parameter markers. The
statement is compared only with other cached statements that
were executed via the binary protocol. That is, for query
cache purposes, prepared statements issued via the binary
protocol are distinct from prepared statements issued via the
text protocol (see Section 12.7, “SQL Syntax for Prepared Statementsâ€).
A query cannot be cached if it contains any of the functions shown in the following table:
A query also is not cached under these conditions:
It refers to user-defined functions (UDFs) or stored functions.
It refers to user variables or local stored routine variables.
It refers to tables in the
mysqlorINFORMATION_SCHEMAsystem database.It is of any of the following forms:
SELECT ... IN SHARE MODE SELECT ... FOR UPDATE SELECT ... INTO OUTFILE ... SELECT ... INTO DUMPFILE ... SELECT * FROM ... WHERE autoincrement_col IS NULL
The last form is not cached because it is used as the ODBC workaround for obtaining the last insert ID value. See the MyODBC section of Chapter 28, Connectors.
It uses
TEMPORARYtables.It does not use any tables.
It generates warnings.
The user has a column-level privilege for any of the involved tables.
|
Contact Us Legal Notices Order Services Online Pantek Home Privacy Policy IT news Site Map Pantek Library |
User Comments
Query caching is also not done in certain cases with transactions, see
http://www.mysqlperformanceblog.com/2008/01/29/how-mysql-query-cache-works-with-transactions/
Add your own comment.