How to Use Query Cache in MySQL for Faster Queries

In MySQL, you can use the query_cache feature to store the results of SELECT queries and reuse them for identical queries, improving performance. Enable it by setting query_cache_type to ON and configuring query_cache_size. Use SQL_CACHE in your SELECT statements to explicitly cache results.

Syntax

The query cache in MySQL is controlled by system variables and optional query hints.

• query_cache_type: Enables or disables query caching globally. Values: ON, OFF, or DEMAND.
• query_cache_size: Sets the amount of memory allocated for caching query results.
• SQL_CACHE: Hint in SELECT statements to cache the result when query_cache_type is DEMAND.
• SQL_NO_CACHE: Hint to skip caching for a specific query.

sql

SET GLOBAL query_cache_type = ON;
SET GLOBAL query_cache_size = 1048576; -- 1MB cache size

-- Example of caching a query explicitly
SELECT SQL_CACHE * FROM employees WHERE department = 'Sales';

Example

This example shows how to enable query cache, set its size, and run a query that caches its result. The second time you run the same query, MySQL returns the cached result, speeding up response.

sql

SHOW VARIABLES LIKE 'query_cache%';

SET GLOBAL query_cache_type = ON;
SET GLOBAL query_cache_size = 1048576; -- 1MB

-- First run: caches the result
SELECT SQL_CACHE * FROM employees WHERE department = 'Sales';

-- Second run: uses cached result
SELECT SQL_CACHE * FROM employees WHERE department = 'Sales';

Output

+----------------------+---------+
| Variable_name         | Value   |
+----------------------+---------+
| query_cache_limit     | 1048576 |
| query_cache_size      | 1048576 |
| query_cache_type      | ON      |
| query_cache_wlock_invalidate | OFF |
+----------------------+---------+

-- Query results from employees table (example output omitted for brevity)

Common Pitfalls

Common mistakes when using query cache include:

• Not enabling query_cache_type or setting query_cache_size to 0 disables caching.
• Using SQL_NO_CACHE unintentionally prevents caching.
• Query cache does not cache queries with non-deterministic functions like NOW() or queries that modify data.
• Frequent table updates invalidate cached queries, reducing cache effectiveness.
• Query cache is deprecated in MySQL 8.0 and removed, so it is only available in MySQL 5.7 and earlier.

sql

/* Wrong: cache disabled by default or size zero */
SET GLOBAL query_cache_type = OFF;
SET GLOBAL query_cache_size = 0;

/* Right: enable cache and allocate memory */
SET GLOBAL query_cache_type = ON;
SET GLOBAL query_cache_size = 1048576;

Quick Reference

Setting	Description	Example Value
query_cache_type	Controls if query cache is enabled	ON, OFF, DEMAND
query_cache_size	Memory size for cache in bytes	1048576 (1MB)
SQL_CACHE	Hint to cache a SELECT query result	SELECT SQL_CACHE * FROM table;
SQL_NO_CACHE	Hint to skip caching a query	SELECT SQL_NO_CACHE * FROM table;

Key Takeaways

Enable query cache by setting query_cache_type to ON and allocating memory with query_cache_size.

Use SQL_CACHE hint to explicitly cache SELECT query results when query_cache_type is DEMAND.

Query cache is invalidated on table changes, so it works best for mostly read-only data.

Query cache is deprecated in MySQL 8.0; consider other caching strategies for newer versions.

Avoid caching queries with non-deterministic functions or frequent updates for best performance.