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"