How MySQL Chooses the Cheapest Execution Plan: Cost Model Explained

MySQL Architecture Overview

MySQL consists of three layers: the service layer, the engine layer, and the file‑system layer.

SQL Execution Flow

The complete execution flow of a SQL statement is shown in the diagram below.

1. Understanding MySQL Execution Plans

Consider a table order defined as:

<code>CREATE TABLE `order` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户id',
  `order_id` bigint NOT NULL COMMENT '订单id',
  `num` int DEFAULT NULL COMMENT '订单数量',
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`user_id`) COMMENT '用户id索引'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;</code>

Test data is inserted (image omitted for brevity).

(1) Execution plan using the secondary index

<code>EXPLAIN format=tree SELECT * FROM `order` WHERE user_id = 1234;</code>

The result shows cost=0.35 , indicating the index plan.

(2) Execution plan with the secondary index disabled

<code>EXPLAIN format=tree SELECT * FROM `order` IGNORE index(idx_userId) WHERE user_id = 1234;</code>

The result (full‑table‑scan) has a higher cost.

(3) Final execution plan

<code>EXPLAIN SELECT * FROM `order` WHERE user_id = 1234;</code>

The output confirms two possible plans: an index plan (cost 0.35) and a full‑table‑scan plan (cost 0.45). MySQL selects the plan with the lower cost.

2. How MySQL Chooses an Execution Plan

The optimizer evaluates io_cost and engine_cost :

<code>cost = io_cost + engine_cost = 0.35</code>

IO cost reflects the time to read data or index blocks from disk into memory. Engine cost reflects the work the storage engine performs after data is read.

MySQL maintains two constant tables (MySQL 8.0.39 example): server_cost and engine_cost , which contain tunable parameters such as:

disk_temptable_create_cost (default 20)

disk_temptable_row_cost (default 0.5)

key_compare_cost (default 0.05)

memory_temptable_create_cost (default 1.0)

memory_temptable_row_cost (default 0.1)

row_evaluate_cost (default 0.1)

Engine‑side parameters include:

io_block_read_cost (default 1.0)

memory_block_read_cost (default 0.25)

All these values can be adjusted to influence the optimizer’s cost calculations.

Using JSON format for detailed plans

<code>EXPLAIN format=json SELECT * FROM `order` IGNORE index(idx_userId) WHERE user_id = 1234;</code>

The JSON output contains fields such as query_cost , read_cost , eval_cost , and prefix_cost , which together explain the total cost of 0.45 for the full‑table‑scan plan.

<code>{
  "query_block": {
    "select_id": 1,
    "cost_info": {"query_cost": "0.45"},
    "table": {
      "table_name": "order",
      "access_type": "ALL",
      "rows_examined_per_scan": 2,
      "rows_produced_per_join": 2,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.20",
        "prefix_cost": "0.45",
        "data_read_per_join": "64"
      },
      "used_columns": ["id", "user_id", "order_id", "num"],
      "attached_condition": "(`longxia`.`order`.`user_id` = 1234)"
    }
  }
}</code>

Key meanings:

rows_examined_per_scan : rows read during a full scan

rows_produced_per_join : rows output per join

query_cost : total cost of the query

cost_info : breakdown of costs

prefix_cost : sum of read_cost (0.25) and eval_cost (0.20)

Summary

MySQL selects the execution plan with the lowest cost value.

The server_cost table stores parameters for the server layer; the engine_cost table stores parameters for the storage‑engine layer.

Appending FORMAT=JSON to EXPLAIN provides detailed cost information that must be interpreted.

Even when an index exists, MySQL may choose a full‑table‑scan if the index’s cost exceeds the scan’s cost.