EXPLAIN vs EXPLAIN ANALYZE: Selecting a Low‑Overhead Query Cost Method for MySQL 8.0+

Goal

Determine a reliable way to capture actual query execution cost in MySQL 8.0+ without jeopardizing live traffic.

Constraints

• EXPLAIN returns optimizer estimates only and adds negligible overhead.
• EXPLAIN ANALYZE executes the statement, revealing real timing and row counts, but can increase latency and modify data for DML.
• Performance Schema digests aggregate statistics without per‑execution cost, yet lose plan detail.
• Slow‑query logging captures long queries but misses frequent fast statements.

Open Decision

Whether to restrict EXPLAIN ANALYZE to staging environments or to sample it in production guarded by MAX_EXECUTION_TIME.

Questions:

1. Is a staged‑only EXPLAIN ANALYZE workflow sufficient to validate optimizer estimates for the production workload?
2. Can a low‑percentage sampling strategy with a strict execution‑time limit provide representative actual metrics without measurable impact?
3. What fallback metrics (e.g., Performance Schema digests) should be correlated when EXPLAIN ANALYZE is unavailable?