Databases Reference
In-Depth Information
that refer to user-defined functions, stored functions, user variables, temporary tables,
tables in the
mysql
database, or any table that has a column-level privilege. (For a list
of everything that makes a query uncacheable, see the MySQL manual.)
We've heard statements such as “MySQL doesn't check the cache if the query contains
a nondeterministic function.” This is incorrect. MySQL cannot know whether a query
contains a nondeterministic function unless it parses the query, and the cache lookup
happens
before
parsing. The server performs a case-insensitive check to verify that the
query begins with the letters
SEL
, but that's all.
However, it is correct to say “The server will find no results in the cache if the query
contains a function such as
NOW()
,” because even if the server executed the same query
earlier, it will not have cached the results. MySQL marks a query as uncacheable as
soon as it notices a construct that forbids caching, and the results generated by such a
query are not stored.
A useful technique to enable the caching of queries that refer to the current date is to
include the date as a literal value, instead of using a function. For example:
... DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY) -- Not cacheable!
... DATE_SUB('2007-07-14', INTERVAL 1 DAY) -- Cacheable
Because the query cache works at the level of a complete
SELECT
statement when the
server first receives it from the client connection, identical queries made inside a sub-
query or view cannot use the query cache, and neither can queries in stored procedures.
Prepared statements also cannot use the query cache in versions prior to MySQL 5.1.
MySQL's query cache can sometimes improve performance, but there are a few issues
you should be aware of when using it. First, enabling the query cache adds some over-
head for both reads and writes:
• Read queries must check the cache before beginning.
• If the query is cacheable and isn't in the cache yet, there's some overhead due to
storing the result after generating it.
• There's overhead for write queries, which must invalidate the cache entries for
queries that use tables they change. Invalidation can be very costly if the cache is
fragmented and/or large (has many cached queries, or is configured to use a large
amount of memory).
The query cache can still be a net gain. However, as we explain later, the extra overhead
can add up, especially in combination with contention caused by queries trying to lock
the cache to perform operations on it.
For InnoDB users, another problem is that transactions limit the query cache's useful-
ness. When a statement inside a transaction modifies a table, the server invalidates any
cached queries that refer to the table, even though InnoDB's multiversioning might
hide the transaction's changes from other statements. The table is also globally
uncacheable until the transaction commits, so no further queries against that table—
