Oracle 5.0, Reference Manual

Page: 726 / 3232

The MySQL Query Cache
706
•
SQL_CACHE
The query result is cached if it is cacheable and the value of the
query_cache_type
[485] system
variable is
ON
or
DEMAND
.
•
SQL_NO_CACHE
The server does not use the query cache. It neither checks the query cache to see whether the
result is already cached, nor does it cache the query result. (Due to a limitation in the parser, a space
character must precede and follow the
SQL_NO_CACHE
keyword; a nonspace such as a newline
causes the server to check the query cache to see whether the result is already cached.)
Examples:
SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;
8.6.3.3. Query Cache Configuration
The
have_query_cache
[455] server system variable indicates whether the query cache is available:
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
When using a standard MySQL binary, this value is always
YES
, even if query caching is disabled.
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 system variables all have names that
begin with
query_cache_
. They are described briefly in Section 5.1.4, “Server System Variables” ,
with additional configuration information given here.
To set the size of the query cache, set the
query_cache_size
[485] system variable. Setting it to 0
disables the query cache. The default size is 0, so the query cache is disabled by default.
Be careful not to set the size of the cache too large. Due to the need for threads to lock the cache
during updates, you may see lock contention issues with a very large cache.
Note
When using the Windows Configuration Wizard to install or configure MySQL,
the default value for
query_cache_size
[485] will be configured automatically
for you based on the different configuration types available. When using the
Windows Configuration Wizard, the query cache may be enabled (that is, set
to a nonzero value) due to the selected configuration. The query cache is also
controlled by the setting of the
query_cache_type
[485] variable. You should
check the values of these variables as set in your
my.ini
file after configuration
has taken place.
When you set
query_cache_size
[485] to a nonzero value, keep in mind that the query cache
needs a minimum size of about 40KB to allocate its structures. (The exact size depends on system
architecture.) If you set the value too small, you'll get a warning, as in this example:
mysql> SET GLOBAL query_cache_size = 40000;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 1282
Message: Query cache failed to set size 39936;