How to Control Query Cache Behavior in MySQL

Why does MySQL have a Query Cache?

Query cache stores complete result sets in memory so identical SELECT statements can be answered instantly without re-execution. It reduces latency on read-heavy workloads but can hurt write performance because each table change invalidates related cache entries.

When should I add SQL_CACHE or SQL_NO_CACHE?

Add SQL_CACHE when a SELECT runs often and underlying tables change rarely.Use SQL_NO_CACHE for volatile tables or ad-hoc reporting queries where caching offers little benefit and only wastes memory.

Example decision

A nightly sales dashboard query over Orders and OrderItems benefits from SQL_CACHE; a real-time order confirmation query should include SQL_NO_CACHE.

How do I configure query cache globally?

Set variables in my.cnf or at runtime: query_cache_type = 0|1|2, query_cache_size = bytes.Type 1 caches all eligible SELECTs, 2 caches only statements tagged with SQL_CACHE, and 0 disables the feature.

How do I check current cache statistics?

Run SHOW STATUS LIKE 'Qcache%'; to view hits, inserts, invalidations, and free blocks.High insertion but low hit ratios indicate poor cache usage.

How can I clear or warm the cache?

Execute RESET QUERY CACHE; to purge all entries, or run frequently used SELECTs after a restart to warm the cache and avoid first-user latency.

Practical example: can caching speed up product sales summary?

Yes. The following query is CPU-heavy yet stable throughout the day.Caching halves response time for repeated access.

SELECT SQL_CACHE p.id, p.name,
SUM(oi.quantity) AS total_sold
FROM Products p
JOIN OrderItems oi ON oi.product_id = p.id
GROUP BY p.id, p.name;


What are best practices for query cache tuning?

Keep query_cache_size modest (128–256 MB) to avoid locking overhead. Use type 2 and tag only truly repeatable queries. Monitor Qcache_free_memory and hit ratio weekly.

What mistakes cause slowdowns?

Updating large tables without SQL_NO_CACHE may invalidate thousands of entries, creating lock contention.Over-allocating cache memory can also trigger frequent global mutex waits.

FAQ

Does InnoDB buffer pool replace the query cache?

No. The buffer pool caches pages; the query cache stores complete result sets. Both can coexist but serve different layers.

Is the query cache enabled in MySQL 8?

No. The feature was removed; use application-level caching or ProxySQL instead.

.