Query Cache

Long ago, Sun Microsystems' Japanese site had a section called "OLTP Technical Resources." Tuning tips and performance characteristics for Oracle on Sun hardware, written out in detail. The premise was always enterprise machines no individual could afford — hundreds of thousands of dollars in hardware, used as lavishly as the budget allowed. Page after page of it.

I was an engineer just starting out, and I read it every day. What I had was a secondhand PC server. The world it described was completely out of reach, but I kept reading anyway. This was when the internet was still "a thing you connected to."

That was where I learned that for OLTP databases, cache tuning is what decides performance. In Oracle, that meant the buffer cache in the SGA — the DB_BLOCK_BUFFERS value you wrote into INIT.ORA. The articles argued over block sizes and buffer counts to lift the hit rate by one percent. That was the world. Cache is where speed comes from. That stuck.

So I had dreams about MySQL's query cache too.

The query cache returned previous results when it saw the same SELECT statement. Identical query string, no parsing, no execution, straight from cache. Fast. But insert a single row into a table and every cache entry for that table was gone. Write-heavy services never kept a warm cache. The overhead of managing it was all that remained.

Small services loved it. Mostly reads, few writes. The same pages visited over and over. Perfect conditions. You set query_cache_size in my.cnf and response times got noticeably faster. For a while it was the first tuning tip anyone taught.

The problem was scale. In the multicore era, the query cache was protected by a global mutex. More cores meant more lock contention. While one thread read from cache, others waited. A mechanism built for speed had become its own drag.

By MySQL 5.6 the best practice was query_cache_type=0. The official documentation recommended disabling it. When 8.0 removed it entirely, it was just clearing away something already dead.

Now the standard is caching in the application layer. Redis, Memcached. Key-level invalidation. The responsibility is yours.

I miss the days when one line in my.cnf made everything faster. No. That was an illusion.