Understanding and Influencing Optimizer Decisions

See which execution plan the MariaDB query optimizer chose, understand why, and influence it — using EXPLAIN, ANALYZE, optimizer trace, and hints.

For every query, the MariaDB query optimizer chooses an execution plan: the order in which to join tables, which index (if any) to use for each table, and which optimization strategies to apply. When a query is slower than expected, the questions are usually: which plan did the optimizer choose?, why did it choose that plan?, and how do I steer it toward a better one?

MariaDB provides tools that answer these questions in increasing order of detail. A typical investigation moves through them in sequence.

1. See the plan: EXPLAIN

EXPLAIN shows the plan the optimizer intends to use, without running the query. It reports the join order, the access method chosen for each table (the type column), the index used (key), and the estimated number of rows examined (rows).

EXPLAIN

For a machine-readable plan that also includes cost estimates and the conditions attached to each table, use EXPLAIN FORMAT=JSON.

EXPLAIN FORMAT=JSON

2. Validate the estimates: ANALYZE

The row counts in EXPLAIN output are estimates. ANALYZE runs the query and reports the actual figures next to the estimates — most importantly r_rows (rows actually read) and r_filtered (the percentage of rows that actually passed the filter). A large gap between the estimated rows/filtered and the actual r_rows/r_filtered is the clearest sign that the optimizer worked from inaccurate statistics, which is a common cause of a poor plan.

ANALYZE Statement

To inspect the plan of a query that is already running in another session, use SHOW EXPLAIN.

3. See why: optimizer trace

Where EXPLAIN and ANALYZE show what the optimizer did, the optimizer trace records why: the alternative plans it considered, the cost it assigned to each, and the ones it rejected. Enable it for the session, run the query, then read the JSON trace from INFORMATION_SCHEMA.OPTIMIZER_TRACE:

SET optimizer_trace = 'enabled=on';
SELECT ...;                                    -- the query to investigate
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
SET optimizer_trace = 'enabled=off';

Optimizer Trace Overview

4. Influence the plan

Once you understand the plan, several mechanisms let you change it:

• optimizer_switch — a system variable that enables or disables individual optimizations globally or for the session.

• Optimizer hints — /*+ ... */ comments placed in the query that steer the optimizer for that single statement (for example, forcing or forbidding a join strategy or an index) without changing server-wide behavior. A hint overrides the corresponding optimizer_switch setting for that query.

• FORCE INDEX — an index hint written in the FROM clause after a table name, and a separate mechanism from the /*+ ... */ hints above. It restricts the optimizer to the indexes you list for that table and tells it to prefer one of them over a full table scan — useful when the optimizer scans a table even though you know an index would be faster. The related USE INDEX restricts the candidate indexes without discouraging a scan, and IGNORE INDEX excludes indexes instead.

• The optimizer cost model — the optimizer picks a plan by comparing estimated costs, and the constants behind those estimates are themselves configurable: what it costs to read a block from storage, to look up a key, or to evaluate the WHERE clause for one row. Engine-level costs are global and are set per storage engine (SET GLOBAL innodb.optimizer_disk_read_cost=20), while optimizer_where_cost and optimizer_scan_setup_cost can also be set per session. A change here shifts the estimates for every query, so treat it as recalibration for hardware the defaults do not fit, rather than a fix for one slow query.

For a single problem query, use an optimizer hint or FORCE INDEX. Use optimizer_switch when the behavior should change across a workload, and adjust the cost model only when the optimizer's estimates are systematically wrong for your hardware.

optimizer_switchOptimizer HintsFORCE INDEX

Queries pushed down to a smart storage engine

Some engines — MariaDB ColumnStore is the main example — can execute an entire SELECT themselves rather than returning rows for the server to join, filter, and sort. An engine advertises this by providing a select handler. When the whole statement is handed off this way, the roles change:

• The server still parses and prepares the statement. Name and column resolution happen as usual, before the server looks for an engine that can take the query. Only a top-level SELECT is eligible for full pushdown — a subquery is not pushed down on its own.

• The engine decides whether it can run the query. The server walks the query's tables looking for one whose engine offers a select handler, then asks that engine to accept the statement. Checking that the query is something the engine can actually execute — for example, that it does not mix tables from different engines — is the engine's responsibility, not the server's.

• The server's cost-based optimizer is bypassed. Once an engine accepts the query, the server does not perform its usual join-order search, index selection, or optimization-strategy selection. Deciding how to execute the query — and producing the rows — is entirely the engine's own planner. The server simply streams back the result.

Because there is no server-side plan for a pushed-down query, the tracing tools above behave differently:

For a fully pushed-down statement, EXPLAIN reports PUSHED SELECT (or PUSHED DERIVED for a pushed-down derived table) instead of a normal plan, and optimizer trace and the mechanisms in Influence the plan above do not apply to the pushed-down part — there is no server-side plan for them to describe or change. To understand or tune how such a query runs, use the engine's own tooling and documentation.

PUSHED SELECT does not always mean a ColumnStore table is involved. ColumnStore's Query Accelerator feature can push down a query against plain ENGINE=InnoDB tables — with no ColumnStore table anywhere in the query — once the session or global variable columnstore_innodb_queries_use_mcs is enabled. If EXPLAIN reports PUSHED SELECT for a query you expected to run entirely on InnoDB, check whether that variable is set before assuming a ColumnStore table caused it.

In a UNION, pushdown can be partial: branches that an engine can run are pushed down, while the remaining branches are executed by the server.

See also

• Query Optimizer — internals of how the optimizer works

• The Optimizer Cost Model from MariaDB 11.0 — how storage-engine operations are turned into comparable costs, the full list of cost variables, and how to change them