Optimizer Architecture, Access Paths, Join Orders, Costing, and Query Transformations

Learning outcomes

ServiceHub receives a report that the same SQL statement became slower after the dataset grew, even though no application code changed. The tempting explanation is that “MariaDB suddenly stopped using the index.” That wording skips the mechanism. The query optimizer takes a declarative SQL statement, enumerates feasible access paths and join orders, estimates how many rows each step will produce, assigns costs to candidate plans, applies transformations, and selects a plan before the executor asks storage engines such as InnoDB to perform the chosen operations.

MariaDB 11.0 introduced a substantially revised cost model, so Chapter 10 treats cost as an optimizer comparison instrument rather than a stopwatch. The current model includes engine-aware costs and exposes them through information_schema.optimizer_costs, but even where cost units are calibrated toward time, an estimated cost is still not the same thing as observed wall-clock latency. Cache state, concurrency, I/O queues, locks, CPU scheduling and data distribution can all make two executions differ.

1. Build a workload where the optimizer has real choices

DROP DATABASE IF EXISTS servicehub_optimizer_lab;CREATE DATABASE servicehub_optimizer_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;USE servicehub_optimizer_lab;CREATE TABLE digits (d TINYINT UNSIGNED PRIMARY KEY) ENGINE=InnoDB;INSERT INTO digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);CREATE TABLE customers (  customer_id INT UNSIGNED NOT NULL PRIMARY KEY,  region_code CHAR(3) NOT NULL,  tier ENUM('standard','premium','enterprise') NOT NULL,  active TINYINT(1) NOT NULL,  KEY idx_customer_region_tier(region_code,tier,customer_id)) ENGINE=InnoDB;INSERT INTO customers(customer_id,region_code,tier,active)SELECT n,       CASE WHEN MOD(n,10)<7 THEN 'BAK' WHEN MOD(n,10)<9 THEN 'GAN' ELSE 'TBZ' END,       CASE WHEN MOD(n,20)=0 THEN 'enterprise' WHEN MOD(n,5)=0 THEN 'premium' ELSE 'standard' END,       1FROM (  SELECT 1 + a.d + 10*b.d + 100*c.d AS n  FROM digits a CROSS JOIN digits b CROSS JOIN digits c) s WHERE n<=900;CREATE TABLE work_orders (  work_order_id INT UNSIGNED NOT NULL PRIMARY KEY,  customer_id INT UNSIGNED NOT NULL,  status VARCHAR(16) NOT NULL,  priority TINYINT UNSIGNED NOT NULL,  region_code CHAR(3) NOT NULL,  scheduled_at DATETIME NOT NULL,  total_cents INT UNSIGNED NOT NULL,  summary VARCHAR(120) NOT NULL,  CONSTRAINT fk_opt_wo_customer FOREIGN KEY(customer_id) REFERENCES customers(customer_id),  KEY idx_status_region_sched(status,region_code,scheduled_at,customer_id),  KEY idx_customer_sched(customer_id,scheduled_at)) ENGINE=InnoDB;INSERT INTO work_orders(work_order_id,customer_id,status,priority,region_code,scheduled_at,total_cents,summary)SELECT n,       1+MOD(n,900),       CASE WHEN MOD(n,100)<95 THEN 'closed' WHEN MOD(n,100)<99 THEN 'open' ELSE 'escalated' END,       1+MOD(n,4),       CASE WHEN MOD(n,10)<7 THEN 'BAK' WHEN MOD(n,10)<9 THEN 'GAN' ELSE 'TBZ' END,       TIMESTAMP('2026-01-01 00:00:00') + INTERVAL MOD(n,200) DAY + INTERVAL MOD(n,24) HOUR,       5000 + MOD(n*137,120000),       CONCAT('ServiceHub work order ',n)FROM (  SELECT 1 + a.d + 10*b.d + 100*c.d + 1000*e.d AS n  FROM digits a CROSS JOIN digits b CROSS JOIN digits c CROSS JOIN digits e) s WHERE n<=10000;CREATE TABLE work_order_events (  event_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  work_order_id INT UNSIGNED NOT NULL,  event_type VARCHAR(24) NOT NULL,  created_at DATETIME NOT NULL,  note VARCHAR(120) NULL,  KEY idx_event_order_type(work_order_id,event_type,created_at),  CONSTRAINT fk_opt_event_order FOREIGN KEY(work_order_id) REFERENCES work_orders(work_order_id)) ENGINE=InnoDB;INSERT INTO work_order_events(work_order_id,event_type,created_at,note)SELECT work_order_id,       CASE WHEN MOD(work_order_id,5)=0 THEN 'reopened' ELSE 'status_change' END,       scheduled_at + INTERVAL 1 HOUR,       'optimizer lab event'FROM work_orders WHERE MOD(work_order_id,3)=0;

The work-order table has a skewed status distribution: roughly 95% closed, 4% open and 1% escalated. It also has two useful secondary indexes. This creates a realistic optimizer problem: a predicate on status='closed' is weakly selective, while status='escalated' is selective. The same physical index can therefore be attractive for one constant and unattractive for another.

