O'Reilly logo

MySQL Reference Manual by Kaj Arno, David Axmark, Michael Widenius

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

MySQL Query Cache

From Version 4.0.1, 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 is sent to a client. If another identical query is received, the server can then retrieve the results from the query cache rather than parsing and executing the same 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 use a lot of dynamic content.

Following is some performance data for the query cache (we got this by running the MySQL benchmark suite on a Linux Alpha 2x500MHz with 2G of RAM and a 64M query cache):

  • If you want to disable the query cache code, set query_cache_size=0. By disabling the query cache code there is no noticeable overhead. (query cache can be excluded from code with help of configure option --without-query-cache)

  • If all of the queries you’re preforming 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. However, in real life, queries are much more complicated than our simple example, so the overhead is normally significantly lower.

  • Searches after one row in a one-row table are 238% faster. This can be regarded as close to the minimum speedup to be expected for a query that is cached.

How the Query Cache Operates

Queries are compared before parsing. Thus:

SELECT * FROM TABLE

and

Select * from table

are regarded as different queries for query cache, so queries need to be exactly the same (byte for byte) to be seen as identical. In addition, a query may be seen as different if for instance, one client is using a new communication protocol format or a character set different from that used by another client.

Queries that use different databases, use different protocol versions, or default character sets are considered different queries and are cached separately.

The cache does work for SELECT CALC_ROWS ... and SELECT FOUND_ROWS( ) ... type queries because the number of found rows is also stored in the cache.

If a table changes (INSERT, UPDATE, DELETE, TRUNCATE, ALTER, or DROP TABLE|DATABASE), all cached queries that used this table (possibly through a MRG_MyISAM table!) become invalid and are removed from the cache.

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

A query cannot be cached if it contains one of these functions:

Function

Function

Function

Function

User-Defined Functions

CONNECTION_ID

FOUND_ROWS

GET_LOCK

RELEASE_LOCK

LOAD_FILE

MASTER_POS_WAIT

NOW

SYSDATE

CURRENT_TIME- STAMP

CURDATE

CURRENT_DATE

CURTIME

CURRENT_TIME

DATABASE

ENCRYPT (with one parameter)

LAST_INSERT_ID

RAND

UNIX_TIME- STAMP (without parameters)

USER

BENCHMARK

Nor can a query be cached if it contains user variables, or if it is of the forms SELECT ... IN SHARE MODE or SELECT * FROM AUTOINCREMENT_FIELD IS NULL (to retrieve last insert id - ODBC workaround).

However, FOUND ROWS( ) will return the correct value, even if the preceding query was fetched from the cache.

Queries that don’t use any tables, or in cases where the user has a column privilege for any of the involved tables, are not cached.

Before a query is fetched from the query cache, MySQL will check that the user has the SELECT privilege to all the involved databases and tables. If this is not the case, the cached result will not be used.

Query Cache Configuration

The query cache adds a few MySQL system variables for mysqld which may be set in a configuration file, on the command-line when starting mysqld:

query_cache_limit

Don’t cache results that are bigger than this. (Default 1M.)

query_cache_size

The memory allocated to store results from old queries. If this is 0, the query cache is disabled (default).

query_cache_startup_type

This may be set (only numeric) to:

Option

Description

0

OFF (don’t cache or retrieve results)

1

ON (cache all results except SELECT SQL_NO_CACHE ... queries)

2

DEMAND (cache only SELECT SQL_CACHE ... queries)

Inside a thread (connection), the behaviour of the query cache can be changed from the default. The syntax is as follows:

SQL_QUERY_CACHE_TYPE = OFF | ON | DEMAND
SQL_QUERY_CACHE_TYPE = 0   | 1  | 2

Option

Description

0 or OFF

Don’t cache or retrieve results.

1 or ON

Cache all results except SELECT SQL_NO_CACHE ... queries.

2 or DEMAND

Cache only SELECT SQL_CACHE ... queries.

SQL_QUERY_CACHE_TYPE depends on the value of query_cache_startup_type when the thread was created. This is the default.

Query Cache Options in SELECT

There are two possible query cache-related parameters that may be specified in a SELECT query:

Option

Description

SQL_CACHE

If SQL_QUERY_CACHE_TYPE is DEMAND, allow the query to be cached. If SQL_QUERY_CACHE_TYPE is ON, this is the default. If SQL_QUERY_CACHE_TYPE is OFF, do nothing.

SQL_NO_CACHE

Make this query non-cachable don’t allow this query to be stored in the cache.

Query Cache Status and Maintenance

With the FLUSH QUERY CACHE command you can defragment the query cache to better utilise its memory. This command will not remove any queries from the cache. FLUSH TABLES also flushes the query cache.

The RESET QUERY CACHE command removes all query results from the query cache.

You can monitor query cache performance in SHOW STATUS:

Variable

Description

Qcache_queries_in_cache

Number of queries registered in the cache

Qcache_inserts

Number of queries added to the cache

Qcache_hits

Number of cache hits

Qcache_not_cached

Number of non-cached queries (not cachable, or due to SQL_QUERY_CACHE_TYPE)

Qcache_free_memory

Amount of free memory for query cache

Qcache_total_blocks

Total number of blocks in query cache

Qcache_free_blocks

Number of free memory blocks in query cache

Total number of queries = Qcache_inserts + Qcache_hits + Qcache_not_cached.

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 (big) free block remains.

Note: Every query needs 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 needs one block, but if two or more queries use the same table only one block needs to be allocated.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required