This section describes how the query cache works when it is operational. Section 18.104.22.168, “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 * FROM
tbl_nameSelect * from
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 whether 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
status variable, not
Section 22.214.171.124, “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
DROP TABLE, or
The query cache also works within transactions when using
In MySQL 5.1, the result from a
SELECT query on a view is
The query cache works for
... queries and stores a value that is returned by a
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
SELECT FOUND_ROWS() query itself
cannot be cached.
Before MySQL 5.1.17, prepared statements do not use the query cache. Beginning with 5.1.17, prepared statements use the query cache under certain conditions, which differ depending on the preparation method:
For a prepared statement executed using the binary
protocol, 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 using the binary protocol. That is, for query
cache purposes, statements issued using the binary
protocol are distinct from statements issued using the
Statements that are issued using the text (nonbinary)
Section 13.5, “SQL Syntax for Prepared Statements”. These
are denoted SQL PS statements here.
Before MySQL 5.1.21, for a prepared statement executed
EXECUTE, it is not cached
if it contains any
? parameter markers.
In that case, the statement after parameter expansion
contains references to user variables, which prevents
caching, even for nonprepared statements. If the statement
contains no parameter markers, the statement is compared
with statements in the query cache that were executed
using the text protocol (that is, it is compared with
other SQL PS statements and nonprepared statements). As of
MySQL 5.1.21, this limitation is lifted and prepared
statements that contain parameter markers can be cached
because expansion directly substitutes the user variable
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 program variables.
It refers to tables in the
INFORMATION_SCHEMA system database.
(MySQL 5.1.63 and later:) It refers to any partitioned tables.
It is of any of the following forms:
SELECT ... LOCK 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 Connector/ODBC section of Chapter 21, Connectors and APIs.
Statements within transactions that use
level also cannot be cached because they use
IN SHARE MODE locking.
It does not use any tables.
It generates warnings.
The user has a column-level privilege for any of the involved tables.
Copyright © 1997, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices