DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info.gz) Query Cache How

Info Catalog (mysql.info.gz) Query Cache (mysql.info.gz) Query Cache (mysql.info.gz) Query Cache in SELECT
 
 5.11.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 or
 not it is operational.
 
 Queries are compared 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.
 
 If a query result is returned from query cache, the server increments
 the `Qcache_hits' status variable, not `Com_select'.   Query Cache
 Status and Maintenance.
 
 If a table changes, then 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.
 
 In MySQL 4.0, the query cache is disabled within transactions (it does
 not return results). Beginning with MySQL 4.1.1, the query cache also
 works within transactions when using `InnoDB' tables (it uses the table
 version number to detect whether or not its contents are still current).
 
 Before MySQL 5.0, a query that begins with a leading comment might 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 following functions:
 
 `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 will not be cached under these conditions:
 
    * It contains user-defined functions (UDFs).
 
    * It contains user variables.
 
    * It refers to the tables in the `mysql' system database.
 
    * It is of any of the following forms:
 
           SELECT ... IN SHARE MODE
           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.   ODBC and
      last_insert_id.
 
    * It uses `TEMPORARY' tables.
 
    * It does not use any tables.
 
    * The user has a column-level privilege for any of the involved
      tables.
 
    * Before a query is fetched from the query cache, MySQL checks that
      the user has `SELECT' privilege for all the involved databases and
      tables. If this is not the case, the cached result is not used.
 
 
Info Catalog (mysql.info.gz) Query Cache (mysql.info.gz) Query Cache (mysql.info.gz) Query Cache in SELECT
automatically generated byinfo2html