Using EXPLAIN to Optimize MySQL Queries
Fast recall
Quick revision
Core answer
Tests practical query tuning skill.
Interviewers want to see that the candidate can read an EXPLAIN output, identify bottlenecks, and know the remediation steps rather than just theoretically knowing indexes exist.
Interviewers want to see that the candidate can read an EXPLAIN output, identify bottlenecks, and know the remediation steps rather than just theoretically knowing indexes exist.
Interview-ready guide
Interview answer
When a MySQL query is slow, EXPLAIN is the first tool to use — it shows exactly how MySQL plans to execute the query: which indexes it considers, which it picks, and how many rows it expects to scan.
Reading the key columns
typeis the most important column —ALLmeans a full table scan reading every row;reforeq_refmeans an index lookup;rangemeans an index range scan; ideally you want anything other thanALLon large tableskeyshows which index MySQL actually chose — if it's NULL when you expected an index, the optimizer decided the scan was cheaper, often because statistics are stale or the selectivity is lowrowsis the estimated rows MySQL expects to examine — a mismatch betweenrowsand the actual result count is a signal thatANALYZE TABLEis needed to refresh statistics
EXPLAIN ANALYZE — actual vs estimated
- MySQL 8.0 added
EXPLAIN ANALYZEwhich actually executes the query and shows actual row counts and execution times alongside the estimates — this is the tool that surfaces cases where the planner's cost model is wrong - Look for large gaps between estimated and actual rows — if estimated=100 but actual=50,000, the index choice was based on wrong data; running
ANALYZE TABLEupdates the statistics the optimizer uses
Extra column — what to watch for
Using filesortmeans MySQL couldn't satisfyORDER BYwith the index, which means an additional sort pass runs in memory or on disk — adding the ORDER BY columns to the index eliminates itUsing temporarymeans MySQL created an internal temp table — common with GROUP BY on non-indexed columns; adding an index on the GROUP BY column can eliminate itUsing indexmeans a covering index is being used — all columns needed are in the index and the table itself is not read; the best possible extra value for a read query
Using EXPLAIN to Optimize MySQL Queries
Direct Answer
EXPLAIN shows the query execution plan: which indexes MySQL uses, the JOIN type, estimated rows scanned, and whether a filesort or temporary table is needed. You look for 'ALL' in the type column (full table scan), NULL in the key column (no index used), and high rows estimates — then add appropriate indexes or rewrite the query to fix them.
Deep Dive & Architecture
EXPLAIN shows the query planner's execution plan — the sequence of operations it will perform. EXPLAIN ANALYZE actually runs the query and reports real execution times.
The plan reads bottom-up as a tree of nodes: Sequential Scan, index Scan, Hash JOIN, Nested Loop, Sort.
EXPLAIN shows the query execution plan: which indexes MySQL uses, the JOIN type, estimated rows scanned, and whether a filesort or temporary table is needed. You look for 'ALL' in the type column (full table scan), NULL in the key column (no index used), and high rows estimates — then add appropriate indexes or rewrite the query to fix them.
Running EXPLAIN without ANALYZE and making optimization decisions based on estimated costs rather than actual execution times — estimates can be wildly inaccurate when table statistics are stale.
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
Interviewers want to see that the candidate can read an EXPLAIN output, identify bottlenecks, and know the remediation steps rather than just theoretically knowing indexes exist.
Check your understanding
Answer these without looking back at the article.
- SQL EXPLAIN Plan — Reading and Optimizing Query Execution Plans
- How does MySQL query caching work and what are the modern best practices for query optimization in MySQL 8.0?
Related interview prep categories
2Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.