MariaDB Query Optimization
How to read EXPLAIN, validate with ANALYZE, steer the optimizer via optimizer_switch and index hints, capture decisions with optimizer_trace, and keep statistics fresh with ANALYZE TABLE. Use this skill any time a query is slow, before adding an index, and before introducing a hint.
Quick Reference
EXPLAIN returns the plan ; ANALYZE FORMAT=JSON runs the query and shows actual rows next to estimates.
type=ALL means full table scan : unacceptable on any table over a few thousand rows. Boundary "fine vs fix now" sits at type=range. type=index is a full index scan, not a range scan.
optimizer_switch flags are MariaDB-specific. NEVER copy MySQL 8 optimizer assumptions ; flag names and defaults differ (e.g. rowid_filter, condition_pushdown_for_subquery, split_materialized).
optimizer_trace (10.4.3+) is the deep-dive tool : SET optimizer_trace='enabled=on' then read INFORMATION_SCHEMA.OPTIMIZER_TRACE.
- ALWAYS run
ANALYZE TABLE after bulk inserts or schema changes. Stale statistics drive bad plans.
- ALWAYS use
ANALYZE SELECT, NEVER ANALYZE UPDATE or ANALYZE DELETE for diagnostics : the last two execute the mutation.
FORCE INDEX overrides the cost model. Use it for verification, NOT as a long-term production fix : it locks you to a plan that breaks when statistics or data shape change.
- Index hints accept
FOR JOIN, FOR ORDER BY, FOR GROUP BY modifiers. Default scope is FOR JOIN (affects WHERE).
Decision Tree : "My query is slow"
- Get the plan : run
EXPLAIN <query>. Read the type column first.
- If
type=ALL on a large table : the optimizer is doing a full table scan. Go to step 4.
- If
type=index : full index scan (not as bad as ALL, but not a range scan). Check whether a more selective composite index would help.
- Check
Extra for red flags : Using filesort and Using temporary indicate the optimizer cannot satisfy ORDER BY or GROUP BY from an index. Composite index that matches filter + sort columns usually fixes it.
- Verify the estimate : run
ANALYZE FORMAT=JSON <query>. Compare r_rows (actual) vs rows (estimate). Large mismatch means stale statistics : run ANALYZE TABLE.
- If estimates are correct but the plan is still bad : check
optimizer_switch defaults. Some MariaDB flags are OFF by default (mrr, mrr_cost_based, index_merge_sort_intersection). Toggle for the session and re-EXPLAIN.
- If the optimizer rejects a clearly better index : enable
optimizer_trace, read the trace, find the cost calculation that rejected the index.
- Last resort : use
IGNORE INDEX (bad_idx) to exclude the bad path. FORCE INDEX only for one-off verification, not for shipped code.
Reading the EXPLAIN Output
| Column |
What it means |
What to watch |
id |
Statement / subquery sequence |
Higher id = deeper subquery ; same id = joined together |
select_type |
Statement role |
SIMPLE, PRIMARY, SUBQUERY, DEPENDENT SUBQUERY (BAD : re-executed per outer row), DERIVED, MATERIALIZED, UNION, LATERAL DERIVED |
table |
Physical or derived table |
Aliases shown as <derivedN> for inline subqueries |
type |
Access method |
Best to worst : system, const, eq_ref, ref, ref_or_null, index_subquery, unique_subquery, index_merge, range, index, ALL. Fix anything worse than range. |
possible_keys |
Indexes the optimizer considered |
NULL = no usable index found |
key |
Index actually chosen |
NULL = full table scan |
key_len |
Bytes of the index used |
Short value on a composite key means trailing columns are not used |
ref |
What is compared against the index |
const good, column name = join |
rows |
Estimated rows the optimizer expects to read |
Estimate ; verify against r_rows from ANALYZE |
Extra |
Notes on plan execution |
Watch for Using filesort, Using temporary, Using join buffer, Range checked for each record |
-- 10.6+
EXPLAIN SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 50;
-- type=ALL on orders + Using filesort + Using temporary = composite index
-- on (created_at DESC, customer_id) almost always fixes both.
EXPLAIN FORMAT=JSON and ANALYZE FORMAT=JSON
EXPLAIN FORMAT=JSON returns a structured tree with per-node r_filtered, read_cost, eval_cost, and access strategy.
ANALYZE FORMAT=JSON (10.1+) actually runs the query and adds r_rows, r_filtered, r_total_time_ms, r_loops. This is the gold standard for "is my optimizer estimate correct" checks.
-- 10.6+
ANALYZE FORMAT=JSON
SELECT *
FROM orders
WHERE customer_id = 4711
AND created_at >= '2026-01-01';
If rows: 5000 but r_rows: 12 : your statistics are wrong. Run ANALYZE TABLE orders. If r_rows matches rows but the plan is still bad : the cost model is choosing wrong, go to optimizer_trace.
NEVER substitute UPDATE or DELETE for SELECT in ANALYZE. ANALYZE UPDATE and ANALYZE DELETE execute the mutation.
Index Hints
Three keywords, three meanings :
-- 10.6+
-- USE INDEX : optimizer chooses from this list, or table scan if none fit.
SELECT * FROM orders USE INDEX (idx_created_at)
WHERE created_at >= '2026-01-01';
-- FORCE INDEX : same as USE INDEX but tells the optimizer the table scan is
-- prohibitively expensive. The hint sticks even when stats say "scan is faster".
SELECT * FROM orders FORCE INDEX (idx_created_at)
WHERE created_at >= '2026-01-01';
-- IGNORE INDEX : exclude a specific index, keep the rest of the cost model.
-- Lowest blast-radius for production : the plan can still adapt as data changes.
SELECT * FROM orders IGNORE INDEX (idx_bad_legacy)
WHERE created_at >= '2026-01-01';
Modifiers steer the hint at a specific clause :
-- 10.6+
SELECT * FROM orders
USE INDEX FOR ORDER BY (idx_created_at_desc)
WHERE customer_id = 4711
ORDER BY created_at DESC;
Default modifier is FOR JOIN ; the hint applies to WHERE-clause access. ALWAYS use the smallest hint that solves the problem :
- One bad path :
IGNORE INDEX (that_one).
- Want a specific index considered first :
USE INDEX (...).
- Cost model is fundamentally wrong (stale stats, complex predicate) :
FORCE INDEX (...) for diagnosis, then fix the root cause.
optimizer_switch (MariaDB-Specific)
Toggle individual transformations. Common defaults (verified against KB optimizer-switch) :
- ON by default :
index_merge, index_merge_intersection, index_merge_union, index_merge_sort_union, index_condition_pushdown, semijoin, materialization, loosescan, firstmatch, subquery_cache, derived_merge, derived_with_keys, condition_pushdown_for_derived, condition_pushdown_for_subquery (10.4+), condition_pushdown_from_having (10.4.3+), rowid_filter (10.4.3+), split_materialized (10.3.4+), exists_to_in, extended_keys.
- OFF by default :
mrr, mrr_cost_based, mrr_sort_keys, index_merge_sort_intersection, not_null_range_scan (10.5+), hash_join_cardinality (until 11.0.3).
-- 10.6+ : disable derived_merge for one session to test materialized plan
SET SESSION optimizer_switch = 'derived_merge=off';
-- Check current state :
SELECT @@SESSION.optimizer_switch;
NEVER set every flag to ON globally. The defaults reflect cost-model decisions that have been right more often than wrong in practice. Tuning by flag is a session-scope diagnostic tool, not a production policy.
NEVER copy MySQL 8 optimizer hints (/*+ NO_BNL(t1) */, JSON_TABLE-specific hints) to MariaDB : flag names and defaults are different. Verify with SELECT @@optimizer_switch.
optimizer_trace (10.4.3+)
When EXPLAIN and ANALYZE FORMAT=JSON are not enough, dump the optimizer's full decision log.
-- 10.6+ (feature 10.4.3+)
SET optimizer_trace = 'enabled=on';
SELECT * FROM orders
WHERE customer_id = 4711
AND created_at >= '2026-01-01';
SELECT QUERY,
TRACE,
MISSING_BYTES_BEYOND_MAX_MEM_SIZE,
INSUFFICIENT_PRIVILEGES
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
SET optimizer_trace = 'enabled=off';
optimizer_trace_max_mem_size defaults to 1048576 bytes. If MISSING_BYTES_BEYOND_MAX_MEM_SIZE > 0 the trace was truncated : raise the limit.
- Each connection stores the trace from the last executed statement only.
- Use it to read why the optimizer rejected a particular index (cost = X) or chose a join order.
Keeping Statistics Fresh
The optimizer's cost model needs accurate row counts and cardinality. ALWAYS run ANALYZE TABLE after :
- Bulk inserts of >10% of table size.
ALTER TABLE that rebuilds.
- Restore from backup.
- Large
DELETE (statistics see fewer rows than the data file).
-- 10.6+ : engine-internal stats (default, fast)
ANALYZE TABLE orders;
-- 10.6+ : engine-independent persistent stats (richer, slower)
ANALYZE TABLE orders PERSISTENT FOR ALL;
use_stat_tables controls engine-independent statistics :
NEVER : engine-internal only (default in older versions).
COMPLEMENTARY : use engine-internal if PERSISTENT is missing.
PREFERABLY : use PERSISTENT if collected.
-- 10.6+ : prefer richer EITS when collected
SET SESSION use_stat_tables = 'PREFERABLY';
Histograms (controlled by histogram_type, histogram_size) further improve selectivity estimates for non-indexed columns. Set histogram_size=0 to disable, default is engine-dependent.
Reading the Signal : EXPLAIN Red Flags
| Signal |
Meaning |
Typical fix |
type=ALL |
Full table scan |
Add an index that matches the WHERE predicate |
type=index |
Full index scan |
Add a more selective composite index, or rewrite to use range |
Using filesort |
External sort step |
Add composite index matching WHERE + ORDER BY |
Using temporary |
Intermediate temp table for GROUP BY / DISTINCT / UNION |
Add covering index or rewrite to avoid materialization |
Using join buffer (Block Nested Loop) |
Join without index access |
Add index on join column |
Range checked for each record |
No usable index decided up-front |
Often dynamic predicate ; consider rewriting |
DEPENDENT SUBQUERY |
Subquery re-executed per outer row |
Rewrite as JOIN or use semijoin transformation |
Using index (in Extra) |
Covering index, no row read |
GOOD : keep |
Using index condition |
ICP active, predicate pushed to engine |
GOOD : keep |
Quality Rules
- ALWAYS run
EXPLAIN before tuning a query. ALWAYS verify with ANALYZE FORMAT=JSON before adding a hint.
- ALWAYS run
ANALYZE TABLE after bulk data movement, before judging plans.
- NEVER ship
FORCE INDEX in production code as the primary fix. Use it to diagnose, then fix the root cause (index, stats, query).
- NEVER set
optimizer_switch flags globally without measuring. Session-scope is the diagnostic tool ; global change is the production decision.
- NEVER assume MySQL 8 optimizer behaviour applies. Flag names and defaults differ ; verify with
@@optimizer_switch.
- NEVER run
ANALYZE UPDATE or ANALYZE DELETE to "see what it would do" : they execute the mutation.
Reference Links
references/methods.md : EXPLAIN column reference, optimizer_switch full flag list with version + default, optimizer_trace API, ANALYZE TABLE syntax, statistics variables.
references/examples.md : ten worked examples covering EXPLAIN reading, FORCE INDEX comparison, optimizer_trace dump, ANALYZE FORMAT=JSON estimate-vs-actual, statistics refresh after bulk insert, derived_with_keys flag impact, ICP + covering index.
references/anti-patterns.md : eight real anti-patterns with code and corrected alternatives.
Sources
1---2name: mariadb-impl-query-optimization3description: "Use when a query is slow, when reading EXPLAIN output, when deciding on index hints, when tuning optimizer_switch flags, when using optimizer_trace, or when comparing MariaDB and MySQL optimizer behavior. Prevents the common mistake of trusting type=ALL queries in production, blindly applying USE INDEX without checking selectivity, leaving outdated statistics, or copying MySQL 8 optimizer assumptions to MariaDB. Covers EXPLAIN reading column-by-column, EXPLAIN FORMAT=JSON, ANALYZE FORMAT=JSON (actual execution stats), index hints USE/FORCE/IGNORE INDEX, optimizer_switch flags (MariaDB-specific), optimizer_trace, ANALYZE TABLE for statistics, persistent vs in-memory statistics. Keywords: EXPLAIN, EXPLAIN FORMAT JSON, ANALYZE FORMAT JSON, query plan, optimizer, optimizer_switch, optimizer_trace, USE INDEX, FORCE INDEX, IGNORE INDEX, type ALL, type ref, type range, key_len, rows, filtered, Using filesort, Using temporary, Using index, ANALYZE TABLE, persistent statistics, why is my query slow, slow query, query "4license: MIT5---67# MariaDB Query Optimization89How to read `EXPLAIN`, validate with `ANALYZE`, steer the optimizer via `optimizer_switch` and index hints, capture decisions with `optimizer_trace`, and keep statistics fresh with `ANALYZE TABLE`. Use this skill any time a query is slow, before adding an index, and before introducing a hint.1011## Quick Reference1213- `EXPLAIN` returns the plan ; `ANALYZE FORMAT=JSON` runs the query and shows actual rows next to estimates.14- `type=ALL` means full table scan : unacceptable on any table over a few thousand rows. Boundary "fine vs fix now" sits at `type=range`. `type=index` is a full index scan, not a range scan.15- `optimizer_switch` flags are MariaDB-specific. NEVER copy MySQL 8 optimizer assumptions ; flag names and defaults differ (e.g. `rowid_filter`, `condition_pushdown_for_subquery`, `split_materialized`).16- `optimizer_trace` (10.4.3+) is the deep-dive tool : `SET optimizer_trace='enabled=on'` then read `INFORMATION_SCHEMA.OPTIMIZER_TRACE`.17- ALWAYS run `ANALYZE TABLE` after bulk inserts or schema changes. Stale statistics drive bad plans.18- ALWAYS use `ANALYZE SELECT`, NEVER `ANALYZE UPDATE` or `ANALYZE DELETE` for diagnostics : the last two execute the mutation.19- `FORCE INDEX` overrides the cost model. Use it for verification, NOT as a long-term production fix : it locks you to a plan that breaks when statistics or data shape change.20- Index hints accept `FOR JOIN`, `FOR ORDER BY`, `FOR GROUP BY` modifiers. Default scope is `FOR JOIN` (affects WHERE).2122## Decision Tree : "My query is slow"23241. **Get the plan** : run `EXPLAIN <query>`. Read the `type` column first.252. **If `type=ALL` on a large table** : the optimizer is doing a full table scan. Go to step 4.263. **If `type=index`** : full index scan (not as bad as ALL, but not a range scan). Check whether a more selective composite index would help.274. **Check `Extra` for red flags** : `Using filesort` and `Using temporary` indicate the optimizer cannot satisfy ORDER BY or GROUP BY from an index. Composite index that matches `filter + sort` columns usually fixes it.285. **Verify the estimate** : run `ANALYZE FORMAT=JSON <query>`. Compare `r_rows` (actual) vs `rows` (estimate). Large mismatch means stale statistics : run `ANALYZE TABLE`.296. **If estimates are correct but the plan is still bad** : check `optimizer_switch` defaults. Some MariaDB flags are OFF by default (`mrr`, `mrr_cost_based`, `index_merge_sort_intersection`). Toggle for the session and re-EXPLAIN.307. **If the optimizer rejects a clearly better index** : enable `optimizer_trace`, read the trace, find the cost calculation that rejected the index.318. **Last resort** : use `IGNORE INDEX (bad_idx)` to exclude the bad path. `FORCE INDEX` only for one-off verification, not for shipped code.3233## Reading the EXPLAIN Output3435| Column | What it means | What to watch |36|--------|---------------|---------------|37| `id` | Statement / subquery sequence | Higher id = deeper subquery ; same id = joined together |38| `select_type` | Statement role | `SIMPLE`, `PRIMARY`, `SUBQUERY`, `DEPENDENT SUBQUERY` (BAD : re-executed per outer row), `DERIVED`, `MATERIALIZED`, `UNION`, `LATERAL DERIVED` |39| `table` | Physical or derived table | Aliases shown as `<derivedN>` for inline subqueries |40| `type` | Access method | Best to worst : `system`, `const`, `eq_ref`, `ref`, `ref_or_null`, `index_subquery`, `unique_subquery`, `index_merge`, `range`, `index`, `ALL`. Fix anything worse than `range`. |41| `possible_keys` | Indexes the optimizer considered | NULL = no usable index found |42| `key` | Index actually chosen | NULL = full table scan |43| `key_len` | Bytes of the index used | Short value on a composite key means trailing columns are not used |44| `ref` | What is compared against the index | `const` good, column name = join |45| `rows` | Estimated rows the optimizer expects to read | Estimate ; verify against `r_rows` from ANALYZE |46| `Extra` | Notes on plan execution | Watch for `Using filesort`, `Using temporary`, `Using join buffer`, `Range checked for each record` |4748```sql49-- 10.6+50EXPLAIN SELECT o.id, c.name51 FROM orders o52 JOIN customers c ON c.id = o.customer_id53 WHERE o.created_at >= '2026-01-01'54 ORDER BY o.created_at DESC55 LIMIT 50;5657-- type=ALL on orders + Using filesort + Using temporary = composite index58-- on (created_at DESC, customer_id) almost always fixes both.59```6061## EXPLAIN FORMAT=JSON and ANALYZE FORMAT=JSON6263`EXPLAIN FORMAT=JSON` returns a structured tree with per-node `r_filtered`, `read_cost`, `eval_cost`, and access strategy.6465`ANALYZE FORMAT=JSON` (10.1+) actually runs the query and adds `r_rows`, `r_filtered`, `r_total_time_ms`, `r_loops`. This is the gold standard for "is my optimizer estimate correct" checks.6667```sql68-- 10.6+69ANALYZE FORMAT=JSON70 SELECT *71 FROM orders72 WHERE customer_id = 471173 AND created_at >= '2026-01-01';74```7576If `rows: 5000` but `r_rows: 12` : your statistics are wrong. Run `ANALYZE TABLE orders`. If `r_rows` matches `rows` but the plan is still bad : the cost model is choosing wrong, go to `optimizer_trace`.7778NEVER substitute `UPDATE` or `DELETE` for `SELECT` in `ANALYZE`. `ANALYZE UPDATE` and `ANALYZE DELETE` execute the mutation.7980## Index Hints8182Three keywords, three meanings :8384```sql85-- 10.6+86-- USE INDEX : optimizer chooses from this list, or table scan if none fit.87SELECT * FROM orders USE INDEX (idx_created_at)88 WHERE created_at >= '2026-01-01';8990-- FORCE INDEX : same as USE INDEX but tells the optimizer the table scan is91-- prohibitively expensive. The hint sticks even when stats say "scan is faster".92SELECT * FROM orders FORCE INDEX (idx_created_at)93 WHERE created_at >= '2026-01-01';9495-- IGNORE INDEX : exclude a specific index, keep the rest of the cost model.96-- Lowest blast-radius for production : the plan can still adapt as data changes.97SELECT * FROM orders IGNORE INDEX (idx_bad_legacy)98 WHERE created_at >= '2026-01-01';99```100101Modifiers steer the hint at a specific clause :102103```sql104-- 10.6+105SELECT * FROM orders106 USE INDEX FOR ORDER BY (idx_created_at_desc)107 WHERE customer_id = 4711108 ORDER BY created_at DESC;109```110111Default modifier is `FOR JOIN` ; the hint applies to WHERE-clause access. ALWAYS use the smallest hint that solves the problem :112113- One bad path : `IGNORE INDEX (that_one)`.114- Want a specific index considered first : `USE INDEX (...)`.115- Cost model is fundamentally wrong (stale stats, complex predicate) : `FORCE INDEX (...)` for diagnosis, then fix the root cause.116117## optimizer_switch (MariaDB-Specific)118119Toggle individual transformations. Common defaults (verified against KB `optimizer-switch`) :120121- **ON by default** : `index_merge`, `index_merge_intersection`, `index_merge_union`, `index_merge_sort_union`, `index_condition_pushdown`, `semijoin`, `materialization`, `loosescan`, `firstmatch`, `subquery_cache`, `derived_merge`, `derived_with_keys`, `condition_pushdown_for_derived`, `condition_pushdown_for_subquery` (10.4+), `condition_pushdown_from_having` (10.4.3+), `rowid_filter` (10.4.3+), `split_materialized` (10.3.4+), `exists_to_in`, `extended_keys`.122- **OFF by default** : `mrr`, `mrr_cost_based`, `mrr_sort_keys`, `index_merge_sort_intersection`, `not_null_range_scan` (10.5+), `hash_join_cardinality` (until 11.0.3).123124```sql125-- 10.6+ : disable derived_merge for one session to test materialized plan126SET SESSION optimizer_switch = 'derived_merge=off';127128-- Check current state :129SELECT @@SESSION.optimizer_switch;130```131132NEVER set every flag to ON globally. The defaults reflect cost-model decisions that have been right more often than wrong in practice. Tuning by flag is a session-scope diagnostic tool, not a production policy.133134NEVER copy MySQL 8 optimizer hints (`/*+ NO_BNL(t1) */`, `JSON_TABLE`-specific hints) to MariaDB : flag names and defaults are different. Verify with `SELECT @@optimizer_switch`.135136## optimizer_trace (10.4.3+)137138When `EXPLAIN` and `ANALYZE FORMAT=JSON` are not enough, dump the optimizer's full decision log.139140```sql141-- 10.6+ (feature 10.4.3+)142SET optimizer_trace = 'enabled=on';143144SELECT * FROM orders145 WHERE customer_id = 4711146 AND created_at >= '2026-01-01';147148SELECT QUERY,149 TRACE,150 MISSING_BYTES_BEYOND_MAX_MEM_SIZE,151 INSUFFICIENT_PRIVILEGES152 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;153154SET optimizer_trace = 'enabled=off';155```156157- `optimizer_trace_max_mem_size` defaults to 1048576 bytes. If `MISSING_BYTES_BEYOND_MAX_MEM_SIZE > 0` the trace was truncated : raise the limit.158- Each connection stores the trace from the last executed statement only.159- Use it to read why the optimizer rejected a particular index (cost = X) or chose a join order.160161## Keeping Statistics Fresh162163The optimizer's cost model needs accurate row counts and cardinality. ALWAYS run `ANALYZE TABLE` after :164165- Bulk inserts of >10% of table size.166- `ALTER TABLE` that rebuilds.167- Restore from backup.168- Large `DELETE` (statistics see fewer rows than the data file).169170```sql171-- 10.6+ : engine-internal stats (default, fast)172ANALYZE TABLE orders;173174-- 10.6+ : engine-independent persistent stats (richer, slower)175ANALYZE TABLE orders PERSISTENT FOR ALL;176```177178`use_stat_tables` controls engine-independent statistics :179180- `NEVER` : engine-internal only (default in older versions).181- `COMPLEMENTARY` : use engine-internal if PERSISTENT is missing.182- `PREFERABLY` : use PERSISTENT if collected.183184```sql185-- 10.6+ : prefer richer EITS when collected186SET SESSION use_stat_tables = 'PREFERABLY';187```188189Histograms (controlled by `histogram_type`, `histogram_size`) further improve selectivity estimates for non-indexed columns. Set `histogram_size=0` to disable, default is engine-dependent.190191## Reading the Signal : EXPLAIN Red Flags192193| Signal | Meaning | Typical fix |194|--------|---------|-------------|195| `type=ALL` | Full table scan | Add an index that matches the WHERE predicate |196| `type=index` | Full index scan | Add a more selective composite index, or rewrite to use range |197| `Using filesort` | External sort step | Add composite index matching `WHERE + ORDER BY` |198| `Using temporary` | Intermediate temp table for GROUP BY / DISTINCT / UNION | Add covering index or rewrite to avoid materialization |199| `Using join buffer (Block Nested Loop)` | Join without index access | Add index on join column |200| `Range checked for each record` | No usable index decided up-front | Often dynamic predicate ; consider rewriting |201| `DEPENDENT SUBQUERY` | Subquery re-executed per outer row | Rewrite as JOIN or use semijoin transformation |202| `Using index` (in Extra) | Covering index, no row read | GOOD : keep |203| `Using index condition` | ICP active, predicate pushed to engine | GOOD : keep |204205## Quality Rules206207- ALWAYS run `EXPLAIN` before tuning a query. ALWAYS verify with `ANALYZE FORMAT=JSON` before adding a hint.208- ALWAYS run `ANALYZE TABLE` after bulk data movement, before judging plans.209- NEVER ship `FORCE INDEX` in production code as the primary fix. Use it to diagnose, then fix the root cause (index, stats, query).210- NEVER set `optimizer_switch` flags globally without measuring. Session-scope is the diagnostic tool ; global change is the production decision.211- NEVER assume MySQL 8 optimizer behaviour applies. Flag names and defaults differ ; verify with `@@optimizer_switch`.212- NEVER run `ANALYZE UPDATE` or `ANALYZE DELETE` to "see what it would do" : they execute the mutation.213214## Reference Links215216- `references/methods.md` : EXPLAIN column reference, optimizer_switch full flag list with version + default, optimizer_trace API, ANALYZE TABLE syntax, statistics variables.217- `references/examples.md` : ten worked examples covering EXPLAIN reading, FORCE INDEX comparison, optimizer_trace dump, ANALYZE FORMAT=JSON estimate-vs-actual, statistics refresh after bulk insert, derived_with_keys flag impact, ICP + covering index.218- `references/anti-patterns.md` : eight real anti-patterns with code and corrected alternatives.219220## Sources221222- KB `EXPLAIN` : https://mariadb.com/kb/en/explain/223- KB `optimizer-switch` : https://mariadb.com/kb/en/optimizer-switch/224- KB `optimizer-trace-overview` (modern path) : https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizer/optimizer-trace/optimizer-trace-overview225- KB `ANALYZE` statement : https://mariadb.com/kb/en/analyze-statement/226- KB `ANALYZE TABLE` : https://mariadb.com/kb/en/analyze-table/227- KB index hints : https://mariadb.com/kb/en/index-hints-how-to-force-query-plans/