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

Answer the Core Questions First

1. Staging‑only EXPLAIN ANALYZE is sufficient for most workloads if the staging data set and connection settings match production. It will validate optimizer estimates for typical data distributions but may miss production‑specific skew, caching effects, or concurrent load. Use it as a baseline and then spot‑check critical queries on production.
2. Low‑percentage sampling with MAX_EXECUTION_TIME can provide representative metrics with negligible impact when the sample rate is 1–5 % and the limit is set to a value that aborts the statement before it hurts latency (e.g., 200–500 ms). The abort stops further work, but any partial updates before the abort may still have side effects; wrap DML in a transaction or use a rollback‑able engine.
3. Fallback metrics are the Performance Schema digest tables (events_statements_summary_by_digest). Correlate their avg_timer_wait and sum_rows_examined with the actual times from sampled EXPLAIN ANALYZE runs. While they lack plan detail, they give a quick view of overall cost and can flag outliers that need deeper analysis.

Why the Staging‑Only Workflow Works

EXPLAIN returns the optimizer’s plan with virtually zero overhead, so it can run on production traffic without side effects. However, it only estimates rows and cost. Running EXPLAIN ANALYZE on a staging copy lets you see the real execution time, rows examined, and whether the chosen index is actually used. If the staging environment mirrors production in schema, data distribution, and server configuration, the estimates will be highly correlated with production behavior.

Sampling Strategy for Production

1. Select a sample rate. 1–5 % of queries usually yields a good trade‑off. Use performance_schema.events_statements_summary_by_digest to identify hot queries and sample only those.
2. Set a MAX_EXECUTION_TIME. In a session, run:

   SET SESSION max_execution_time = 200; -- 200 ms
   EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'shipped';
   

   The statement will abort after 200 ms, limiting latency impact. For DML, wrap in a transaction:

   START TRANSACTION;
   SET SESSION max_execution_time = 200;
   EXPLAIN ANALYZE UPDATE orders SET shipped_at = NOW() WHERE id = 12345;
   ROLLBACK;
   

   This guarantees no data changes.
3. Collect results. Store the actual_time, rows_examined, and rows_sent columns in a separate table for later comparison.
4. Adjust the limit. If the abort is too frequent, increase the limit; if it rarely aborts, lower it.

When to Use Performance Schema Digests

If you cannot run EXPLAIN ANALYZE (e.g., due to strict latency budgets or non‑transactional engines), query the digest table for the same digest:

SELECT digest_text, avg_timer_wait, sum_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text = 'SELECT * FROM orders WHERE status = ?';

Compare avg_timer_wait (in picoseconds) to the sample average from EXPLAIN ANALYZE. A consistent ratio suggests the digest is representative.

Verification Checklist

• Run EXPLAIN on production to capture estimates.
• Run EXPLAIN ANALYZE on a staging copy; compare estimated vs actual rows.
• Enable Performance Schema and query the digest table for the same query.
• For sampled production, confirm that statements abort after MAX_EXECUTION_TIME and that a rollback leaves data unchanged.

Missing Diagnostic Detail

To fine‑tune the recommendation, could you confirm whether the queries you target are read‑only or include DML? Knowing that will determine if transaction wrapping is necessary and whether partial updates could affect the system during sampling.