Optimizer Switches, Hints, Invisible/Ignored Index Concepts, and Regression Control

Learning outcomes

After a MariaDB upgrade, one ServiceHub query regresses. The team immediately adds FORCE INDEX, commits it to the ORM, and declares victory. Six months later the data distribution changes, the forced index becomes worse than the optimizer’s natural plan, and nobody remembers why the hint exists. Hints can be valuable, but a durable regression workflow needs a query identity, before/after plans, runtime measurements, change isolation, acceptance criteria and an explicit removal/rollback path.

1. A regression-control packet starts before the fix

USE servicehub_optimizer_lab;SELECT VERSION(),@@optimizer_switch,@@use_stat_tables;SHOW CREATE TABLE work_orders\GSHOW INDEX FROM work_orders;EXPLAIN FORMAT=JSONSELECT work_order_id,scheduled_at,total_centsFROM work_ordersWHERE status='open' AND region_code='BAK'ORDER BY scheduled_at DESCLIMIT 50;ANALYZE FORMAT=JSONSELECT work_order_id,scheduled_at,total_centsFROM work_ordersWHERE status='open' AND region_code='BAK'ORDER BY scheduled_at DESCLIMIT 50;

The regression record should include a normalized query fingerprint plus representative parameter classes, not only one literal query. A predicate can be selective for one value and broad for another. Capture server version, optimizer policy, relevant DDL, statistics state and data-distribution summary. If the issue appeared after an upgrade, keep the pre-upgrade plan/runtime packet as the comparison target; MariaDB’s optimizer changed materially starting in 11.0, so plan changes across versions are expected and must be validated rather than assumed good or bad.

2. Classic index hints are local but still brittle

EXPLAINSELECT work_order_id,scheduled_at,total_centsFROM work_ordersWHERE status='open' AND region_code='BAK'ORDER BY scheduled_at DESC LIMIT 50;EXPLAINSELECT work_order_id,scheduled_at,total_centsFROM work_orders FORCE INDEX(idx_status_region_sched)WHERE status='open' AND region_code='BAK'ORDER BY scheduled_at DESC LIMIT 50;

If the forced plan is better in a controlled test, that is a clue—not proof that FORCE INDEX is the final design. Ask why the optimizer rejected it. A stale or skew-blind estimate can often be fixed with statistics; a query shape may need a better composite index; an upgrade may have changed cost assumptions. Keep the hint as a tactical containment only when the business needs immediate stability and the underlying cause cannot be corrected safely yet.

3. Ignored indexes create a reversible removal experiment

ALTER TABLE work_orders  ALTER INDEX idx_customer_sched IGNORED;SHOW INDEX FROM work_orders;SELECT INDEX_NAME,IGNOREDFROM information_schema.STATISTICSWHERE TABLE_SCHEMA='servicehub_optimizer_lab'  AND TABLE_NAME='work_orders';EXPLAINSELECT * FROM work_ordersWHERE customer_id=120ORDER BY scheduled_at DESC;-- Roll back the experiment instantly:ALTER TABLE work_orders  ALTER INDEX idx_customer_sched NOT IGNORED;

MariaDB keeps an ignored index maintained and visible in metadata but the optimizer treats it as if it does not exist; it is also not used as a source of optimizer statistics. This makes ignored state useful before dropping a suspected redundant index: observe the real workload, restore instantly if regressions appear, and only schedule a physical drop after the observation window. The primary key cannot be ignored.

4. Deliberately wrong: force the plan before checking statistics

A hint can make symptoms disappear while preserving the bad estimate that caused the regression. That is dangerous because related queries without the hint can still regress, and the hinted query may become worse later. The repair workflow is causal: validate statistics and index definitions, compare estimated/actual rows, inspect transformations, then use the narrowest control necessary.