EXPLAINSELECT work_order_id,customer_id,scheduled_atFROM work_ordersWHERE status='escalated' AND region_code='BAK'ORDER BY scheduled_atLIMIT 25;EXPLAINSELECT work_order_id,customer_id,scheduled_atFROM work_ordersWHERE status='closed' AND region_code='BAK'ORDER BY scheduled_atLIMIT 25;SHOW INDEX FROM work_orders;

A plan is a hypothesis about row flow. possible_keys lists indexes the optimizer can consider for the access conditions; key is the chosen index; rows is an estimate, not a count produced by the executor. If a broad predicate causes a scan, that is not automatically an optimizer bug—the cost model may estimate that touching a large fraction of secondary-index entries plus base rows costs more than scanning the clustered table.

2. Access path, selectivity, cardinality and cost are different concepts

The optimizer combines multiple estimates. For a join it must decide not only how to access each table but also which table should be first. Starting with a highly selective input can reduce downstream lookups; starting with the wrong input can multiply work. MariaDB’s optimizer can reorder inner joins, transform subqueries, merge or materialize derived tables, push conditions closer to their data source and consider index/order interactions. These are not separate “tricks”—they all change estimated row flow and therefore candidate cost.

3. Join order is multiplicative

EXPLAIN FORMAT=JSONSELECT c.customer_id,c.tier,w.work_order_id,w.total_centsFROM customers AS cJOIN work_orders AS w ON w.customer_id=c.customer_idWHERE c.region_code='BAK'  AND c.tier='enterprise'  AND w.status='open';ANALYZE FORMAT=JSONSELECT c.customer_id,c.tier,w.work_order_id,w.total_centsFROM customers AS cJOIN work_orders AS w ON w.customer_id=c.customer_idWHERE c.region_code='BAK'  AND c.tier='enterprise'  AND w.status='open';

The useful question is not “which table did MariaDB put first?” but “why does that order reduce expected downstream work?” If the customer predicate yields only a small set, probing work orders by customer may be cheap. If the open-work-order predicate is more selective, the reverse can win. Read the JSON plan as a tree of row-producing operations and compare estimates with the r_* runtime fields taught in Lesson 2.

4. Transformations can change the shape before costing

SQL text is not a literal execution recipe. MariaDB can transform IN/EXISTS subqueries into semijoins, merge some derived tables into the outer query, materialize others, push outer predicates into derived tables or apply index-condition pushdown. The exact set of enabled strategies is visible in @@optimizer_switch. From MariaDB 12.0 the default switch set includes strategies such as derived_merge, firstmatch, loosescan, duplicateweedout, materialization, semijoin, subquery_cache and condition-pushdown options.

SELECT @@optimizer_switch\GSELECT * FROM information_schema.optimizer_costsWHERE engine IN ('DEFAULT','InnoDB');SET optimizer_trace='enabled=on';SELECT c.customer_idFROM customers cWHERE EXISTS (  SELECT 1 FROM work_orders w  WHERE w.customer_id=c.customer_id AND w.status='escalated');SELECT QUERY,TRACE,MISSING_BYTES_BEYOND_MAX_MEM_SIZEFROM information_schema.OPTIMIZER_TRACE\GSET optimizer_trace='enabled=off';

Optimizer trace is particularly valuable when the question is “why was another plan rejected?” It records optimization decisions for the last traced statement in the current connection. It is not a permanent telemetry store, and large traces can be truncated according to optimizer_trace_max_mem_size. Use it as a focused diagnostic artifact, not an always-on production log.

5. Deliberately wrong: change global cost variables until the plan looks familiar

Because MariaDB exposes optimizer-cost variables, an operator can be tempted to “fix” a regression by globally inflating scan or disk-read cost until an index plan appears. That is dangerous: one changed cost can affect thousands of unrelated statements and may encode a symptom rather than the real cause. The 11.0+ cost model is engine-aware and already calibrated around modern storage assumptions; changing it is an expert-level operation that requires controlled benchmarking and rollback.

-- Record evidence first; do not change global optimizer costs as a first response.SELECT VERSION(),@@optimizer_switch;SHOW INDEX FROM work_orders;ANALYZE TABLE work_orders;EXPLAIN FORMAT=JSONSELECT * FROM work_orders WHERE status='escalated' AND region_code='BAK';ANALYZE FORMAT=JSONSELECT * FROM work_orders WHERE status='escalated' AND region_code='BAK';

Repair the investigation in layers: confirm schema/index definitions, check row-distribution changes, refresh statistics when justified, compare estimate versus runtime, inspect transformations, and only then run a narrowly scoped experiment such as a session-level switch or query hint. A forced familiar plan is not evidence that the forced plan is correct.

6. Production judgment and bridge

1. Capture the exact SQL, parameter class, server version, schema/index definitions and optimizer settings.
2. Separate estimated cardinality/cost from observed runtime.
3. Check whether the regression correlates with data skew, stale statistics, schema changes or an upgrade.
4. Use optimizer trace only for focused questions about rejected alternatives.
5. Prefer session/query-scoped experiments before global optimizer changes.
6. Keep a rollback path for any statistics, index or optimizer-policy change.

Lesson 2 now turns the plan hypothesis into executed evidence and establishes the safety boundary between EXPLAIN and ANALYZE.