MySQL 5.1 Database Reference Guide :: 5.13.1 How the Query Cache Operates

Previous / Next / Up / Table of Contents

5.13.1. How the Query Cache Operates

$8.99

This section describes how the query cache works when it is operational. Section 5.13.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 * 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.

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 5.13.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.

Transactional InnoDB tables that have been changed are invalidated when a COMMIT is performed.

The query cache also works within transactions when using InnoDB tables, making use of the table version number to detect whether its contents are still current.

In MySQL 5.1, queries generated by views are cached.

The query cache works for SELECT SQL_CALC_FOUND_ROWS ... and SELECT FOUND_ROWS() type queries. 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.

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()` with one parameter
`FOUND_ROWS()`	`GET_LOCK()`	`LAST_INSERT_ID()`
`LOAD_FILE()`	`MASTER_POS_WAIT()`	`NOW()`
`RAND()`	`RELEASE_LOCK()`	`SYSDATE()`
`UNIX_TIMESTAMP()` with no parameters	`USER()`

A query also is not cached under these conditions:

It refers to user-defined functions (UDFs).
It refers to user variables.
It refers to tables in the mysql system 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 Section 26.1.14.1, “How to Get the Value of an AUTO_INCREMENT Column in ODBC”.
It was issued as a prepared statement, even if no placeholders were employed. For example, the query used here is not cached:
```
char *my_sql_stmt = "SELECT a, b FROM table_c";
/* ... */
mysql_stmt_prepare(stmt, my_sql_stmt, strlen(my_sql_stmt));
```
See Section 25.2.4, “C API Prepared Statements”.
It uses TEMPORARY tables.
It does not use any tables.
The user has a column-level privilege for any of the involved tables.

Previous / Next / Up / Table of Contents