Understanding MySQL EXPLAIN FORMAT=JSON Cost Details and Calculations

EXPLAIN FORMAT=JSON can print detailed execution plan costs; the following examples show how to view the cost output and calculate the costs.

Table structures:

mysql> show create table sbtest1\G
*************************** 1. row ***************************
        Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` varchar(90) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4316190 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

##注意sbtest3无主键
mysql> show create table sbtest3\G
*************************** 1. row ***************************
        Table: sbtest3
Create Table: CREATE TABLE `sbtest3` (
  `id` int(11) NOT NULL,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` varchar(66) COLLATE utf8mb4_bin DEFAULT NULL,
  KEY `k_3` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

Example 1

mysql> explain format=json select * from sbtest3 where id<100 and k<200\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "26.21"            ##查询总成本
    },
    "table": {
      "table_name": "sbtest3",        ##表名
      "access_type": "range",         ##访问数据的方式是range，即索引范围查找
      "possible_keys": [
        "k_3"
      ],
      "key": "k_3",                   ##使用索引
      "used_key_parts": [
        "k"
      ],
      "key_length": "4",
      "rows_examined_per_scan": 18,   ##扫描 k_3 索引的行数：18（满足特定条件时使用index dive可得到真实行数）
      "rows_produced_per_join": 5,    ##在扫描索引后估算满足id<100条件的行数：5
      "filtered": "33.33",            ##在扫描索引后估算满足其他条件id<100的数据行占比
      "index_condition": "(`sbtest`.`sbtest3`.`k` < 200)",      ##索引条件
      "cost_info": {
        "read_cost": "25.01",          ##这里包含了所有的IO成本+部分CPU成本
        "eval_cost": "1.20",           ##计算扇出的CPU成本
        "prefix_cost": "26.21",        ##read_cost+eval_cost
        "data_read_per_join": "4K"
      },
      "used_columns": [
        "id",
        "k",
        "c",
        "pad"
      ],
      "attached_condition": "(`sbtest`.`sbtest3`.`id` < 100)"
    }
  }
}

eval_cost

This is the CPU cost of the fan‑out. With condition k<200 the optimizer scans 18 index rows, then estimates that 33.33% satisfy id<100, so the CPU cost is 18*33.33%*0.2=1.2, where 0.2 is the row_evaluate_cost constant.

Note: rows_examined_per_scan * filtered is the fan‑out count, not rows_produced_per_join.

read_cost

Read cost includes all I/O cost plus (CPU cost – eval_cost). The I/O cost to read the index range is 1*1.0 (one page). The CPU cost for scanning 18 rows is 18*0.2. Because the query needs the full row (SELECT * and id<100), a table‑lookup (back‑table) is required, costing 18*1.0 I/O and 18*0.2 CPU. Summing these gives a total cost of 1*1.0+18*0.2+18*1+18*0.2=26.2. The read_cost can also be expressed as rows_examined_per_scan*(1-filtered)*0.2.

Example 2

mysql> explain format=json select t1.id from sbtest1 t1 join sbtest3 t3 \
on t1.id=t3.id and t3.k<200 and t3.id<100\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "33.41"              ##查询总成本
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "t3",               ##t3是驱动表
          "access_type": "range",            ##访问数据的方式是range，即索引范围查找
          "possible_keys": [
            "k_3"
          ],
          "key": "k_3",                     ##使用的索引：k_3
          "used_key_parts": [
            "k"
          ],
          "key_length": "4",
          "rows_examined_per_scan": 18,      ##k_3索引扫描行数：18
          "rows_produced_per_join": 5,       ##（估算值）扫描索引18行后，满足条件id<200的行数
          "filtered": "33.33",               ##（估算值）扫描索引18行后，满足条件id<200的数据占扫描行数的比例，即驱动表扇出
          "index_condition": "(`sbtest`.`t3`.`k` < 200)",
          "cost_info": {
            "read_cost": "25.01",            ##这里包含了所有的IO成本+部分CPU成本
            "eval_cost": "1.20",             ##计算扇出的CPU成本
            "prefix_cost": "26.21",          ##驱动表的总成本：read_cost+eval_cost
            "data_read_per_join": "4K"
          },
          "used_columns": [
            "id",
            "k"
          ],
          "attached_condition": "(`sbtest`.`t3`.`id` < 100)"
        }
      },
      {
        "table": {
          "table_name": "t1",               ##t1为被驱动表
          "access_type": "eq_ref",          ##关联查询时访问驱动表方式是通过主键或唯一索引的等值查询
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",                 ##使用索引为主键
          "used_key_parts": [
            "id"
          ],
          "key_length": "4",
          "ref": [
            "sbtest.t3.id"
          ],
          "rows_examined_per_scan": 1,        ##关联查询时，每次扫描被驱动表1行数据（使用主键）
          "rows_produced_per_join": 5,        ##被驱动表需要查询的次数，不是准确的驱动表扇出数
          "filtered": "100.00",               ##满足关联条件数据占扫描行数的比例，被驱动表上看这个没啥意义
          "using_index": true,
          "cost_info": {
            "read_cost": "6.00",            ##单次查询被驱动表的IO成本*驱动表扇出数。6*1.0=6，1.0为成本常数
            "eval_cost": "1.20",            ##单次查询被驱动表的CPU成本*驱动表扇出数。6*0.2=1.2，0.2位成本常数
            "prefix_cost": "33.41",          ##查询总成本=驱动表的总成本+被驱动表的(read_cost+eval_cost)
            "data_read_per_join": "5K"
          },
          "used_columns": [
            "id"
          ]
        }
      }
    ]
  }
}

The total join cost can be simplified as:

join_total_cost = cost_of_accessing_driver_table + (driver_fan_out * cost_of_one_access_to_driven_table)

In the example, driver cost = 26.21, driver fan‑out = 18*33.33% = 6, one‑access cost = 1.0 (I/O) + 0.2 (CPU), so total cost = 26.21 + 6*(1.0+0.2) = 33.41.

Note: read_cost and eval_cost represent different kinds of costs for driver and driven tables.

Related articles: EXPLAIN Execution Plan Details 1 , EXPLAIN Execution Plan Details (2) – Extra .