Plan Regressions, Optimizer Hints, Invisible Indexes, and Evidence-Driven Tuning

Learning outcomes

A plan regression is not simply “EXPLAIN looks different.” It is a meaningful deterioration in latency, resource use, row work, or stability after some relevant change—data distribution, statistics, indexes, schema, configuration, or server version. The professional response is a reproducible comparison, not a permanent hint copied from a forum.

Define a baseline before creating the regression

The ServiceHub dispatcher query from Lesson 1 now has a workload-shaped composite index. Capture its schema and plan before changing anything.

USE servicehub_plan_lab;-- Reuse the composite access path created in Lesson 1.-- If you run Lesson 5 standalone, first create:-- CREATE INDEX ix_wo_tenant_status_opened--   ON work_orders (tenant_id,status,opened_at,customer_id,work_order_id);ANALYZE TABLE work_orders;SELECT VERSION() AS server_version,@@version_comment AS version_comment;SHOW CREATE TABLE work_orders;SHOW INDEX FROM work_orders WHERE Key_name='ix_wo_tenant_status_opened';EXPLAIN FORMAT=TREESELECT c.customer_id,c.segment,w.work_order_id,w.opened_atFROM work_orders AS wJOIN customers AS c ON c.customer_id=w.customer_idWHERE w.tenant_id=17 AND w.status='open'  AND w.opened_at >= '2026-05-01'ORDER BY w.opened_at DESC LIMIT 40;EXPLAIN ANALYZESELECT c.customer_id,c.segment,w.work_order_id,w.opened_atFROM work_orders AS wJOIN customers AS c ON c.customer_id=w.customer_idWHERE w.tenant_id=17 AND w.status='open'  AND w.opened_at >= '2026-05-01'ORDER BY w.opened_at DESC LIMIT 40;

Store the plan with the exact predicates and dataset description. A plan without parameter context is not a reliable baseline: a plan that is excellent for one tenant/date range can be poor for another.

Reproduce a regression reversibly with an invisible index

Invisible indexes are maintained on writes but ignored by normal optimizer planning. That makes visibility a safer way to answer “what if the optimizer could not use this index?” than dropping and rebuilding a potentially large index.

ALTER TABLE work_orders ALTER INDEX ix_wo_tenant_status_opened INVISIBLE;SHOW INDEX FROM work_orders WHERE Key_name='ix_wo_tenant_status_opened';EXPLAIN FORMAT=TREESELECT c.customer_id,c.segment,w.work_order_id,w.opened_atFROM work_orders AS wJOIN customers AS c ON c.customer_id=w.customer_idWHERE w.tenant_id=17 AND w.status='open'  AND w.opened_at >= '2026-05-01'ORDER BY w.opened_at DESC LIMIT 40;EXPLAIN ANALYZESELECT c.customer_id,c.segment,w.work_order_id,w.opened_atFROM work_orders AS wJOIN customers AS c ON c.customer_id=w.customer_idWHERE w.tenant_id=17 AND w.status='open'  AND w.opened_at >= '2026-05-01'ORDER BY w.opened_at DESC LIMIT 40;

Expected qualitative regression: MySQL must choose another visible access path such as ix_wo_tenant_opened, then filter status separately. Whether local elapsed time changes dramatically depends on cache and machine speed; the robust evidence is increased rows examined/produced by intermediate iterators and any extra sorting or lookups.

Use the invisible index in one controlled query

MySQL’s use_invisible_indexes optimizer-switch flag is off by default. The SET_VAR hint can enable it for one statement, which makes a useful A/B experiment without changing the session permanently.

EXPLAIN ANALYZESELECT /*+ SET_VAR(optimizer_switch='use_invisible_indexes=on') */       c.customer_id,c.segment,w.work_order_id,w.opened_atFROM work_orders AS wJOIN customers AS c ON c.customer_id=w.customer_idWHERE w.tenant_id=17 AND w.status='open'  AND w.opened_at >= '2026-05-01'ORDER BY w.opened_at DESC LIMIT 40;

If the original access path returns and actual row work falls, you have strong evidence that the hidden index is materially valuable for this workload. Remember: invisibility did not remove write amplification or storage, so this experiment tests plan dependency, not the full benefit/cost of dropping the index.

Hints are experiments and narrow safeguards, not a substitute for diagnosis

MySQL 8.4 supports optimizer hints such as index-level INDEX/NO_INDEX and join-order hints. Older FORCE INDEX/USE INDEX/IGNORE INDEX forms still work, but current documentation says the index-level optimizer hints are intended to supersede them.

