The database we use has a read and write intensive load. Some read queries are heavy.
Unfortunately the Query Cache cannot be used as it is invalidated too quickly.
We wanted to avoid the classic Memcached/MySQL couple to simplify the architecture and decrease connections/latency.
Is there a strategy to add a "time based cache" in MySQL? (ideally per collection)
EDIT:
- Each query group has a different TTL (from 5 seconds to several days).
Best Answer
I'll assume your database is all InnoDB when answering this question.
What you are asking for is not possible for the Query Cache. The mechanisms you have in mind do exist with the InnoDB Buffer Pool.
What you should try is the following:
This controls what and how long blocks of data and index are to stay inside the Buffer Pool
To release blocks after a while, you would change innodb_old_blocks_pct back to 37