MySQL Bugs: #33218: SQL_CACHE and SQL_NO_CACHE documentation is confusing

Bug #33218	SQL_CACHE and SQL_NO_CACHE documentation is confusing
Submitted:	13 Dec 2007 15:25	Modified:	20 Dec 2007 14:10
Reporter:	Baron Schwartz (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
From http://dev.mysql.com/doc/refman/5.0/en/select.html:

"SQL_CACHE tells MySQL to store the query result in the query cache if you are using a query_cache_type value of 2 or DEMAND. For a query that uses UNION or subqueries, this option effects any SELECT in the query. See Section 6.5.4, “The MySQL Query Cache”.

SQL_NO_CACHE tells MySQL not to store the query result in the query cache. See Section 6.5.4, “The MySQL Query Cache”. For a query that uses UNION or subqueries, this option effects any SELECT in the query."

The phrases about unions and subqueries is either wrong or misleading.  Queries are cached at the level of an entire query.  Subqueries, and/or parts of unions, are not cached separately.

However, perhaps what the documentation is trying to say is that the hints can appear in any of the query's SELECTs, even in subqueries or unions?  In other words, the following query, as a whole, will not be cached because SQL_NO_CACHE appears in it?

SELECT * FROM (SELECT SQL_NO_CACHE * FROM sakila.film) as X;

If that is the intended meaning, it is unclear :-)

How to repeat:
doc bug

Also, from http://dev.mysql.com/doc/refman/5.0/en/query-cache-in-select.html,

"SQL_CACHE

The query result is cached if the value of the query_cache_type system variable is ON or DEMAND. "

Not quite true.  Only if the query is cacheable.

Thank you for the bug report.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated description:

The SQL_CACHE and SQL_NO_CACHE options affect caching of query
results in the query cache. SQL_CACHE tells MySQL to store the
result in the query cache if it is cacheable and the value of the
query_cache_type system variable is 2 or DEMAND. SQL_NO_CACHE tells
MySQL not to store the result in the query cache. For a query that
uses UNION, subqueries, or views, the following rules apply:

SQL_NO_CACHE applies if it appears in any SELECT in the query.

For a cacheable query, SQL_CACHE applies if it appears in the first
SELECT of the query, or in the first SELECT of a view referred to by
the query.

Any view, or just views that use the MERGE algorithm?  If it uses TEMPTABLE, and the query that refers to the view doesn't say SQL_CACHE, it doesn't seem like it will be cached, since the view will be processed as though it's a derived table.

"For a cacheable query, SQL_CACHE applies if it appears in the first
SELECT of the query, or in the first SELECT of a view referred to by
the query."

That is, if the query is not cacheable, SQL_CACHE doesn't apply.

I did a little experiment and the results surprised me:

set session query_cache_type=demand;
reset query cache;
flush status;
create view v1 as select sql_cache film_id, count(*) as cnt from sakila.film group by film_id;

mysql> show status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        | 
| Qcache_free_memory      | 16759744 | 
| Qcache_hits             | 0        | 
| Qcache_inserts          | 0        | 
| Qcache_lowmem_prunes    | 0        | 
| Qcache_not_cached       | 1        | 
| Qcache_queries_in_cache | 0        | 
| Qcache_total_blocks     | 1        | 
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> select * from v1 limit 5;
+---------+-----+
| film_id | cnt |
+---------+-----+
|       1 |   1 | 
|       2 |   1 | 
|       3 |   1 | 
|       4 |   1 | 
|       5 |   1 | 
+---------+-----+
5 rows in set (0.01 sec)

mysql> show status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        | 
| Qcache_free_memory      | 16757696 | 
| Qcache_hits             | 0        | 
| Qcache_inserts          | 1        | 
| Qcache_lowmem_prunes    | 0        | 
| Qcache_not_cached       | 2        | 
| Qcache_queries_in_cache | 1        | 
| Qcache_total_blocks     | 5        | 
+-------------------------+----------+
8 rows in set (0.00 sec)

I didn't think that would be cached, but it was!  So please disregard my last comment.  I thought that a view that uses TEMPTABLE wouldn't be cached because the temp table is built in a separate step.