There are two types of "caching" in MySQL
- the basic caching of data and index blocks
- the "Query cache".
Both have configurable limits.
"Cache" implies that a finite amount of space is reserved. When you "run out" of the space, something is bumped out of cache to make room for the new item. The one bumped out is usually the "least recently used", which, without other info, is the least likely to be needed again soon.
So, I don't see what the problem is.
On the other hand, maybe you are asking a different question... With the "Query cache",
SELECTs are saved in it. This cache keeps the queries and their result sets. (Again, when it fills up, old entries are bumped out to make room for new ones.) A major drawback of the QC is that when any write occurs, all entries in the QC for that table are purged. So, for tables that are frequently written to, the QC is virtually useless.
For the few systems that can effectively use the QC, here are my recommendations:
query_cache_type = DEMAND
query_cache_size = 50M -- no more than this, else purging is too costly
SELECT SQL_CACHE ... -- on queries that can benefit from the QC
SELECT SQL_NO_CACHE ... -- for the rest