Pantek Library
Hosting Provided By
CybrHost
High Speed Hosting
Applications: MySQL > doc > refman > 5.1 > enquery-cache-how.html (Request Expert MySQL Support)
Skip navigation links

Additional languages

7.5.4.1. How the Query Cache Operates

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

The cache is not used for queries of the following types:

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 5.1, 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.

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:

Do you need more help?X

  • Statements that are issued via the binary protocol using mysql_stmt_prepare() and mysql_stmt_execute(). See Section 28.2.4, “C API Prepared Statements”.

    For a prepared statement executed via 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 via the binary protocol. That is, for query cache purposes, statements issued via the binary protocol are distinct from statements issued via the text protocol.

  • Statements that are issued via the text (non-binary) protocol using PREPARE and EXECUTE. See Section 12.7, “SQL Syntax for Prepared Statements”. These are denoted SQL PS statements here.

    Before MySQL 5.1.21, for a prepared statement executed via PREPARE and 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 non-prepared statements. If the statement contains no parameter markers, the statement is compared with statements in the query cache that were executed via the text protocol (that is, it is compared with other SQL PS statements and non-prepared statements). As of MySQL 5.1.21, this limitation is lifted and prepared statments that contain parameter markers can be cached because expansion directly substitutes the user variable values.

A query cannot be cached if it contains any of the functions shown in the following table:

BENCHMARK()CONNECTION_ID()CONVERT_TZ()
CURDATE()CURRENT_DATE()CURRENT_TIME()
CURRENT_TIMESTAMP()CURTIME()DATABASE()
ENCRYPT() with one parameterFOUND_ROWS()GET_LOCK()
LAST_INSERT_ID()LOAD_FILE()MASTER_POS_WAIT()
NOW()RAND()RELEASE_LOCK()
SLEEP()SYSDATE()UNIX_TIMESTAMP() with no parameters
USER()UUID()UUID_SHORT()

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.

    Can we help you?X

  • It refers to tables in the mysql or INFORMATION_SCHEMA 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 the MyODBC section of Chapter 29, Connectors.

  • It uses TEMPORARY tables.

  • It does not use any tables.

  • It generates warnings.

  • The user has a column-level privilege for any of the involved tables.


User Comments

Posted by Erik Romijn on February 20 2008 4:28pm[Delete] [Edit]

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/

Can't find what you're looking for?X

Add your own comment.
Contact Us  Legal Notices  Order Services Online 
Pantek Home  Privacy Policy  IT news  Site Map  Pantek Library