From version 4.0.1 on, MySQL Server features a query cache. When in use, the query cache stores the text of a SELECT query together with the corresponding result that was sent to the client. If the identical query is received later, the server retrieves the results from the query cache rather than parsing and executing the query again.
The query cache is extremely useful in an environment where (some) tables don't change very often and you have a lot of identical queries. This is a typical situation for many Web servers that generate a lot of dynamic pages based on database content.
Note: The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.
Note: The query cache does not work in an environment where you have many mysqld servers updating the same MyISAM tables.
Some performance data for the query cache follow. These results were generated by running the MySQL benchmark suite on a Linux Alpha 2 x 500MHz system with 2GB RAM and a 64MB query cache.
If all the queries you're performing are simple (such as selecting a row from a table with one row), but still differ so that the queries cannot be cached, the overhead for having the query cache active is 13%. This could be regarded as the worst case scenario. In real life, queries tend to be much more complicated, so the overhead normally is significantly lower.
Searches for a single row in a single-row table are 238% faster with the query cache than without it. This can be regarded as close to the minimum speedup to be expected for a query that is cached.
To disable the query cache at server startup, set the query_cache_size system variable to 0. By disabling the query cache code, there is no noticeable overhead. Query cache capabilities can be excluded from the server entirely by using the --without-query-cache option to configure when compiling MySQL.
This section describes how the query cache works when it is operational. the section called “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. See the section called “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 no parameters | USER() |
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. See the section called “How to Get the Value of an AUTO_INCREMENT Column in ODBC”.
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.
There are two query cache-related options that may be specified in a SELECT statement:
The query result is cached if the value of the query_cache_type system variable is ON or DEMAND.
The query result is not cached.
Examples:
SELECT SQL_CACHE id, name FROM customer; SELECT SQL_NO_CACHE id, name FROM customer;
The have_query_cache server system variable indicates whether the query cache is available:
mysql> SHOW VARIABLES LIKE 'have_query_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+
Several other system variables control query cache operation. These can be set in an option file or on the command line when starting mysqld. The query cache-related system variables all have names that begin with query_cache_. They are described briefly in the section called “Server System Variables”, with additional configuration information given here.
To set the size of the query cache, set the query_cache_size system variable. Setting it to 0 disables the query cache. The default cache size is 0; that is, the query cache is disabled.
If the query cache is enabled, the query_cache_type variable influences how it works. This variable can be set to the following values:
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 value of query_cache_type 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 value of query_cache_type. For example, a client can disable use of the query cache for its own queries like this:
mysql> SET SESSION query_cache_type = OFF;
To control the maximum size of individual query results that can be cached, set the query_cache_limit variable. The default value is 1MB.
The result of a query (the data sent to the client) is stored in the query cache during result retrieval. Therefore the data usually is not handled in one big chunk. The query cache allocates blocks for storing this data on demand, so when one block is filled, a new block is allocated. Because memory allocation operation is costly (timewise), the query cache allocates blocks with a minimum size given by the query_cache_min_res_unit system variable. When a query is executed, the last result block is trimmed to the actual data size so that unused memory is freed. Depending on the types of queries your server executes, you might find it helpful to tune the value of query_cache_min_res_unit:
The default value of query_cache_min_res_unit is 4KB. This should be adequate for most cases.
If you have a lot of queries with small results, the default block size may lead to memory fragmentation, as indicated by a large number of free blocks. Fragmentation can force the query cache to prune (delete) queries from the cache due to lack of memory. In this case, you should decrease the value of query_cache_min_res_unit. The number of free blocks and queries removed due to pruning are given by the values of the Qcache_free_blocks and Qcache_lowmem_prunes status variables.
If most of your queries have large results (check the Qcache_total_blocks and Qcache_queries_in_cache status variables), you can increase performance by increasing query_cache_min_res_unit. However, be careful to not make it too large (see the previous item).
query_cache_min_res_unit is present from MySQL 4.1.
You can check whether the query cache is present in your MySQL server using the following statement:
mysql> SHOW VARIABLES LIKE 'have_query_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+
You can defragment the query cache to better utilize its memory with the FLUSH QUERY CACHE statement. The statement does not remove any queries from the cache.
The RESET QUERY CACHE statement removes all query results from the query cache. The FLUSH TABLES statement also does this. To monitor query cache performance, use SHOW STATUS to view the cache status variables:
mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+--------+ | Variable_name | Value | +-------------------------+--------+ | Qcache_free_blocks | 36 | | Qcache_free_memory | 138488 | | Qcache_hits | 79570 | | Qcache_inserts | 27087 | | Qcache_lowmem_prunes | 3114 | | Qcache_not_cached | 22989 | | Qcache_queries_in_cache | 415 | | Qcache_total_blocks | 912 | +-------------------------+--------+
Descriptions of each of these variables are given in the section called “Server Status Variables”. Some uses for them are described here.
The total number of SELECT queries is equal to:
Com_select + Qcache_hits + queries with errors found by parser
The Com_select value is equal to:
Qcache_inserts + Qcache_not_cached + queries with errors found during columns/rights check
The query cache uses variable-length blocks, so Qcache_total_blocks and Qcache_free_blocks may indicate query cache memory fragmentation. After FLUSH QUERY CACHE, only a single free block remains.
Every cached query requires a minimum of two blocks (one for the query text and one or more for the query results). Also, every table that is used by a query requires one block. However, if two or more queries use the same table, only one block needs to be allocated.
The information provided by the Qcache_lowmem_prunes status variable can help you tune the query cache size. It counts the number of queries that have been removed from the cache to free up memory for caching new queries. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache. Tuning information is given in the section called “Query Cache Configuration”.