How to Control Query Cache Behavior in MySQL

Galaxy Glossary

How do I enable, disable, and fine-tune query cache behavior in MySQL?

Use SQL_CACHE, SQL_NO_CACHE, and configuration variables to manage how MySQL stores and serves cached query results.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

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.

Why How to Control Query Cache Behavior in MySQL is important

How to Control Query Cache Behavior in MySQL Example Usage


SELECT SQL_CACHE c.id, c.name, COUNT(o.id) AS orders_placed
FROM   Customers c
LEFT JOIN Orders o ON o.customer_id = c.id
GROUP  BY c.id, c.name
ORDER  BY orders_placed DESC;

How to Control Query Cache Behavior in MySQL Syntax


SELECT [SQL_CACHE | SQL_NO_CACHE] select_expr
FROM   table_reference
[WHERE ...]
[GROUP BY ...]
[ORDER BY ...];

SET GLOBAL query_cache_type = 0|1|2;   -- 0=OFF, 1=ON, 2=ON-DEMAND
SET GLOBAL query_cache_size = <bytes>;  -- e.g., 134217728 (128 MB)

RESET QUERY CACHE;                      -- purge all cached results
SHOW STATUS LIKE 'Qcache%';             -- inspect statistics

Common Mistakes

Blindly leaving query_cache_type at 1. It caches every eligible SELECT, causing global mutex waits on busy write-heavy systems. Fix: switch to type 2 and add SQL_CACHE only where useful.
Setting query_cache_size >512 MB. Large caches increase maintenance overhead and lock timeouts. Fix: keep size under 256 MB and monitor Qcache_free_memory.