MySQL Query Cache and Query Optimization
Fast recall
Quick revision
Core answer
Tests whether candidates have current MySQL 8.0 knowledge and can distinguish between database-level and application-level caching strategies.
Shows awareness of why naively relying on MySQL's old query cache was an anti-pattern.
Shows awareness of why naively relying on MySQL's old query cache was an anti-pattern.
Interview-ready guide
Interview answer
MySQL's built-in query cache was removed in MySQL 8.0 — and the reason is worth understanding, because it informs how you should implement caching instead.
Why the query cache was removed
- The MySQL query cache stored complete SELECT result sets and returned them for identical queries — but it used a single global mutex that serialized every cache lookup and invalidated the entire cache entry for a table on any write
- Under concurrent write workloads, this global mutex became a severe bottleneck: threads queued behind it even for reads on tables that hadn't changed, and every write to any row in a table invalidated all cached queries on that table regardless of whether the query touched those rows
- The result: the query cache made concurrent write-heavy applications slower than having no cache at all, which is why it was deprecated in MySQL 5.7 and removed in 8.0
What to use instead
- Application-level caching with Redis or Memcached is the correct replacement — it sits outside the database, doesn't hold the global mutex, and gives you control over invalidation at the granularity that makes sense for your data
- The cache-aside pattern: check Redis first, if not found query MySQL, store the result in Redis with a TTL, return; invalidate the cache key when you write to the underlying table
- MySQL 8.0 query optimization instead: use
EXPLAIN ANALYZE, add covering indexes for hot queries, enable the InnoDB buffer pool to cache data pages in memory (theinnodb_buffer_pool_sizesetting should be 70-80% of available RAM on a dedicated database server)
For Spring Boot, use @Cacheable with a Redis cache manager rather than relying on database-level caching.
MySQL Query Cache and Query Optimization
Direct Answer
MySQL's built-in query cache was removed in MySQL 8.0 because it caused severe mutex contention under concurrent write workloads. Modern query-level caching should be handled at the application layer using Redis or Caffeine in Spring Boot. Query optimization in MySQL 8.0 instead focuses on the improved cost-based optimizer, invisible indexes, descending indexes, and histogram statistics.
Code Example
Application-layer caching in Spring Boot to replace MySQL query cache
// Spring Boot application-layer cache with Caffeine (local) or Redis (distributed)
// 1. Dependency: spring-boot-starter-cache + caffeine (or spring-boot-starter-data-redis)
// build.gradle: implementation 'com.github.ben-manes.caffeine:caffeine'
// 2. Enable caching
@SpringBootApplication
@EnableCaching
public class Application { ... }
// 3. application.properties — Caffeine cache config
// spring.cache.type=caffeine
// spring.cache.caffeine.spec=maximumSize=1000,expireAfterWrite=5m
// 4. Cache query results in the service layer
@Service
public class ProductService {
@Cacheable(value = "products", key = "#categoryId")
@Transactional(readOnly = true)
public List<Product> getProductsByCategory(Long categoryId) {
// Only hits the DB on first call; returns cached value afterwards
return productRepository.findByCategoryId(categoryId);
}
@CacheEvict(value = "products", key = "#product.categoryId")
@Transactional
public Product saveProduct(Product product) {
// Evicts the cache when data changes — solves the stale data problem
return productRepository.save(product);
}
}
// MySQL 8.0 optimizer improvements (no application code needed):
-- Invisible indexes: test index removal without actually dropping
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;
-- Now query the table; if performance is acceptable, drop the index
ALTER TABLE orders DROP INDEX idx_status;
-- Histogram statistics: tell optimizer about data distribution
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 10 BUCKETS;
-- Optimizer now knows status='PENDING' has 60% of rows vs. 'DONE' 5%
-- and can make better join/filter ordering decisions
-- Descending index: speeds up ORDER BY col DESC queries
CREATE INDEX idx_created_desc ON orders(created_at DESC);
Common Mistakes & Pitfalls
Shows awareness of why naively relying on MySQL's old query cache was an anti-pattern.
Check your understanding
Answer these without looking back at the article.
- How would you decide between Caffeine and Redis for Spring Boot application-level caching?
- What is a histogram in MySQL 8.0 and how does it improve query plan selection?
- How would you implement cache-aside pattern in Spring Boot with Redis to handle cache stampede?
Related interview prep categories
2Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.