How to Set Query Cache in MySQL: Syntax and Examples

To set query cache in MySQL, use the query_cache_size system variable to define the cache size and query_cache_type to enable or disable caching. You can set these variables dynamically with SET GLOBAL or permanently in the MySQL configuration file my.cnf.

Syntax

MySQL query cache is controlled mainly by two variables:

• query_cache_size: Sets the amount of memory allocated for caching query results.
• query_cache_type: Controls whether query caching is enabled and how it behaves.

You can set these variables dynamically using:

SET GLOBAL query_cache_size = size_in_bytes;
SET GLOBAL query_cache_type = {0 | 1 | 2};

Where query_cache_type values mean:

• 0: Disabled
• 1: Enabled for all queries except those with SQL_NO_CACHE
• 2: Only cache queries with SQL_CACHE hint

To make changes permanent, add these lines to my.cnf under the [mysqld] section.

sql

SET GLOBAL query_cache_size = 1048576;
SET GLOBAL query_cache_type = 1;

Example

This example shows how to enable query cache with 1MB size and verify the settings:

sql

SHOW VARIABLES LIKE 'query_cache_size';
SHOW VARIABLES LIKE 'query_cache_type';

SET GLOBAL query_cache_size = 1048576;
SET GLOBAL query_cache_type = 1;

SHOW VARIABLES LIKE 'query_cache_size';
SHOW VARIABLES LIKE 'query_cache_type';

Output

+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| query_cache_size  | 0       |
+-------------------+---------+
1 row in set (0.00 sec)

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| query_cache_type  | OFF   |
+-------------------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)

+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| query_cache_size  | 1048576 |
+-------------------+---------+
1 row in set (0.00 sec)

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| query_cache_type  | ON    |
+-------------------+-------+
1 row in set (0.00 sec)

Common Pitfalls

Common mistakes when setting query cache include:

• Setting query_cache_size to 0 disables caching.
• Not restarting MySQL or not using SET GLOBAL for dynamic changes.
• Using query cache on tables with frequent writes can cause overhead and reduce performance.
• Forgetting that query cache is deprecated and removed in MySQL 8.0 and later.

Always check your MySQL version before using query cache.

sql

/* Wrong: Setting cache size to 0 disables cache */
SET GLOBAL query_cache_size = 0;

/* Right: Set a positive size to enable cache */
SET GLOBAL query_cache_size = 1048576;

Quick Reference

Variable	Description	Values
query_cache_size	Memory size for query cache	Bytes (e.g., 1048576 for 1MB)
query_cache_type	Enable or disable query cache	0 = OFF, 1 = ON, 2 = DEMAND (only with SQL_CACHE)
query_cache_limit	Maximum size of individual query result to cache	Bytes (default 1048576)

Key Takeaways

Enable query cache by setting query_cache_size to a positive value and query_cache_type to 1 or 2.

Use SET GLOBAL to change cache settings dynamically or edit my.cnf for permanent changes.

Query cache is best for mostly read-only tables; frequent writes can reduce performance.

Query cache is removed in MySQL 8.0, so check your version before using it.

Use SQL_CACHE and SQL_NO_CACHE hints to control caching per query when query_cache_type is DEMAND.