I’ve been thinking about the query cache since last years user conference. One of the features of the query cache is that it’s completely transparent to client. It achieves this by clearing cache entries for tables as soon as the tables are updated. This makes the cache inefficient for tables that are constantly updating.
While thinking about this also thought about slaves and non critical reads. In a replication setup reads that are sent to slaves expect to get data back that might not be the most current. In this situation it doesn’t make sense to expire the cache for every single update because queries running against the slave already know that they are going to get back slightly old data. I thought, “Why not add a time to live to cache entries instead of clearing them for every update?” So, I did.
I added an option to select called sql_cache_ttl. This option instructs the query cache hold the entry in the cache for query_cache_ttl seconds. query_cache_ttl can be set globally or per thread. Initial testing shows that it does help eliminate the cache thrashing that occurs during updates on tables that have many large cache entries.
I have a few more optimizations that I want to add to the patch before sending it into MySQL. The patch below is a preview that works with 5.0.37.