1. Fingerprint query and classify parameters.2. Capture EXPLAIN FORMAT=JSON and safe ANALYZE FORMAT=JSON.3. Record relevant index/statistics metadata and data skew.4. Reproduce under representative cache/concurrency.5. Apply one controlled change: stats refresh, index, SQL rewrite, switch, or hint.6. Re-run the same measurements.7. Accept only if correctness is unchanged and target latency/throughput improves without a material regression elsewhere.8. Define rollback and, for hints, a retirement date/condition.

Do not combine “refresh stats + add index + change optimizer_switch + deploy ORM hint” into one experiment. You will not know which change mattered, and rollback becomes ambiguous. One-variable-at-a-time discipline is especially important in query tuning because plan changes can interact nonlinearly.

5. New-style hints are diagnostic tools, not a new configuration language

MariaDB 12.0 introduced join-order hints such as JOIN_FIXED_ORDER and table-level BKA/BNL controls; 12.1 expanded the hint set further. MariaDB 12.3 also documents newer query-control additions. These features can make A/B plan experiments precise, but their availability and semantics are version-sensitive. A course or runbook should always state the minimum server version and verify ignored/accepted hints rather than copying MySQL syntax by appearance.

-- MariaDB 12.0+ join-order hint example for a disposable test:EXPLAIN FORMAT=JSONSELECT /*+ JOIN_FIXED_ORDER() */       c.customer_id,w.work_order_idFROM customers cJOIN work_orders w ON w.customer_id=c.customer_idWHERE c.region_code='TBZ' AND w.status='open';

If the hinted plan wins, use optimizer trace and cardinality evidence to understand why the optimizer’s natural order differed. If the hint is deployed, document the exact incident, server version, baseline metrics, owner, expiry/retirement condition and test coverage that will tell you when it can be removed.

6. Regression acceptance and rollback matrix

A plan is not “better” merely because it uses more indexes or shows a lower local execution time once. Production judgment includes tail latency, concurrency, write cost, memory, lock behavior, replication impact and operational reversibility. The optimizer is one component of a system; regression control must protect the whole workload.

7. Parameter-class baselines prevent false regressions

A single SQL fingerprint can represent workloads with very different selectivity. For example, the same predicate can target a tiny enterprise customer or a tenant that owns a large fraction of the table. A regression packet should therefore identify parameter classes such as rare, typical, and dominant—not only one literal query. If a change improves the rare class but damages the dominant class, the aggregate dashboard may hide the tradeoff until traffic mix changes.

For each class, retain plan and runtime evidence plus workload-level latency percentiles. This makes upgrade testing substantially stronger: you can compare whether the new optimizer merely chose a different-looking plan or actually changed resource consumption for a business-relevant segment.

8. Hints and ignored indexes need lifecycle ownership

Ownership matters because tactical controls age. A hint written for MariaDB 12.3 may become unnecessary after statistics improve or optimizer behavior changes. An ignored index retained forever still consumes write and storage cost. Every control should therefore have an owner, review date, rollback command and measurable condition for removal.

9. Chapter verification, cleanup, and next bridge

1. Confirm all query experiments were run on the disposable ServiceHub optimizer lab.
2. Restore any changed session optimizer settings.
3. Restore ignored indexes to NOT IGNORED unless the experiment explicitly requires otherwise.
4. Save one complete regression packet: SQL fingerprint, parameter class, EXPLAIN JSON, ANALYZE JSON, stats/index evidence, before/after metric, rollback criterion.
5. Clean up with DROP DATABASE servicehub_optimizer_lab; when finished.

Chapter 10 completes the optimizer evidence loop: candidate plans, runtime counters, statistics, transformations and scoped controls. Chapter 11 shifts from query planning to server-side objects—views, routines, triggers and the event scheduler—where definers, privileges and hidden coupling become the next operational correctness boundary.

Authoritative references

• MariaDB Documentation — Ignored Indexes
• MariaDB Documentation — USE INDEX
• MariaDB Documentation — IGNORE INDEX
• MariaDB Documentation — Expanded New-Style Optimizer Hints
• MariaDB Documentation — optimizer_switch