The MySQL optimizer rewrites and re-optimizes SQL statements at runtime. For most queries, this produces efficient execution plans. However, in production environments, stale statistics, schema changes, or index additions can cause the optimizer to silently switch to a slower plan, degrading query performance.
PolarDB for MySQL provides two features to lock in a known-good execution plan and prevent performance regressions: statement outline and auto plan cache. Both operate at the database level and require no application code changes.
Key benefits:
Lock a SQL statement to a specific execution plan so optimizer changes do not affect it.
Stabilize execution plans without modifying application SQL or redeploying services.
Apply plan controls to specific statements while letting the optimizer run freely on others.
Choose the right feature:
Use statement outline to steer the optimizer toward a specific plan by injecting SQL hints at parse time. Best for preventing index regressions and locking plans after performance tuning.
Use auto plan cache to cache and reuse execution plans for repeated identical SQL statements, reducing repeated parse and optimization overhead. Best for high-frequency transactional queries.