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
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 is slow, query takes forever, full table scan, missing index, wrong index, stale statistics, how do I read EXPLAIN, how do I optimize a query, what does type=ALL mean, getting started with query optimization
license
MIT
compatibility
Designed for Claude Code. Requires MariaDB 10.6-LTS, 10.11-LTS, 11.x, 12.x.
metadata
{"author":"OpenAEC-Foundation","version":"1.0"}
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.
: and indicate the optimizer cannot satisfy ORDER BY or GROUP BY from an index. Composite index that matches columns usually fixes it.
Check Extra for red flags
Using filesort
Using temporary
filter + sort
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
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'ORDERBY 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.
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 FORORDERBY (idx_created_at_desc)
WHERE customer_id =4711ORDERBY 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) :
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 planSET 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 =4711AND 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).
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.
