Let's say I have a query that I want to be cached, and only that query, any other query is not relevant.
Since my database receives a lot of traffic, sooner or later my caching space would run out, and that cached query would get invalidated. Can I set up some sort of mechanism (in MySQL only, no external tools) that enables caching only on that query and lets say, every night it updates the cached results?
There are two types of "caching" in MySQL
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
