MySQL Query Cache is a performance feature that stores the results of SELECT statements in memory for quick retrieval, eliminating the need to re-execute identical queries. While deprecated in MySQL 8.0, Query Cache remains relevant for earlier versions and can dramatically improve response times for read-heavy applications. In this guide, you’ll learn how to configure, optimize, and troubleshoot MySQL Query Cache on Ubuntu 24, along with best practices to maximize its effectiveness while avoiding common performance traps.

How MySQL Query Cache Works

The Query Cache operates by storing complete result sets of SELECT statements in a dedicated memory region. When a query executes, MySQL first checks if an identical query exists in the cache. If found, it returns the cached result immediately, bypassing query parsing, optimization, and execution phases.

The cache uses a hash-based lookup system where the query string, database name, protocol version, and character set form the cache key. This means even minor differences like extra spaces or case variations create separate cache entries. The cache invalidates automatically when any table referenced in a cached query gets modified through INSERT, UPDATE, DELETE, or DDL operations.

Query Cache effectiveness depends heavily on workload characteristics. Read-heavy applications with repetitive queries see substantial benefits, while write-intensive systems may experience degraded performance due to frequent cache invalidations and locking overhead.

Step-by-Step Query Cache Setup on Ubuntu 24

First, verify your MySQL version and current Query Cache status. Query Cache is only available in MySQL 5.7 and earlier versions:

mysql -u root -p -e "SELECT VERSION();"
mysql -u root -p -e "SHOW VARIABLES LIKE 'query_cache%';"

Install or verify MySQL 5.7 installation on Ubuntu 24:

sudo apt update
sudo apt install mysql-server-5.7
sudo systemctl start mysql
sudo systemctl enable mysql

Edit the MySQL configuration file to enable Query Cache:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add or modify these Query Cache parameters in the [mysqld] section:

[mysqld]
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M
query_cache_min_res_unit = 4096

Restart MySQL to apply changes:

sudo systemctl restart mysql

Verify Query Cache activation:

mysql -u root -p -e "SHOW VARIABLES LIKE 'query_cache%';"
mysql -u root -p -e "SHOW STATUS LIKE 'Qcache%';"

Query Cache Configuration Parameters

Understanding each configuration parameter helps optimize Query Cache for your specific workload:

Parameter	Purpose	Recommended Values	Impact
query_cache_type	Controls cache behavior	0 (OFF), 1 (ON), 2 (DEMAND)	0 disables completely, 1 caches all SELECT queries, 2 only caches queries with SQL_CACHE hint
query_cache_size	Memory allocated to cache	64M – 512M	Too small reduces effectiveness, too large wastes memory and increases management overhead
query_cache_limit	Maximum size of individual cached results	1M – 4M	Prevents large result sets from consuming excessive cache space
query_cache_min_res_unit	Minimum memory block size	2048 – 8192	Smaller values reduce memory waste for small results, larger values reduce fragmentation

Dynamic configuration changes without restart:

mysql -u root -p

SET GLOBAL query_cache_size = 256*1024*1024;
SET GLOBAL query_cache_limit = 4*1024*1024;
SET GLOBAL query_cache_type = 1;

Monitoring and Performance Analysis

Monitor Query Cache effectiveness using built-in status variables:

mysql -u root -p -e "SHOW STATUS LIKE 'Qcache%';"

Key metrics to track:

• Qcache_hits: Number of queries served from cache
• Qcache_inserts: Number of queries added to cache
• Qcache_not_cached: Queries not cached (non-SELECT, too large, etc.)
• Qcache_lowmem_prunes: Cache entries removed due to memory pressure
• Qcache_free_memory: Available cache memory
• Qcache_free_blocks: Free memory blocks (fragmentation indicator)

Calculate Query Cache hit ratio:

mysql -u root -p -e "
SELECT 
  ROUND(Qcache_hits / (Qcache_hits + Com_select) * 100, 2) AS 'Query Cache Hit Ratio %',
  Qcache_hits AS 'Cache Hits',
  Com_select AS 'Total SELECT Queries'
