MySQL Bugs: #15271: Result for VIEWs with ALGORITHM=TEMPTABLE not cached

Bug #15271	Result for VIEWs with ALGORITHM=TEMPTABLE not cached
Submitted:	27 Nov 2005 14:38	Modified:	7 Jul 2017 9:14
Reporter:	Beat Vontobel (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S4 (Feature request)
Version:	5.0.16-standard-log-i686-glibc23	OS:	Linux (Linux 2.4.21 (SuSE Professional))
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
In a VIEW with ALGORITHM=TEMPTABLE the result of the underlying VIEW query (the temporary VIEW table) would be an excellent (or even necessary) candidate for caching. The manual states: "In MySQL 5.0, queries generated by views are cached." However this only seems to apply for VIEWs with ALGORITHM=MERGE or the final result of a query against a view with ALGORITHM=TEMPTABLE. The TEMPTABLE itself seems to be rebuilt on every single query against the VIEW, what makes expensive VIEWs with ALGORITHM=TEMPTABLE almost unusable (that's why I gave it an S3 instead of an S5 for only performance).

How to repeat:
mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 67108864 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
6 rows in set (0.00 sec)

mysql> CREATE TABLE t (i INT);
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE fill_t(e INT) BEGIN WHILE e DO INSERT INTO t VALUES (e); SET e := e - 1; END WHILE; END //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> CALL fill_t(1000000);
Query OK, 1 row affected (38.08 sec)

mysql> CREATE ALGORITHM=TEMPTABLE VIEW v AS SELECT * FROM t WHERE i = 1;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM v;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.20 sec)

mysql> SELECT * FROM v;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> SELECT i FROM v;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.21 sec)

The final result from the first SELECT is cached, as the execution time suggests. The third query however shows that the underlying TEMPTABLE of the VIEW is rebuilt on every query.

Suggested fix:
Cache the TEMPTABLE result of VIEWs.

Sorry, forget to set it to S3.

Thank you for a problem report. 

When I tried to repeat the behaviour you described I've got a crash on latest 5.0.17-BK. Just as described in http://bugs.mysql.com/bug.php?id=15119. I believe, the fix to that bug will help with your problem too. 

Please, wait for the patch to be released, check the caching with it and inform about the results.

Thank you for bugreport! But our Query cache do not cache parts of query. It work with query text and intercepts results on a network layer. So I think I misunderstood you in the forum. I thought that you meant query with tempteble view. Maybe in somepoint we will cache results of "views"/"subqueries in the FROM clause" but it will be some different cache.

> It work with query text

I was aware of this and thought it would be easy to apply the same to the query text of a VIEW...

> and intercepts results on a network layer.

...but this is of course a problem and doesn't make it easy to expand the existent query cache to work with queries inside a VIEW. As you say this will probably be a completely different approach. I hope you can live with it if I keep it as a feature request, as this would greatly improve the usability and performance of expensive VIEWs.

MySQL will no longer invest in the query cache, see:

http://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/