The MySQL query cache dies on the first write to the table

MySQL 5.1 caches the complete result of a SELECT, keyed on the exact query text, and it is on by default. The part that is easy to read past is the invalidation rule: one write to a table discards every cached entry that mentions that table, whether or not the rows that changed had anything to do with the cached result.

SHOW STATUS LIKE 'Qcache%';

-- Qcache_hits            184223
-- Qcache_inserts         903117   <- five stored for every one served
-- Qcache_lowmem_prunes    41180

SELECT SQL_NO_CACHE COUNT(*) FROM order_lines WHERE status = 'open';

Comparing Qcache_inserts against Qcache_hits is most of the diagnosis. If inserts dwarf hits, the server is spending time storing results it will never serve, and the invalidation takes a lock that every other query waits behind. On a table written to on every page view the cache is a net loss. Setting query_cache_type = 2 and opting in per query with SQL_CACHE is the middle ground I have settled on — the reference tables get cached, the busy ones stay out of it.