> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/understanding-and-influencing-optimizer-decisions.md).

# 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`).

{% content-ref url="/pages/OJvu3KK61oTVTVOnPMj4" %}
[EXPLAIN](/docs/server/reference/sql-statements/administrative-sql-statements/analyze-and-explain-statements/explain.md)
{% endcontent-ref %}

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

{% content-ref url="/pages/o1fDEnYq7zOtPDtp7D8U" %}
[EXPLAIN FORMAT=JSON](/docs/server/reference/sql-statements/administrative-sql-statements/analyze-and-explain-statements/explain-format-json.md)
{% endcontent-ref %}

## 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.

{% content-ref url="/pages/jZTmsYqc31EkCtLdyrPN" %}
[ANALYZE Statement](/docs/server/reference/sql-statements/administrative-sql-statements/analyze-and-explain-statements/analyze-statement.md)
{% endcontent-ref %}

{% hint style="info" %}
To inspect the plan of a query that is already running in another session, use [SHOW EXPLAIN](/docs/server/reference/sql-statements/administrative-sql-statements/show/show-explain.md).
{% endhint %}

## 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`:

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

{% content-ref url="/pages/OFkiyquYTpYvJiGF9m0C" %}
[Optimizer Trace Overview](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizer/optimizer-trace/optimizer-trace-overview.md)
{% endcontent-ref %}

## 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](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/use-index.md) restricts the candidate indexes without discouraging a scan, and [IGNORE INDEX](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/ignore-index.md) 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.

{% content-ref url="/pages/F0DXnccg4Dj1CcHNzBC8" %}
[optimizer\_switch](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimizer-switch.md)
{% endcontent-ref %}

{% content-ref url="/pages/Wy20ynQf3ooRBMi0pDrJ" %}
[Optimizer Hints](/docs/server/ha-and-performance/optimization-and-tuning/optimizer-hints.md)
{% endcontent-ref %}

{% content-ref url="/pages/cfySndh64IyPWcFB4dcr" %}
[FORCE INDEX](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/force-index.md)
{% endcontent-ref %}

## 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:

{% hint style="info" %}
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.
{% endhint %}

{% hint style="warning" %}
**`PUSHED SELECT` does not always mean a ColumnStore table is involved.** ColumnStore's [Query Accelerator](https://app.gitbook.com/o/diTpXxF5WsbHqTReoBsS/s/rBEU9juWLfTDcdwF3Q14/mariadb-columnstore/use-cases/query-accelerator.md) 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.
{% endhint %}

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](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizer.md) — internals of how the optimizer works
* [The Optimizer Cost Model from MariaDB 11.0](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizer/the-optimizer-cost-model-from-mariadb-11-0.md) — how storage-engine operations are turned into comparable costs, the full list of cost variables, and how to change them