In this lesson, you will learn how to lock query execution plans in MySQL and MariaDB to prevent performance regressions after major software deployments or configuration changes.
Why Plans Drift
Even when your code remains identical, the database optimizer might choose a different execution plan after a software update or a statistic refresh. A minor change in table distribution or a slight adjustment to the optimizer’s cost model can cause the engine to switch from a high-performance Index Range Scan to a slow Full Table Scan. In a production environment, this "plan drift" can lead to unexpected outages.
Locking the Plan
To achieve plan stability, we need to instruct the optimizer to ignore its dynamic cost estimation and stick to a proven, known-good execution path. While we previously discussed FORCE INDEX to nudge the optimizer, formal "Plan Stability" (often referred to as Optimizer Hints or Plan Management) allows us to pin a specific execution strategy.
In modern MySQL, we utilize Optimizer Hints to guarantee that the path remains constant across deployments. By embedding hints directly into the query, you effectively bypass the volatility of the optimizer's cost-based decisions:
SELECT /*+ INDEX(employees idx_dept_id) */ name, salary
FROM employees
WHERE dept_id = 5;
By explicitly specifying the index to use, you ensure that even if the optimizer decides that a Full Table Scan is "cheaper" due to updated statistics, the database will respect your hint.
Managing Stability at Scale
When you cannot modify the application source code to add hints, you can utilize Stored Outlines or Optimizer Hint Injection via proxy layers (like ProxySQL, which you learned earlier). By intercepting the incoming SQL and transparently rewriting it to include the required hints, you can freeze query behavior without touching the application’s codebase. This creates a safety net: your deployment changes might update the software, but the execution plans remain locked to the patterns that you have already verified as optimal.
Key Takeaway: Query plan stability is achieved by shifting from trust in the dynamic cost-based optimizer to explicit directive-based execution, ensuring that proven query patterns persist even after environmental changes.