MySQL query cache with time based expiration 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).
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:


Disable the Query Cache (Why query_cache_type is disabled by default start from MySQL 5.6?)
Make the InnoDB Buffer Pool Resistant to Scans


Increasing innodb_old_blocks_pct
Increasing innodb_old_blocks_time



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