(mysql.info) query-cache-how

(mysql.info) query-cache-in-select
 
 5.14.1 How the Query Cache Operates
 -----------------------------------
 
 This section describes how the query cache works when it is
 operational.  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 
 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.0, queries generated by views are cached.
 
 Before MySQL 5.0, a query that began with a leading comment could be
 cached, but could not be fetched from the cache. This problem is fixed
 in MySQL 5.0.
 
 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  `USER()'                 
 no parameters                                     
 
 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 
      odbc-and-last-insert-id.
 
    * 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  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.
Info Catalog
(mysql.info) query-cache
(mysql.info) query-cache-in-select
automatically generated byinfo2html