Optimize Your Queries for the Query Cache in MySQL

The MySQL Query Cache was designed to improve application performance by storing the results of previously executed SELECT queries. When an identical query was received and the underlying data had not changed, MySQL could return the cached result instead of executing the query again.

This could reduce repeated query processing for suitable workloads. However, Query Cache was deprecated in MySQL 5.7.20 and completely removed in MySQL 8.0, so these techniques are relevant only to older MySQL versions.

How the Query Cache Worked

For supported MySQL versions, the Query Cache stored the text of a query together with its result.

When the same query was submitted again, MySQL could check the cache and return the stored result.

A simplified flow was:

Application
    ↓
SELECT Query
    ↓
Query Cache
   ↙     ↘
Hit      Miss
 ↓         ↓
Result   Database

A cache hit avoided executing the query again.

Query Text Mattered

The Query Cache matched queries based on their exact query text and other characteristics.

For example, these two queries could be treated as different:

SELECT id, name FROM users WHERE status = 'active';

SELECT id,name FROM users WHERE status='active';

Differences in the query text could therefore reduce cache effectiveness.

For applications using the Query Cache, consistently generated SQL was important.

Avoid Unnecessary Differences

Applications should avoid generating multiple syntactically different versions of the same logical query when they expect Query Cache reuse.

For example, keeping query formatting and construction consistent could increase the chance of matching an existing cached entry.

The exact behavior depended on the MySQL version and query characteristics.

Use Appropriate SELECT Queries

The Query Cache was intended for suitable SELECT statements.

Queries involving frequently changing data could provide limited benefit because cached results needed to be invalidated when relevant tables changed.

For example, a table receiving constant updates may cause frequent invalidation and reduce the effectiveness of the cache.

Keep Queries Consistent

Applications should use consistent query patterns.

For example:

SELECT id, name
FROM products
WHERE category_id = 10;

Using the same SQL structure consistently was more suitable for Query Cache reuse than repeatedly generating different forms of the same request.

Avoid Excessively Large Query Results

Large cached result sets could consume significant cache memory.

The amount of data returned by a query should therefore be appropriate for the application's requirements.

Pagination can also reduce the amount of data retrieved at once:

SELECT id, name
FROM products
ORDER BY id
LIMIT 20;

Query Cache and Dynamic Data

One of the major limitations of Query Cache was invalidation.

When a table changed, cached results depending on that table could be invalidated. This meant that workloads with frequent INSERT, UPDATE, or DELETE operations might see limited benefit.

Query Cache was therefore more appropriate for workloads with many repeated reads and relatively infrequent data changes.

Check Query Cache Statistics

In older MySQL versions with Query Cache enabled, administrators could review cache-related status information:

SHOW STATUS LIKE 'Qcache%';

These statistics could help determine whether the cache was receiving useful hits or experiencing significant invalidation.

Important: Query Cache Is No Longer Available

Query Cache was deprecated in MySQL 5.7.20 and removed in MySQL 8.0. Modern MySQL applications cannot use query_cache_size, query_cache_type, or other Query Cache settings.

For current systems, query optimization should use other mechanisms.

Modern Alternatives to Query Cache

Modern MySQL applications should focus on:

Proper indexing: Create indexes that support frequently executed queries.

Query optimization: Use EXPLAIN and performance monitoring to improve expensive SQL.

InnoDB Buffer Pool: Keep frequently accessed data and indexes in memory where appropriate.

Application-level caching: Technologies such as Redis can cache frequently requested application data.

CDNs and HTTP caching: Cache suitable static or public content closer to users.

Conclusion

Optimizing queries for the Query Cache was once a useful MySQL performance technique, particularly for applications with repeated read-heavy workloads.

However, because MySQL Query Cache was removed in MySQL 8.0, it should not be used as a recommendation for modern database deployments.

For current MySQL applications, focus on efficient SQL, appropriate indexes, InnoDB memory configuration, database monitoring, and application-level caching to achieve better performance.