Skip to content 
I'll explain the MariaDB Optimizer Real-world examples: how it creates plans, estimates costs, and why it sometimes gets it wrong. Here's how to read the SQL execution plan effectively, use indexes wisely, and guide the optimizer with facts rather than gut feelings.
Key points
To start, I'll briefly summarize the most important components so you can put the following sections into context and Overview keep.
- Phases: Parsing, preparing, optimizing, and executing make up the lifecycle of every query.
- Cost model: Time-based microsecond values control index selection, scans, and join order.
- Statistics: Cardinality and histograms determine the selectivity estimate.
- Transparency: EXPLAIN, EXPLAIN ANALYZE, and Optimizer Trace open the black box.
- Tuning: Indexes, query rewrites, ANALYZE TABLE, and cost parameters boost performance.
The Lifecycle of a Query in MariaDB
Before a plan is formed, a query goes through four stages, which I specifically review on a daily basis in order to Causes to identify causes of slowness. During parsing, MariaDB converts SQL into an internal structure; syntax errors are detected at this stage. During preparation, the engine checks tables, columns, and potential indexes and performs simple transformations. Next comes optimization, during which candidate execution plans are calculated and evaluated using a cost model. During execution, the server implements the selected plan step by step: read, join, filter, return.
I clearly categorize analysis errors by phase, because that way, solutions are implemented more quickly and Measures Take a targeted approach. Performance issues usually stem from optimization: incorrect estimates, missing indexes, or inefficient join sequences. Parsing errors are trivial, but the preparation phase can already involve techniques such as view resolution or subquery transformations. During execution, inefficiencies become painfully apparent if a full scan was selected earlier. That’s why I start every investigation with a structured review of all four stages.
How the Optimizer Makes Decisions Internally
MariaDB operates on a cost-based approach and evaluates alternative execution plans using a Cost Function. For each variant, the server estimates the number of rows read, the selectivity of WHERE/ON clauses, access types such as table scan, index scan, and range scan, as well as the time required for individual operations. Internally, the server distinguishes between `join_preparation` and `join_optimization`. In `join_preparation`, query rewrites, condition simplifications, subquery transformations, and view resolutions take place. `join_optimization` calculates join orders, checks index candidates via `ref_optimizer_key_uses`, estimates rows via range scans, and assigns conditions to specific tables as early as possible.
This mechanism explains why a small filter in the wrong place can cause expensive Consequences If `attaching_conditions_to_tables` occurs late, the plan drags an unnecessary number of rows through joins. If statistics are out of date, `rows_estimation` and selectivity are incorrect; the optimizer then resorts to access paths that seem favorable but are actually slow. These are exactly the areas I focus on: better statistics, clearer predicates, and neatly sorted composite indexes. After that, the choice of execution plan often shifts noticeably.
Pricing Model Starting with MariaDB 11.0
Current releases no longer evaluate work roughly based on weights, but rather using microseconds for specific storage operations. Parameters such as `optimizer_disk_read_cost`, `optimizer_disk_read_ratio`, and `optimizer_where_cost` bring the model closer to actual execution times. This allows the optimizer to compare an index range scan versus a full scan based on real-world time assumptions. LAST_QUERY_COST displays the estimated total cost and often correlates much better with reality than it used to. For data-intensive systems, this finer-grained approach pays off immediately.
I carefully calibrate the model when hardware characteristics contradict the standard assumptions and thus the Plan Selection distort. NVMe SSDs, distributed storage, or specialized caches can significantly shift the disk ratio and read times. Minor adjustments to `optimizer_costs` cause MariaDB to favor sensible execution paths. I document every change and then check EXPLAIN ANALYZE to measure the impact. Without measurement, tuning remains a game of chance.
Selectivity, Statistics, and Histograms
Good estimates start with clean cardinality and reliable selectivity. MariaDB maintains statistics on different values for each column and can optionally use histograms for distributions. Uneven data in particular—hotspots, Zipf distributions, seasonal patterns—benefit from histograms. After major data changes, I run `ANALYZE TABLE` so that the optimizer can once again work with accurate data. Anyone who forgets to do this risks full scans, which are objectively incorrect.
I'm scheduling ANALYZE as a regular job, tailored to Changes in terms of data volume and critical tables. For highly skewed column distributions, histograms help provide a realistic assessment of the selectivity of singular values. This reduces miscalculations in range scans and merge strategies. Combined with appropriate composite indexes, hit accuracy improves dramatically. The result: shorter execution times and less I/O.
EXPLAIN and Reading Execution Plans
To make decisions transparent, I use EXPLAIN, EXPLAIN EXTENDED, and FORMAT=JSON. The standard columns provide a quick overview: id, select_type, table, type, possible_keys, key, key_len, ref, rows, and, if applicable, filtered. A `type=ALL` indicates a full scan, which is rarely desirable. `FORMAT=JSON` shows in detail how conditions were moved and which paths the optimizer evaluated. In a hosting context, I recommend the guide on Execution Plans in Hosting, to link plan information with infrastructure impacts.
To help me interpret the data quickly, I use a small table that briefly lists typical values and thus Misinterpretations prevented.
| EXPLAIN field | Typical value | Practical Significance |
|---|---|---|
| type | ALL, range, ref, eq_ref, const | The further to the right, the more selective; ALL indicates a full scan. |
| possible_keys | Index List | Indices that fit in theory; if there are no candidates here, there is no structure. |
| key | Index Name | Index actually used; blank means no index is used. |
| rows | Number | Estimated number of lines read; significantly different from reality = poor statistics. |
| filtered | Percent | How much is passed through the filter; less is often better. |
Why the Optimizer Sometimes Gets It Wrong
No cost model fits every situation, so I'm making some adjustments Mistakes Targeted. Outdated statistics lead to incorrect row estimates and suboptimal join orders. Improperly constructed composite indexes prevent index usage with multi-column filters. Highly nested subqueries make effective rewrites difficult and block materialization. Missing or misleading filters force the engine to move many rows before useful predicates take effect.
First, I check whether the query is formulated according to the Index What really helps: the left prefix rule, an appropriate sort order, and avoiding functions on columns in the WHERE clause. Then I check EXPLAIN ANALYZE to see if the actual execution supports the estimate. If not, I run ANALYZE TABLE and, if necessary, a rewrite. Only as a last resort do I resort to FORCE INDEX or hinting, because that can limit future optimizations.
Using the Optimizer Trace Effectively
If EXPLAIN isn't enough, I enable the optimizer trace and monitor Decisions in the JSON log. There, I can see which plans were considered, rejected, or accepted. I can see why a condition took effect late or why an index wasn't shortlisted. The log also shows how conditions were rearranged. This view deepens my understanding and provides concrete levers for the next round of tuning.
I save relevant sections of the trace along with the query hash and Parametersvalue. That way, I can later compare which change had which effect. The MariaDB Server documentation and various presentations within the ecosystem describe these fields in detail (Source: MariaDB Server documentation on the Query Optimizer and Optimizer Trace). With this tool, I can identify flawed assumptions faster than through trial and error. I save the most time when dealing with complex joins.
Practical Guide: Database Tuning Step by Step
I start every optimization with a clear Measurement. I identify problem queries through monitoring and the Slow Query Log. Then I compare EXPLAIN with EXPLAIN ANALYZE to compare the execution plan with the actual results side by side. I adjust the index strategy to match the WHERE, JOIN, and ORDER BY clauses; I align composite indexes with the most frequent access points. I only use FORCE INDEX if the optimizer selects the wrong candidate despite having correct statistics.
Each step involves maintaining the Statistics: ANALYZE TABLE on highly active tables; histograms for skewed distributions. I simplify unnecessary subqueries, materialize intermediate results as needed, and clean up old workarounds. For specialized hardware, I check optimizer_costs to ensure the microsecond model is accurate. I document every change with before-and-after values so that the impact remains traceable over time.
Common Optimizer Problems and Solutions
If EXPLAIN shows type=ALL even though possible_keys is filled, I first look at Selectivity. Often, the column order in the composite index is incorrect, or a function prevents the index from being used. In such cases, I reverse the order, remove problematic functions, or split predicates. If the join order is incorrect, I check whether early filtering is possible, for example, by bringing the more selective table to the front. Where appropriate, I convert subqueries into joins or TEMPORARY tables.
I can also identify poor decisions by looking at values that deviate significantly rows between theory and practice. In that case, `ANALYZE TABLE` or a histogram on the affected column can help. If even correct statistics don't get the job done, I consider using explicit hints. Before doing so, I save cross-checks and measurement values to ensure that later versions of the optimizer aren’t inadvertently hindered. Discipline in documentation pays off here.
Hosting Context and Operational Aspects
Query quality and infrastructure must be aligned; otherwise, the application is wasting its potential. Potential. Fast SSDs, consistent caches, and a clean configuration are the foundation on which the optimizer makes good decisions. High traffic leaves no room for full scans; just a few poor queries can slow down entire systems. For MySQL/MariaDB environments in production, practical tips such as MySQL Optimizer Helpful insights on combining strategy and platform. By considering this aspect, you can prevent bottlenecks before they escalate.
I always link plan analysis to metrics related to I/O, latency, and concurrency. If the values don't match the assumed cost model, I check the parameters. Then I look at buffer sizes, parallel workloads, and the distribution of hot sets. This approach allows me to run queries and manage resources smoothly and keep peak times under control.
Join and Access Paths in Practice
I clear up many misunderstandings by explaining the Types of Access carefully weigh one against the other. A range- or ref-Access almost always works ALL. For equality joins on unique keys (eq_ref) the plans are particularly stable. I also check to see if a Coverage Index fully satisfies the query: If all the required columns are included in the index, MariaDB avoids costly table lookups. Index Condition Pushdown (ICP) helps to evaluate additional WHERE conditions right in the index—which reduces the number of rows returned and I/O.
About Index Merge MariaDB can combine multiple indexes (intersection/union). This is useful for OR predicates or multiple selective conditions, but is often slower than a well-chosen composite index. I am also evaluating MRR (Multi-Range Read) and BKA (Batched Key Access). MRR sorts primary keys to be read in order to smooth out random I/O; BKA bundles join lookups and is particularly effective for non-overlapping joins. In practice, I test BKA/MRR using `optimizer_switch` and use `EXPLAIN ANALYZE` to check whether the I/O patterns decrease. If, on the other hand, MariaDB resorts to Block Nested Loop (BNL), it's usually better to increase the join buffer (join_buffer_size)—or to use a rewrite that allows for true index joins.
-- Example: Composite index for join + filter + order
CREATE INDEX ix_orders_cust_status_created
ON orders (customer_id, status, created_at);
-- Typical query
SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'open' AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 50;
Using the above index, the optimizer can choose the most selective order, evaluate filters early, and often perform the sort without requiring an additional file sort.
ORDER BY, GROUP BY, file sort, and temporary tables
Sorting and aggregating take time. I'll make sure that ORDER BY and GROUP BY can run in the order of the index. This works if the prefix and direction match exactly. Otherwise, a filesort with a sort buffer (sort_buffer_size) and, if necessary, a temporary table. If the result set contains wide TEXT/BLOB columns, MariaDB performs faster on disk TEMP tables (Aria). I take precautions by selecting only the columns I need, loading large fields only at the end, or using prefixes with length limits.
When performing aggregations, I use, whenever possible, Loose Index Scan (e.g., GROUP BY on the leading index component) and select composite indexes along the grouping. When intermediate results become large, materialization using sensible keys scales better than a single mega-join. I regularly monitor handler metrics and Created_tmp_* counters to identify sort and temp-table hotspots.
Subqueries, Semi-Joins, and Materialization
Many subqueries can be efficiently restructured during preparation. IN/EXISTS constructs can be used as Semi-Join run, using strategies such as materialization or LooseScan. I check whether the optimizer is a derived_merge was able to perform: If a derived table (or a WITH-CTE) is pushed into the outer plan, its indexes are immediately available. If that doesn't work, the subquery ends up in a temporary table—in which case, if feasible, I give it a primary key (e.g., using SELECT DISTINCT/ORDER BY on key columns) so that joins on it don't end up in limbo.
-- Example: EXISTS instead of IN and a merge-able derived table
SELECT o.id
FROM orders o
WHERE EXISTS (
SELECT 1 FROM payments p
WHERE p.order_id = o.id AND p.state = 'captured'
);
-- Derivation with distinct keys
WITH paid_orders AS (
SELECT DISTINCT order_id
FROM payments
WHERE state = 'captured'
)
SELECT o.*
FROM orders o
JOIN paid_orders po ON po.order_id = o.id;
I use EXPLAIN FORMAT=JSON to check whether materialized or dependent subquery was selected and whether conditions (condition pushdown) take action early enough.
Partitioning and Pruning
Partitioning is no substitute for indexes, but it can Amount of data per access drastically reduce. The optimizer only prunes correctly if the predicate meets the Partition Key is unambiguous and not obscured by functions. I therefore avoid expressions like DATE(created_at) in the WHERE clause on partitioned tables and work with range boundaries instead. EXPLAIN shows which partitions are read; wide ranges indicate poor pruning.
Too many small partitions increase planning overhead. I therefore choose a reasonable level of granularity (e.g., monthly instead of daily), keep statistics up to date for each partition (ANALYZE PARTITION), and check whether important indexes are present locally in the partitions. For migration projects, I factor in the impact on replication and backup—both of which influence how aggressively I partition.
Sargability and Rewrite Patterns
The simplest lever remains Sargability – Conditions that make indexes usable. I avoid functions on columns in the WHERE clause, reduce constants to the column level, and break down OR conditions into UNION ALL. A BTREE index is useless for LIKE searches without a leading anchor ("%foo"); for these, I plan to use full-text search or a suitable search service. For calculations, I use Indexed generated columns, so that the optimizer can identify the logic in the index.
-- Anti-pattern: Function on a column
WHERE DATE(created_at) = '2026-08-01'
-- Better: Range on raw value
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02'
-- Anti-pattern: OR prevents indexing
WHERE status = 'open' OR customer_id = 42
-- Better: Two queries using UNION ALL, each with its own index
(SELECT ... WHERE status = 'open')
UNION ALL
(SELECT ... WHERE customer_id = 42');
When it comes to composite indices, I consider the left prefix rule Strictly follow this rule: Sort columns by selectivity and by the sort order that will be needed later. If I need a descending ORDER BY, I take that into account in the index layout—that way, I avoid a file sort.
Optimizer Switch and Cost Fine-Tuning
Before I start working on queries, I check optimizer_switch and memory buffers. Features such as mrr, batched_key_access, index_merge, semijoin, derived_merge or condition_pushdown_for_derived can be adjusted per session. I selectively activate candidates for a test session, measure performance using EXPLAIN ANALYZE, and roll back if there is no improvement. The join path benefits from sufficient join_buffer_size; large varieties of sort_buffer_size. At the same time, I keep an eye on the buffers in relation to concurrency to ensure that the server doesn't start swapping under parallel load.
At the cost level, I adjust the previously mentioned items, if necessary optimizer_costs in microseconds. My approach: small, reversible steps with documented measurement points. I use LAST_QUERY_COST for plausibility checking and repeat measurements using realistic parameter values, because plans can depend heavily on specific literals.
Plan Stability, Regressions, and Team Workflow
Even a good plan can be disrupted by data growth or version changes tip over. That’s why I gather execution plan data: query hashes, EXPLAIN JSON, optimizer trace snippets, and EXPLAIN ANALYZE execution times. Changes to indexes and rewrites are submitted as pull requests with before-and-after evidence. In CI/CD environments, I automatically test critical queries against representative data sets. This is how I catch Regression Plans early.
For sensitive cases, I keep Tips (FORCE INDEX, STRAIGHT_JOIN, optimizer_switch per query) are available as a last resort, but use them sparingly and with an expiration date. It’s better to address the root causes—statistics, indexes, query structure. In teams, a lightweight guide to scalability, index design, and measurement discipline ensures that new features don’t introduce performance issues without anyone noticing.
Quick Overview: From Plan to Performance
Who the Plan Understanding this allows you to control performance. The parsing, preparing, optimizing, and executing phases reveal where time is lost. The time-based cost model introduced in version 11.0, along with well-maintained statistics and histograms, makes estimates reliable. EXPLAIN, EXPLAIN ANALYZE, and the Optimizer Trace provide transparency, which I translate into concrete actions. With a clean index strategy, clear query design, and appropriate infrastructure, MariaDB queries consistently deliver fast results.
