SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillsmariadb-impl-query-optimization

mariadb-impl-query-optimization

General & other

View repository

Updated 10-15 22:18

About this skill

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"

  1. Get the plan : run EXPLAIN <query>. Read the type column first.
  2. If type=ALL on a large table : the optimizer is doing a full table scan. Go to step 4.
  3. 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.
  4. 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.
  5. Verify the estimate : run ANALYZE FORMAT=JSON <query>. Compare r_rows (actual) vs rows (estimate). Large mismatch means stale statistics : run ANALYZE TABLE.
  6. 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.
  7. If the optimizer rejects a clearly better index : enable optimizer_trace, read the trace, find the cost calculation that rejected the index.
  8. 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