Operation of Query Cache
Operation of Query Cache
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_name
Select * from tbl_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.
Because comparison of a query against those in the cache occurs before parsing, the cache is not used for queries of the following types:
* Prepared statements
* Queries that are a subquery of an outer query
* Queries executed within the body of a stored procedure, stored function, or trigger
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.
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.
A query cannot be cached if it contains any of the functions shown in the following table:
BENCHMARK(), CONNECTION_ID(), CURDATE(), CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP(), CURTIME(),
DATABASE(), ENCRYPT(), FOUND_ROWS(), GET_LOCK(), LAST_INSERT_ID(), LOAD_FILE(), MASTER_POS_WAIT(), NOW(), RAND(),
RELEASE_LOCK(), SYSDATE(), UNIX_TIMESTAMP(), USER()
Query Cache SELECT Options
Two query cache-related options may be specified in SELECT statements:
* SQL_CACHE
The query result is cached if the value of the query_cache_type system variable is ON or DEMAND.
* SQL_NO_CACHE
The query result is not cached.
Examples:
SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;
query_cache_type:
* A value of 0 or OFF prevents caching or retrieval of cached results.
* A value of 1 or ON allows caching except of those statements that begin with SELECT SQL_NO_CACHE.
* A value of 2 or DEMAND causes caching of only those statements that begin with SELECT SQL_CACHE.
Setting the GLOBAL query_cache_type value determines query cache behavior for all clients that connect after the change is made. Individual clients can control
cache behavior for their own connection by setting the SESSION query_cache_type value. For example, a client can disable use of the query cache for its own
queries like this:
mysql> SET SESSION query_cache_type = OFF;
