Skip to content 
With the optimizer trace in MariaDB, I can understand, step by step, why the optimizer chooses a particular plan and which alternatives it rejects. This JSON trace shows me Decisions on costs, join orders, and filters, so that I can tailor SQL queries to my specific needs.
Key points
- Transparency: A JSON-based trace explains rewrites, costs, and discarded plans.
- Focus: `join_preparation` and `join_optimization` provide the most important insights.
- Control system: Session variables limit overhead and memory usage.
- Workflow: EXPLAIN/ANALYZE for the execution plan, TRACE for the „why.“.
- Practical benefits: Make informed adjustments to indexes, statistics, and join orders.
What is the MariaDB Optimizer Trace?
Since version 10.4, MariaDB has included a Optimizer Trace, which documents each major optimization phase of a SELECT, UPDATE, or DELETE statement as JSON. It shows me how the engine expands queries, normalizes conditions, and ultimately determines the join order along with index accesses. This insight goes significantly deeper than EXPLAIN, which primarily shows the final plan, and reveals discarded alternatives along with the reasons for their rejection. The trace is stored in memory for each connection and is available via information_schema.OPTIMIZER_TRACE ready. This gives me a complete, machine-readable explanation of the internal Steps, which led to an implementation plan.
Enable and Read the Optimizer Trace
I enable this feature on a per-session basis so that I can run diagnostics without global overhead and maintain full control over Memory I have. Typically, I set SET SESSION optimizer_trace = 'enabled=on'; and if required SET SESSION optimizer_trace_max_mem_size = 1048576; or higher if the trace becomes extensive. Then I run the suspicious query and read the trace using SELECT * FROM information_schema.OPTIMIZER_TRACE LIMIT 1\G;. Important: The table stores only the last query from the active connection, and I take into account fields such as MISSING_BYTES_BEYOND_MAX_MEM_SIZE or INSUFFICIENT_PRIVILEGES for diagnostic information. This approach keeps the production environment lean and makes analysis accurate.
| Variable/Field | Purpose | Example value |
|---|---|---|
optimizer_trace | Enables tracing per session | 'enabled=on' |
optimizer_trace_max_mem_size | Maximum memory per trace | 1048576 (1 MB) |
OPTIMIZER_TRACE.QUERY | Original SQL statement | SELECT ... |
OPTIMIZER_TRACE.TRACE | JSON Document for Optimization | JSON text |
MISSING_BYTES_BEYOND_MAX_MEM_SIZE | Bytes Truncated When the Trace Is Too Long | 0 or quantity |
INSUFFICIENT_PRIVILEGES | Is read-only access sufficient? | 0 or 1 |
JSON Structure: join_preparation and join_optimization
The JSON structure is divided into the following blocks: join_preparation and join_optimization, which I look through first because they're the most important Notes deliver. In the section join_preparation I recognize the extended query (expanded_query) and see if and how the engine has transformed conditions or projections. The second block join_optimization logs row estimates, the plans considered, the chosen join order, and the appending of selective WHERE clauses to tables. The subtrees are particularly useful rows_estimation, considered_execution_plans and attaching_conditions_to_tables, because they refer directly to cost assumptions and filter positions. This allows me to quickly identify where there are misjudgments or unfavorable Indices lead to suboptimal plans.
Comparison Using EXPLAIN and ANALYZE
For a complete evaluation, I combine EXPLAIN, ANALYZE, and the Trace in a set sequence. First, I use EXPLAIN or EXPLAIN FORMAT=JSON, to view the selected plan and key paths. Then I set EXPLAIN ANALYZE to obtain actual runtime data and count values such as loops and filtered rows. If I still have questions, I enable the optimizer trace and review which variants the optimizer evaluated and rejected. This article provides a concise introduction to interpreting the results: Understanding EXPLAIN ANALYZE, which I consult as needed for additional information.
Understanding Planning Decisions: Costs, Cardinalities, Filters
The decision-making logic is based on cardinalities, cost models, and the placement of Filter following the plan. In the trace, I can see, for each join order under consideration, which row sets the engine expects and how it derives the total cost from them. I check whether outdated statistics or unfavorable correlations cause range scans to be underestimated and full scans to be preferred. I also check whether the engine applies WHERE conditions to the most selective table early enough to minimize expensive join steps. This allows me to make well-founded conclusions about why a particular plan was chosen and how I can optimize it with Indices, rewrites, or statistics maintenance.
Practical Example: Trace of a Simple Filter Query
At SELECT * FROM t1 WHERE a < 10 I check under join_preparation, whether the engine has expanded the projection and possibly consolidated conditions, which gave me an initial Indicators returns. After that, I see in the block rows_estimation, how many lines the engine uses for range scanning on a compared to a full-table scan. If I find unrealistic values, I often interpret this as a sign of outdated statistics or missing histograms. In the section considered_execution_plans I can then see whether the index access was actually calculated to be more efficient than a full scan. Finally, it shows attaching_conditions_to_tables, whether the selective condition applies to a takes effect early in the schedule, which significantly reduces the duration lowers.
JSON Functions: Extracting Specific Snippets
Since the trace is in JSON format, I filter specific subtrees using JSON_EXTRACT and create small reports for recurring Sample. For example, I simply read out the list of considered plans to check whether certain join sequences consistently fail. I also extract cost fields from the top candidates and compare them with ANALYZE data to identify false assumptions. I automate these checks for my diagnostic sessions using simple views or stored procedures. This way, I build myself an easy Monitoring for optimizer decisions without enabling persistent tracing.
Typical Use Cases and Benefits
I use a trace when EXPLAIN shows an unexpected full scan and I want to find out why a Index I want to find out. Likewise, for many tables, the trace provides me with the rationale behind the chosen join order, which helps me identify alternative execution plans. When switching versions, I save traces before and after the update to assess changes in the optimizer’s behavior. For strategic tuning questions, this overview helps me to internal optimizer mechanisms, which I associate with trace results. This allows me to make a structured decision on whether to adjust indexes, statistics, or query formulations to adjusting screw set.
Best Practices for Production
I consistently enable tracing as Session-Stop and cleanly exit the diagnostic process as soon as I have enough data. For large traces, I increase optimizer_trace_max_mem_size I only do this temporarily and then set the value back to a low level. Before I share JSON files, I mask sensitive constants, comment text, or business metrics. I use the trace specifically as a diagnostic tool, while for continuous monitoring I prefer slow-query logs, performance views, or external profilers. This discipline keeps systems lean and prevents unnecessary Overhead in day-to-day business.
Optimizer Trace in the Tool Mix
For a holistic approach to tuning, I map out the chain consisting of understanding the plan, analyzing the causes, and measuring the system, and link the Findings. EXPLAIN shows me the plan, ANALYZE confirms the actual costs, and the trace provides the rationale behind the decision. At the same time, I’m looking at query execution plan concepts to identify patterns in key selection, cardinalities, and join strategies. A good complement to this perspective is the concise overview of Query Execution Plans, which I consult when I have questions about architecture. From this, I derive reliable Priorities for indexing, rewrites, and parameters.
Dive Deeper: Range Analysis and Key Selection
There is often a block in the trace range_analysis for each table, where I can identify which indexes were candidates for range, ref, or EQ-ref accesses. The optimizer compares alternatives such as „range on idx_a,“ „range on idx_b,“ or „full scan,“ assigns costs and expected rows to them, and selects the winner. If I see that a useful index was rejected due to high costs, I next look at the underlying selectivities and statistics. If the assumptions are incorrect, a ANALYZE TABLE (with persistent statistics, if applicable) or the creation of a more targeted Coverage Index overturn the decision.
It’s also helpful to look at splits for composite indexes: The trace shows whether the condition uses only the first column of the index or whether additional predicates are searchable, making other key columns effective. Based on this, I determine whether to reformulate predicates (for example, by avoiding functions) or to extend the index so that typical filters and sorts are covered.
Joins in Detail: Semijoins, BKA/MRR, and Join Buffers
For multi-table queries, the trace sections show whether and which semijoin strategy was considered (e.g., FirstMatch, DuplicateWeedout, LooseScan, or Materialization). I can see there why a variant was rejected—for example, due to high materialization costs or insufficient selectivity. Also Batched Key Access (BKA) and Multi-Range Read (MRR) appear in the trace, if enabled. These techniques bundle key lookups and improve cache locality. If BKA/MRR do not appear in the trace, I check optimizer_switch and parameters such as join_cache_level. In workloads with many random key lookups, this can noticeably speed up the join phase, which can be verified using `EXPLAIN ANALYZE`.
The size and type of the join buffer are also crucial: The trace reveals whether nested-loop variants were executed with or without a buffer and at what point filters are applied. I evaluate whether adding indexes on join keys or rewriting the query to reduce the number of intermediate results is a more efficient choice than simply increasing buffer sizes.
Subqueries, Derived Tables, and Views
At join_preparation I think that whether subqueries in EXISTS/IN form in Semijoins were transformed (in_to_exists), whether derived tables are merged (derived_merge) or were materialized, and whether Condition Pushdown all the way to derived tables. These steps are crucial because a missing merge can lead to costly materialization. If I see repeated materialization decisions with high costs in the trace, I test whether an explicit STRAIGHT_JOIN, a hint or a reorganization of the query (e.g., common table expressions with targeted filters) prompts the engine to use a more efficient strategy. For views, I check whether the optimizer sufficiently resolves the view's contents or whether additional indexes are missing in the underlying table.
Partitioning and Pruning
For partitioned tables, the trace shows which partitions were excluded based on partition keys and predicates (Partition Pruning). If expected pruning does not occur, this is a signal to formulate filters earlier and in a way that allows for pruning based on the partition key. I also pay attention to the interaction between partitioning and indexes: If local or global indexes are missing, the engine may examine an excessive number of rows despite pruning, which is evident in the trace as high scan costs.
Verify Hints, Index Specifications, and `optimizer_switch` in a Targeted Manner
I use the trace to see the effect of hints and parameter switches on prove. For example, if I set. FORCE INDEX or an optimizer hint, I can see in the trace whether the alternative was actually forced and how it was evaluated. Via optimizer_switch I can temporarily enable or disable strategies (e.g., for SEMIJOIN, INDEX_MERGE, or DERIVED_MERGE decisions). The trace then serves as evidence to show whether the engine accepted the specifications or whether other restrictions (e.g., cardinalities) continue to take precedence. Optionally, I use formatting flags such as one_line or end_markers at optimizer_trace-String to customize the readability for my analysis tool.
Update/DELETE and Write Paths
The optimizer trace isn't limited to SELECT statements. For UPDATE and DELETE statements, I can also see how access paths are chosen and whether filters take effect early enough to keep the number of affected rows low. I check whether a WHERE filter is non-sargable or whether a missing index leads to a broad scan phase before the actual change is executed. From the trace, I determine whether a compact index (e.g., containing only the necessary columns) avoids unnecessary back-and-forth accesses, thereby reducing locks and log volume.
Security, Privileges, and Prepared Statements
In order to read the entire track, I need sufficient object privileges—if they are missing, the field indicates INSUFFICIENT_PRIVILEGES Restrictions. In production-like scenarios, I therefore use the same logins as the application or a specially authorized diagnostic account. For prepared statements, the trace typically shows the optimized form with parameters already bound, which allows me to evaluate selectivity without revealing sensitive constants. If I need to share traces, I mask parameter values or replace them with representative ranges to comply with data protection requirements.
Automation: Capture, Categorize, and Document Traces
To ensure reproducible analyses, I store traces on a random basis in a diagnostic table and tag them with metadata such as schema, version, session variables, and timestamps. This allows me to compare results before and after index changes or version upgrades diffen, which decisions have been postponed. It is practical to group the blocks considered_execution_plans and rows_estimation store them separately so I can quickly compare cost changes. Smaller helper queries extract the selected join order and calculated costs—for example, using JSON_EXTRACT(TRACE, '$.join_optimization.considered_execution_plans') – and save the results alongside the EXPLAIN and ANALYZE output. This creates reliable documentation for each tuning step.
Limitations, Version-Specific Features, and Comparison with MySQL
The trace's key structures are based on MySQL; however, details and field names may vary slightly depending on the MariaDB version. I will therefore focus on the semantic Sections (rewrites, row estimates, plans considered, condition attachments), rather than letting cosmetic differences throw me off. Important: In MariaDB, the focus is on the last statement of the active connection. Anyone analyzing many consecutive statements should therefore read them immediately after execution or automatically via a hook, so that no relevant traces are overwritten. For very large JSONs, I factor in the memory requirements and understand MISSING_BYTES_BEYOND_MAX_MEM_SIZE as an invitation to temporarily raise the limit and run the analysis again.
Practical JSON Extractions for Everyday Use
To wrap things up, here are a few concise excerpts that I often use in practice to get straight to the point:
- Selected join order and candidate lists: I extract the plan prefixes and the corresponding appended tables so that I can trace the decision sequence.
- Range Alternatives and Costs: I extract the list of evaluated indexes for the most selective tables in order to accurately evaluate rewrites or new indexes.
- Filters applied early on: I'm reading the
attaching_conditions_to_tables-sections to ensure that strong predicates are located as close as possible to the data source.
With just a few views for these extractions, I have a streamlined „lens“ for optimizer decisions that I turn on as needed during diagnostic sessions and then turn off again afterward.
Frequent stumbling blocks and troubleshooting
If histograms are missing or statistics are outdated, estimates will be inaccurate and result in Plans with unnecessary full scans. If I see significantly varying cardinalities in the trace, I update statistics, create appropriate indexes, or rewrite filters to make them Sargable. I identify traces that are too brief by MISSING_BYTES_BEYOND_MAX_MEM_SIZE and respond by temporarily raising the limit. If ANALYZE yields better execution times for an alternative path, I check the trace to see which cost factor favored the chosen variant. This way, I fill in the gaps in my knowledge step by step and achieve Clarity about the decision-making logic.
Briefly summarized
The MariaDB Optimizer Trace explains to me, in a JSON document, how the engine transforms queries, estimates rows, compares plans, and ultimately selects a Sequence selects. I enable it for each session, read the track, check join_preparation and join_optimization and apply these insights using EXPLAIN/ANALYZE. Based on the reasons for rejected indexes, late filters, or incorrect estimates, I derive specific steps: better indexes, more up-to-date statistics, and clearly formulated queries. Using JSON functions, I extract relevant data, identify patterns, and document decisions in a reproducible manner. This allows me to handle even extensive SQL workloads reliably Performance and make tuning decisions transparent.
