Senior LevelSQL & MySQL
How do you diagnose and fix sudden Query Plan Regressions using Optimizer Hints and Plan Baselines?
❓ Interview Prompt: Explain Query Plan Regression, sudden optimizer execution plan degradation, MySQL Optimizer Hints (`/*+ MAX_EXECUTION_TIME() */`), and SQL Plan Management.
A Query Plan Regression occurs when the query optimizer suddenly switches a fast query to a catastrophic execution plan (e.g. dropping a B-Tree index scan in favor of a full table scan due to stale statistics):
Diagnosis & Immediate Fix:
1
Diagnose: Run 'EXPLAIN' to confirm index dropping or bad join order.
2
Immediate Patch: Apply MySQL 8 Optimizer Hints ('/+ INDEX(table idx_name) /' or '/+ JOIN_ORDER(t1, t2) /') to override optimizer choices without altering application logic.
Code Implementation Example
sql
-- Applying inline MySQL 8 Optimizer Hint to force specific B-Tree index usage
SELECT /*+ INDEX(users idx_users_status_created) */ id, email
FROM users
WHERE status = 'ACTIVE'
ORDER BY created_at DESC;💡Patching query plan regressions using inline MySQL 8 Optimizer Hints.
🎯 Interview Key Takeaway
Use inline Optimizer Hints to fix sudden query plan regressions quickly while updating underlying table statistics.
#MySQL 8#Optimizer Hints#Plan Regression#Performance#Senior
Back to all SQL & MySQL Questions→