MySQL Bugs: #18519: Extend query cache functionality to determine if a given statement is cached

Bug #18519	Extend query cache functionality to determine if a given statement is cached
Submitted:	26 Mar 2006 13:23	Modified:	7 Jul 2017 9:14
Reporter:	Issac Goldstand	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	query cache

Description:
Hi there,
  I was wondering if it would be possible to leverage the MySQL query cache to allow one to query whether the results of a given query are still valid.  This could be used to further cache results in the client; as the client knows that the query's results are unchanged, it (the client) might be able to further optimize performace by using a cached result of the output from the processed query resultset.

An easy example of this would be for web applications, where it would be possible to further optimize HTML generation of certain pages by caching the entire HTML entity; if the query cache confirms that the resultset is unchanged, the server could return the cached HTML rather than regenerate the HTML with the query results.  

This could be equally useful for any 1:1 transformation of query results.

How to repeat:
N/A

Suggested fix:
The implementation I forsee would be similar to the SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() implementation where an optional tag can be added to the select statement to return whether a valid cahced result exists or not, and more importantly a function which will return whether the last executed statement was cached or not.

The biggest issue in this request that I've thought of is how to validate that although the query cache already contain the results of a query, the results indeed match what the client is expecting.  I've thought of two solutions.

1) Connection based.  Each time a query returns a cached result for a given connection, the server would mark that connection as cached.  From that point, until the underlying tables were changed in such a manner that the query cache must refresh the resultset, each time that connection ID requested the resultset, it could be informed that the resultset has not been changed.  Once the underlying tables were changed, all connections would have this flag unset, and would only be able to see this as "cached" after requesting it for the first time.  

2) Digest based.  Each result in the query cache could be marked with a digest (similar to an E-Tag from the HTTP standard).  Clients could then request the E-Tag of the last returned resultset (similar to SELECT FOUND_ROWS()) and in future queries, embed the returned digest as part of the query (eg, SELECT SQL_CACHE SQL_VERIFY_CACHED_RESULT='digest' * FROM `table`;)

Thank you for a reasonable feature request.

This looks too complicated/granular to be practical. Remember, the query cache is effective because of its simplicity.
The essense of this feature request could be handled through http://bugs.mysql.com/26513 if it were to cover explicit inclusions as well as exclusions of tables. I will add a comment about that now.

Bug 26513 seems to be asking to control the granularity of the query cache by allowing/preventing certain statements from entering the query cache.  This bug is asking to be able to simply establish whether a given query generates a query cache hit or not.  Perhaps I'm missing something, but I don't understand where they overlap.

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

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