FROM 
  (SELECT VARIABLE_VALUE AS Qcache_hits FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Qcache_hits') AS hits,
  (SELECT VARIABLE_VALUE AS Com_select FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Com_select') AS selects;
"

Create a monitoring script for continuous tracking:

#!/bin/bash
# query_cache_monitor.sh

while true; do
  echo "=== Query Cache Stats $(date) ==="
  mysql -u root -p[password] -e "
  SELECT 
    VARIABLE_NAME, 
    VARIABLE_VALUE 
  FROM information_schema.GLOBAL_STATUS 
  WHERE VARIABLE_NAME IN ('Qcache_hits', 'Qcache_inserts', 'Qcache_not_cached', 'Qcache_lowmem_prunes');"
  
  sleep 60
done

Real-World Use Cases and Examples

Query Cache excels in specific scenarios. Here are practical examples:

E-commerce Product Catalog:

-- Frequently cached queries
SELECT name, price, description FROM products WHERE category_id = 5 AND status = 'active';
SELECT COUNT(*) FROM products WHERE category_id = 5;

-- Cache invalidation triggers
UPDATE products SET price = 29.99 WHERE id = 1001;  -- Invalidates all cached queries referencing products table

Content Management System:

-- Repetitive queries that benefit from caching
SELECT title, content, author FROM articles WHERE published = 1 ORDER BY created_at DESC LIMIT 10;
SELECT COUNT(*) FROM comments WHERE article_id = 123 AND approved = 1;

-- Selective caching with SQL_CACHE hint (when query_cache_type = 2)
SELECT SQL_CACHE * FROM static_pages WHERE slug = 'about-us';

Performance comparison showing Query Cache impact:

Scenario	Without Query Cache	With Query Cache	Improvement
Simple SELECT (10,000 executions)	2.5 seconds	0.3 seconds	8.3x faster
Complex JOIN (1,000 executions)	15.2 seconds	1.8 seconds	8.4x faster
COUNT query (5,000 executions)	4.1 seconds	0.2 seconds	20.5x faster

Best Practices and Optimization Tips

Maximize Query Cache effectiveness with these proven strategies:

• Size cache appropriately: Start with 64-128MB and adjust based on hit ratio and memory pressure
• Monitor fragmentation: High Qcache_free_blocks indicates fragmentation; consider smaller query_cache_min_res_unit
• Use consistent query formatting: Identical queries must match exactly, including whitespace and case
• Implement query standardization: Use prepared statements or query builders to ensure consistent formatting
• Consider selective caching: Use query_cache_type = 2 with SQL_CACHE hints for fine-grained control

Query Cache works best when:

• Read-to-write ratio exceeds 3:1
• Queries are frequently repeated with identical parameters
• Result sets are small to medium-sized (under query_cache_limit)
• Tables are relatively stable with infrequent updates

Disable Query Cache using SQL_NO_CACHE for specific queries:

SELECT SQL_NO_CACHE * FROM real_time_data WHERE timestamp > NOW() - INTERVAL 1 MINUTE;

Common Issues and Troubleshooting

Low Hit Ratio: If hit ratio remains below 20%, investigate these causes:

• Queries use non-deterministic functions like NOW(), RAND(), or USER()
• Frequent table updates invalidate cache entries
• Query variations prevent cache reuse
• Result sets exceed query_cache_limit

Debug cache misses with query analysis:

-- Enable general log to analyze query patterns
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/query_analysis.log';

-- Review queries that cannot be cached
SHOW STATUS LIKE 'Qcache_not_cached';

Memory Pressure Issues: High Qcache_lowmem_prunes indicates insufficient cache size:

-- Check current memory usage
SHOW STATUS WHERE Variable_name IN ('Qcache_free_memory', 'Qcache_queries_in_cache', 'Qcache_total_blocks');

-- Increase cache size if system memory allows
SET GLOBAL query_cache_size = 512*1024*1024;

Fragmentation Problems: Excessive free blocks fragment memory:

-- Flush and reset cache to defragment
FLUSH QUERY CACHE;
RESET QUERY CACHE;

Performance Degradation: Query Cache can hurt performance in write-heavy environments:

-- Monitor invalidation frequency
SHOW STATUS LIKE 'Qcache_invalidations';

-- Consider disabling for write-intensive applications
SET GLOBAL query_cache_type = 0;

Alternatives and Modern Approaches

Since MySQL 8.0 removed Query Cache, consider these alternatives:

Solution	Type	Best For	Complexity
Redis	External cache	Distributed applications, complex caching logic	Medium
Memcached	External cache	Simple key-value caching, high performance	Low
Application-level caching	Code-based	Fine-grained control, business logic integration	High
ProxySQL	Database proxy	Query routing, connection pooling, result caching	Medium

Redis implementation example for MySQL query results:

# Install Redis
sudo apt install redis-server

# Python example with Redis caching
import redis
import mysql.connector
import json
import hashlib

r = redis.Redis(host='localhost', port=6379, db=0)

def cached_query(query, params=None):
    # Create cache key from query and parameters
    cache_key = hashlib.md5(f"{query}{params}".encode()).hexdigest()
    
    # Check cache first
    cached_result = r.get(cache_key)
    if cached_result:
        return json.loads(cached_result)
    
    # Execute query if not cached
    conn = mysql.connector.connect(host='localhost', user='root', password='password', database='mydb')
    cursor = conn.cursor()
    cursor.execute(query, params)
    result = cursor.fetchall()
    
    # Cache result with 300 second TTL
    r.setex(cache_key, 300, json.dumps(result, default=str))
    
    return result

For comprehensive MySQL performance optimization, consult the official MySQL Query Cache documentation and consider implementing modern caching strategies alongside database-level optimizations.

---

---

This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.

This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.