-- Make the index visible again first so a normal forced-index experiment is meaningful.ALTER TABLE work_orders ALTER INDEX ix_wo_tenant_status_opened VISIBLE;EXPLAIN ANALYZESELECT /*+ INDEX(w ix_wo_tenant_status_opened) */       c.customer_id,c.segment,w.work_order_id,w.opened_atFROM work_orders AS wJOIN customers AS c ON c.customer_id=w.customer_idWHERE w.tenant_id=17 AND w.status='open'  AND w.opened_at >= '2026-05-01'ORDER BY w.opened_at DESC LIMIT 40;-- Compare immediately with the unhinted statement.EXPLAIN ANALYZESELECT c.customer_id,c.segment,w.work_order_id,w.opened_atFROM work_orders AS wJOIN customers AS c ON c.customer_id=w.customer_idWHERE w.tenant_id=17 AND w.status='open'  AND w.opened_at >= '2026-05-01'ORDER BY w.opened_at DESC LIMIT 40;

A hint that reproduces the good plan proves a hypothesis about plan selection; it does not prove the hint is the best permanent fix. If statistics are stale, refresh them. If the access path is missing, add or reshape the index. If the query asks for too much data, redesign the query or application flow. Use permanent hints only when you have tested a bounded need, documented why the optimizer cannot reliably infer it, and added upgrade/regression tests.

Statistics changes can also reproduce or repair regressions

A release, bulk load, or distribution shift can make old estimates wrong. Before blaming the optimizer version, compare statistics state and actual distribution.

SELECT channel,COUNT(*) AS exact_rowsFROM work_orders GROUP BY channel ORDER BY exact_rows DESC;SELECT SCHEMA_NAME,TABLE_NAME,COLUMN_NAME,HISTOGRAMFROM INFORMATION_SCHEMA.COLUMN_STATISTICSWHERE SCHEMA_NAME='servicehub_plan_lab'  AND TABLE_NAME='work_orders';ANALYZE TABLE work_orders;ANALYZE TABLE work_orders UPDATE HISTOGRAM ON channel WITH 16 BUCKETS;EXPLAIN FORMAT=TREESELECT COUNT(*) FROM work_orders WHERE channel='monitor';

If estimates improve after statistics refresh, record that as the root correction. If the plan does not change and the query remains expensive, the evidence points elsewhere—often access-path design, data volume, or query semantics.

Optimizer trace: deeper evidence when EXPLAIN is not enough

MySQL can produce an optimizer trace for statements in the current session. It is verbose and can consume memory, so use it selectively in diagnostics, not as always-on production logging.

SET optimizer_trace='enabled=on';SELECT c.customer_idFROM customers AS cWHERE EXISTS (SELECT 1 FROM work_orders AS w              WHERE w.customer_id=c.customer_id AND w.status='open')LIMIT 20;SELECT QUERY,TRACE,MISSING_BYTES_BEYOND_MAX_MEM_SIZE,INSUFFICIENT_PRIVILEGESFROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\GSET optimizer_trace='enabled=off';

The trace can show considered alternatives and reasons for choices. Do not copy its entire JSON into an alerting system; use it for focused root-cause investigation when normal plan evidence leaves an unanswered “why.”

Write the tuning decision like an engineering change

Final Chapter 09 lab

Run the baseline query for at least three parameter shapes, capture unhinted EXPLAIN ANALYZE, toggle the composite index invisible, compare, restore it, refresh statistics, and write a one-page decision record. The outcome may be “keep the index,” “replace it with a more reusable key,” or “the index is unnecessary for our measured workload.” What matters is that the conclusion follows evidence.

ALTER TABLE work_orders ALTER INDEX ix_wo_tenant_status_opened VISIBLE;ANALYZE TABLE work_orders;SET optimizer_trace='enabled=off';SHOW INDEX FROM work_orders;SELECT VERSION() AS server_version;

Chapter summary and bridge to Chapter 10

Chapter 09 turned the optimizer into an observable engineering system: estimates, actual iterator evidence, persistent statistics, histograms, join/subquery transformations, invisible indexes, hints, and regression records. Chapter 10 moves from query planning to server-side database objects—views, stored procedures/functions, triggers, and the event scheduler—where correctness and hidden coupling become the central design concerns.

Authoritative references

• MySQL 8.4 — Invisible Indexes
• MySQL 8.4 — Optimizer Hints
• MySQL 8.4 — Index Hints
• MySQL 8.4 — Controlling the Query Optimizer
• MySQL 8.4 — Tracing the Optimizer