MySQL Bugs: #26513: Add config options to exclude specific dbs/tables from query cache

Bug #26513	Add config options to exclude specific dbs/tables from query cache
Submitted:	21 Feb 2007 2:13	Modified:	7 Jul 2017 9:13
Reporter:	Arjen Lentz	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S4 (Feature request)
Version:	any	OS:	Any (any)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	query cache

Description:
It would be useful to have a configurable list of databases and tables to exclude from being cached by the query cache. This would maintain the overall simplicity of the query cache, but deal with the fact that
  a) some tables have an access pattern such that the query cache is a hindrance;
  b) apps cannot always be changed to include SQL_NO_CACHE. They may be
     third-party closed-source apps, or it may be an ORM generated query.

DBAs require this level of tuning. Completely disabling the query cache would have a detremental effect on other queries.

How to repeat:
Feature req.

Thank you for a reasonable feature request.

That's a real coincidence! 

I wanted to propose another but related improvement. 

Right now, you have sort-of statement level control over the query cache, but only if you're willing (and able!) to modify the sql statements (and adding the cache modifiers in the statement text). 

It would be much nicer to be able to operate on the query cache on the server level and instruct the query cache to cache or ignore queries at runtime. Something like:

ENABLE [QUERY] CACHE FOR {<statement>}

DISABLE [QUERY] CACHE FOR {<statement>}
(the { and } are delimiters so you have good control over the literal text that is to be recognized as SQL statement text)

For example, this would allow one to benchmark the effect of caching particular queries without touching the application code (to add the cache modifiers) 

It would also be extremely useful to see which entries are in the cache, and how often they are hit

just noticed 18519 which asks to see query cache entries too

Looking at http://bugs.mysql.com/18519, the above suggestion could be extended to have explicit inclusions as well as exclusions, but all per table. This would maintain the simplicity of the query cache while providing the necessary control.

If this seems reasonable, 18519 could be marked as a dup.

Thank you for a valid feature request.

Perhaps we can regard the mysql query cache as an obsolete concept, given that memcached is so widespread now and more flexible. in which case it can be scrapped.

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

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