タイトルが何を言っているのか意味不明ですが、MySQL 8.0では（正確にはおそらくMySQL 8.0.1 DMRから）、オプティマイザのコスト計算で、

ストレージからデータを読み取る場合
バッファプールにあるデータを読み取る場合

のそれぞれの実行コストを区別し、デフォルトで後者（バッファプール読み取り）を低コストと見積もるようになりました。
ので、ちょっとだけ試してみます。

ネタ元はこちらです。

MySQL 8.0: Query Optimizer Takes Data Buffering into Account（MySQL Server Blog）
8.9.5 The Optimizer Cost Model（MySQL 8.0 Reference Manual）

※MySQL Casual Advent Calendar 2017開催中ですが、個人的に枠を取りすぎたので、こちらは通常記事です…。

1. 検証内容

140万レコードを持つほぼ同じ構造のテーブルが2つ（「flag_test_h」と「flag_test_h2」）
「flag_test_h」には「flag=1」の行が1,600行
「flag_test_h2」には「flag=1」の行が3,600行
両テーブルで同じ主キー（id）の行を**「flag=1」を条件に内部結合すると280行**

という状況で、

両テーブルのデータがバッファプールに載っていないケースと、**「flag_test_h」**が全行バッファプールに載っているケースで実行計画はどう変わる？（駆動表／内部表）
コストモデルのテーブル**「mysql.engine_cost」**でコスト係数を変えたら？

各テーブルの定義、内容等は以下の通りです（使い回しです。すみません）。

テーブル定義・内容等

mysql> USE bp_opt_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SHOW CREATE TABLE flag_test_h\G
*************************** 1. row ***************************
       Table: flag_test_h
Create Table: CREATE TABLE `flag_test_h` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `str` varchar(100) DEFAULT NULL,
  `flag` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `flag` (`flag`)
) ENGINE=InnoDB AUTO_INCREMENT=1400001 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE flag_test_h2\G
*************************** 1. row ***************************
       Table: flag_test_h2
Create Table: CREATE TABLE `flag_test_h2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `str` varchar(100) DEFAULT NULL,
  `flag` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `flag` (`flag`)
) ENGINE=InnoDB AUTO_INCREMENT=1400001 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM flag_test_h;
+----------+
| COUNT(*) |
+----------+
|  1400000 |
+----------+
1 row in set (2.48 sec)

mysql> SELECT COUNT(*) FROM flag_test_h WHERE flag = 1;
+----------+
| COUNT(*) |
+----------+
|     1600 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM flag_test_h2;
+----------+
| COUNT(*) |
+----------+
|  1400000 |
+----------+
1 row in set (2.48 sec)

mysql> SELECT COUNT(*) FROM flag_test_h2 WHERE flag = 1;
+----------+
| COUNT(*) |
+----------+
|     3600 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1;
+----------+
| COUNT(*) |
+----------+
|      280 |
+----------+
1 row in set (0.00 sec)

コストモデルのテーブルのデフォルト値は以下の通りです（今回は**「mysql.engine_cost」**テーブルの値を途中で変更します）。

コストモデルのテーブル初期値

mysql> SELECT * FROM mysql.server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name                    | cost_value | last_update         | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost   |       NULL | 2017-09-29 14:27:27 | NULL    |            20 |
| disk_temptable_row_cost      |       NULL | 2017-09-29 14:27:27 | NULL    |           0.5 |
| key_compare_cost             |       NULL | 2017-09-29 14:27:27 | NULL    |          0.05 |
| memory_temptable_create_cost |       NULL | 2017-09-29 14:27:27 | NULL    |             1 |
| memory_temptable_row_cost    |       NULL | 2017-09-29 14:27:27 | NULL    |           0.1 |
| row_evaluate_cost            |       NULL | 2017-09-29 14:27:27 | NULL    |           0.1 |
+------------------------------+------------+---------------------+---------+---------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment | default_value |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| default     |           0 | io_block_read_cost     |       NULL | 2017-12-08 11:48:31 | NULL    |             1 |
| default     |           0 | memory_block_read_cost |       NULL | 2017-12-08 11:59:10 | NULL    |          0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
2 rows in set (0.00 sec)

ストレージ（ディスク）からの読み取り係数1.0に対し、バッファプール（メモリ）からの読み取り係数は0.25（1/4）です。ざっくり言ってメモリはストレージの4倍高速とみなしている、と考えると良いでしょう。

なお、テストの際、サーバ再起動でバッファプールがウォームアップしないように設定しています。

2. 結果

2-1. 両テーブルがバッファプールに載っていないケース

先に示した条件で結合すると、駆動表（外部表）の1行に対応する内部表の行のフェッチに主キーが使える（ので行フェッチのコストは小さい）⇒外側の「ぐるぐる」（ループ）回数を減らすほうがコストが小さくなることから、**「flag_test_h」が駆動表（外部表）**になります。

両テーブルがバッファプールに載っていないケース

mysql> EXPLAIN SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | h     | NULL       | ref    | PRIMARY,flag  | flag    | 5       | const            | 1600 |   100.00 | Using index |
|  1 | SIMPLE      | h2    | NULL       | eq_ref | PRIMARY,flag  | PRIMARY | 4       | bp_opt_test.h.id |    1 |     5.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

mysql> SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1;
+---------+
| id      |
+---------+
|       1 |
|       2 |
|       3 |
（中略）
| 1380000 |
| 1390000 |
| 1400000 |
+---------+
280 rows in set (0.10 sec)

※オプティマイザトレースの結果は後ほど。

2-2. 「flag_test_h」テーブルがバッファプールに載っているケース

一旦MySQLサーバを再起動してバッファプールを開放し（前述の通りウォームアップ無効）、今度はテーブルチェックサムを取得して「flag_test_h」テーブルのデータをバッファプールに載せます。
すると、「flag_test_h」テーブルの検索コストが小さくなり、「flag_test_h」テーブルを内部表にしたほうがトータルコストが低くなるとオプティマイザが判断したため、駆動表と内部表が入れ替わりました（⇒内部表検索1回あたりのコストが小さい＝駆動表のループ回数が増えてもトータルコストが小さくなる、と判断した）。

「flag_test_h」テーブルがバッファプールに載っているケース

mysql> USE bp_opt_test;
No connection. Trying to reconnect...
Connection id:    7
Current database: *** NONE ***

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CHECKSUM TABLE flag_test_h;
+-------------------------+------------+
| Table                   | Checksum   |
+-------------------------+------------+
| bp_opt_test.flag_test_h | 2122123514 |
+-------------------------+------------+
1 row in set (2.48 sec)

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
（中略）
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 687144960
Dictionary memory allocated 389263
Buffer pool size   40960
Free buffers       28637
Database pages     12318
Old database pages 4567
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 12280, created 38, written 51
280.83 reads/s, 0.02 creates/s, 1.19 writes/s
Buffer pool hit rate 937 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 273.85/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 12318, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
（中略）
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.01 sec)

mysql> EXPLAIN SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | h2    | NULL       | ref    | PRIMARY,flag  | flag    | 5       | const             | 3600 |   100.00 | Using index |
|  1 | SIMPLE      | h     | NULL       | eq_ref | PRIMARY,flag  | PRIMARY | 4       | bp_opt_test.h2.id |    1 |     5.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1;
+---------+
| id      |
+---------+
|       1 |
|       2 |
|       3 |
（中略）
| 1380000 |
| 1390000 |
| 1400000 |
+---------+
280 rows in set (0.01 sec)

2-3. コストモデルのテーブル「mysql.engine_cost」でコスト係数を変えたら

MySQL 8.0（8.0.1 DMR以降）では、コストモデルのテーブルのうち、**「mysql.engine_cost」**のレコードを書き換えることで、ストレージ（ディスク）上のデータの読み取りコスト係数とバッファプール（メモリ）上のデータの読み取りコスト係数を変更することができます。

先のケースと同じように、「flag_test_h」テーブルのデータをバッファプールに載せます。

先の例と同じ状態にする

mysql> USE bp_opt_test;
No connection. Trying to reconnect...
Connection id:    7
Current database: *** NONE ***

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CHECKSUM TABLE flag_test_h;
+-------------------------+------------+
| Table                   | Checksum   |
+-------------------------+------------+
| bp_opt_test.flag_test_h | 2122123514 |
+-------------------------+------------+
1 row in set (2.48 sec)

mysql> EXPLAIN SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | h2    | NULL       | ref    | PRIMARY,flag  | flag    | 5       | const             | 3600 |   100.00 | Using index |
|  1 | SIMPLE      | h     | NULL       | eq_ref | PRIMARY,flag  | PRIMARY | 4       | bp_opt_test.h2.id |    1 |     5.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

続いて、バッファプール（メモリ）上のデータの読み取りコスト係数を4に変更し、（計算上の）コストを高くします。

コスト係数を変える

mysql> UPDATE mysql.engine_cost SET cost_value=4.0 WHERE cost_name = 'memory_block_read_cost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH OPTIMIZER_COSTS;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment | default_value |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| default     |           0 | io_block_read_cost     |       NULL | 2017-12-08 11:48:31 | NULL    |             1 |
| default     |           0 | memory_block_read_cost |          4 | 2017-12-08 13:36:45 | NULL    |          0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
2 rows in set (0.00 sec)

一旦接続を切り、再接続してからEXPLAINを見てみると、（計算上の）バッファプールからの読み取りコストが大きくなったので、元の実行計画と同様（⇒**「flag_test_h」が駆動表**）になりました。

再接続後

mysql> USE bp_opt_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CHECKSUM TABLE flag_test_h;
+-------------------------+------------+
| Table                   | Checksum   |
+-------------------------+------------+
| bp_opt_test.flag_test_h | 2122123514 |
+-------------------------+------------+
1 row in set (2.49 sec)

mysql> EXPLAIN SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | h     | NULL       | ref    | PRIMARY,flag  | flag    | 5       | const            | 1600 |   100.00 | Using index |
|  1 | SIMPLE      | h2    | NULL       | eq_ref | PRIMARY,flag  | PRIMARY | 4       | bp_opt_test.h.id |    1 |     5.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1;
+---------+
| id      |
+---------+
|       1 |
|       2 |
|       3 |
（中略）
| 1380000 |
| 1390000 |
| 1400000 |
+---------+
280 rows in set (0.09 sec)

※コストモデルのテーブルの設定値はサーバ再起動後も残りますので、元に戻しておきます。

コスト係数を元に戻す

mysql> UPDATE mysql.engine_cost SET cost_value=NULL WHERE cost_name = 'memory_block_read_cost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH OPTIMIZER_COSTS;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment | default_value |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| default     |           0 | io_block_read_cost     |       NULL | 2017-12-08 11:48:31 | NULL    |             1 |
| default     |           0 | memory_block_read_cost |       NULL | 2017-12-08 13:41:52 | NULL    |          0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
2 rows in set (0.00 sec)

なお、コストモデルのテーブル自体はMySQL 5.7から存在し、設定値の変更も可能ですが、ストレージ（ディスク）／バッファプール（メモリ）のコスト係数は実際の実行計画には反映されず、同一のものとみなされるようです。

3. まとめ

実際、バッファプールに載っているのとストレージから読み取るのでは実行時間が変わるので、妥当な変更だと思います。

ただ、テーブル結合の場合、内部表の読み取りが1回でも行われると読み取られた部分のデータページがバッファプールに載り、結果として当初のコスト計算とは実行時間が変化するはずなので（バッファプールのOldページを考慮に入れると、何回目の読み取りかによっても変化する、と言える）、「ちょっと賢くなったな」程度に見ておくのがいいと思います。

4. オプティマイザトレースの結果とdiff

4-1. 両テーブルがバッファプールに載っていないケース

両テーブルがバッファプールに載っていないケース

mysql> SELECT * FROM information_schema.optimizer_trace\G
*************************** 1. row ***************************
                            QUERY: SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `h`.`id` AS `id` from `flag_test_h` `h` join `flag_test_h2` `h2` where ((`h`.`id` = `h2`.`id`) and (`h`.`flag` = 1) and (`h2`.`flag` = 1))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`h`.`id` = `h2`.`id`) and (`h`.`flag` = 1) and (`h2`.`flag` = 1))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(multiple equal(`h`.`id`, `h2`.`id`) and multiple equal(1, `h`.`flag`) and multiple equal(1, `h2`.`flag`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(multiple equal(`h`.`id`, `h2`.`id`) and multiple equal(1, `h`.`flag`) and multiple equal(1, `h2`.`flag`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(multiple equal(`h`.`id`, `h2`.`id`) and multiple equal(1, `h`.`flag`) and multiple equal(1, `h2`.`flag`))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`flag_test_h` `h`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              },
              {
                "table": "`flag_test_h2` `h2`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`flag_test_h` `h`",
                "field": "id",
                "equals": "`h2`.`id`",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h` `h`",
                "field": "flag",
                "equals": "1",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h` `h`",
                "field": "id",
                "equals": "`h2`.`id`",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h2` `h2`",
                "field": "id",
                "equals": "`h`.`id`",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h2` `h2`",
                "field": "flag",
                "equals": "1",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h2` `h2`",
                "field": "id",
                "equals": "`h`.`id`",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`flag_test_h` `h`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1347159,
                    "cost": 146282
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "flag",
                      "usable": true,
                      "key_parts": [
                        "flag",
                        "id"
                      ]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "flag",
                    "cost": 136193,
                    "chosen": true
                  },
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "flag",
                        "ranges": [
                          "1 <= flag <= 1"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 1600,
                        "cost": 162.76,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "flag",
                      "rows": 1600,
                      "ranges": [
                        "1 <= flag <= 1"
                      ]
                    },
                    "rows_for_plan": 1600,
                    "cost_for_plan": 162.76,
                    "chosen": true
                  }
                }
              },
              {
                "table": "`flag_test_h2` `h2`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1352657,
                    "cost": 146896
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "flag",
                      "usable": true,
                      "key_parts": [
                        "flag",
                        "id"
                      ]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "flag",
                    "cost": 136746,
                    "chosen": true
                  },
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "flag",
                        "ranges": [
                          "1 <= flag <= 1"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 3600,
                        "cost": 364.94,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "flag",
                      "rows": 3600,
                      "ranges": [
                        "1 <= flag <= 1"
                      ]
                    },
                    "rows_for_plan": 3600,
                    "cost_for_plan": 364.94,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`flag_test_h` `h`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "flag",
                      "rows": 1600,
                      "cost": 162.75,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "flag"
                      },
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 1600,
                "cost_for_plan": 162.75,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`flag_test_h` `h`"
                    ],
                    "table": "`flag_test_h2` `h2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 1760,
                          "chosen": true,
                          "cause": "clustered_pk_chosen_by_heuristics"
                        },
                        {
                          "rows_to_scan": 3600,
                          "filtering_effect": [
                          ],
                          "final_filtering_effect": 1,
                          "access_type": "range",
                          "range_details": {
                            "used_index": "flag"
                          },
                          "resulting_rows": 3600,
                          "cost": 1.16e6,
                          "chosen": false
                        }
                      ]
                    },
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 1600,
                    "cost_for_plan": 1922.8,
                    "chosen": true
                  }
                ]
              },
              {
                "plan_prefix": [
                ],
                "table": "`flag_test_h2` `h2`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "flag",
                      "rows": 3600,
                      "cost": 364.93,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "flag"
                      },
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 3600,
                "cost_for_plan": 364.93,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`flag_test_h2` `h2`"
                    ],
                    "table": "`flag_test_h` `h`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 3960,
                          "chosen": true,
                          "cause": "clustered_pk_chosen_by_heuristics"
                        },
                        {
                          "rows_to_scan": 1600,
                          "filtering_effect": [
                          ],
                          "final_filtering_effect": 1,
                          "access_type": "range",
                          "range_details": {
                            "used_index": "flag"
                          },
                          "resulting_rows": 1600,
                          "cost": 1.16e6,
                          "chosen": false
                        }
                      ]
                    },
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 3600,
                    "cost_for_plan": 4324.9,
                    "pruned_by_cost": true
                  }
                ]
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`h2`.`flag` = 1) and (`h`.`flag` = 1) and (`h2`.`id` = `h`.`id`))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`flag_test_h` `h`",
                  "attached": null
                },
                {
                  "table": "`flag_test_h2` `h2`",
                  "attached": "(`h2`.`flag` = 1)"
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`flag_test_h` `h`"
              },
              {
                "table": "`flag_test_h2` `h2`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

4-2. 「flag_test_h」テーブルがバッファプールに載っているケース

「flag_test_h」テーブルがバッファプールに載っているケース

mysql> SELECT * FROM information_schema.optimizer_trace\G
*************************** 1. row ***************************
                            QUERY: SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `h`.`id` AS `id` from `flag_test_h` `h` join `flag_test_h2` `h2` where ((`h`.`id` = `h2`.`id`) and (`h`.`flag` = 1) and (`h2`.`flag` = 1))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`h`.`id` = `h2`.`id`) and (`h`.`flag` = 1) and (`h2`.`flag` = 1))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(multiple equal(`h`.`id`, `h2`.`id`) and multiple equal(1, `h`.`flag`) and multiple equal(1, `h2`.`flag`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(multiple equal(`h`.`id`, `h2`.`id`) and multiple equal(1, `h`.`flag`) and multiple equal(1, `h2`.`flag`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(multiple equal(`h`.`id`, `h2`.`id`) and multiple equal(1, `h`.`flag`) and multiple equal(1, `h2`.`flag`))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`flag_test_h` `h`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              },
              {
                "table": "`flag_test_h2` `h2`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`flag_test_h` `h`",
                "field": "id",
                "equals": "`h2`.`id`",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h` `h`",
                "field": "flag",
                "equals": "1",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h` `h`",
                "field": "id",
                "equals": "`h2`.`id`",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h2` `h2`",
                "field": "id",
                "equals": "`h`.`id`",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h2` `h2`",
                "field": "flag",
                "equals": "1",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h2` `h2`",
                "field": "id",
                "equals": "`h`.`id`",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`flag_test_h` `h`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1347159,
                    "cost": 137609
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "flag",
                      "usable": true,
                      "key_parts": [
                        "flag",
                        "id"
                      ]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "flag",
                    "cost": 136193,
                    "chosen": true
                  },
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "flag",
                        "ranges": [
                          "1 <= flag <= 1"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 1600,
                        "cost": 162.76,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "flag",
                      "rows": 1600,
                      "ranges": [
                        "1 <= flag <= 1"
                      ]
                    },
                    "rows_for_plan": 1600,
                    "cost_for_plan": 162.76,
                    "chosen": true
                  }
                }
              },
              {
                "table": "`flag_test_h2` `h2`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1352657,
                    "cost": 146896
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "flag",
                      "usable": true,
                      "key_parts": [
                        "flag",
                        "id"
                      ]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "flag",
                    "cost": 136746,
                    "chosen": true
                  },
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "flag",
                        "ranges": [
                          "1 <= flag <= 1"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 3600,
                        "cost": 364.94,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "flag",
                      "rows": 3600,
                      "ranges": [
                        "1 <= flag <= 1"
                      ]
                    },
                    "rows_for_plan": 3600,
                    "cost_for_plan": 364.94,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`flag_test_h` `h`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "flag",
                      "rows": 1600,
                      "cost": 162.75,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "flag"
                      },
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 1600,
                "cost_for_plan": 162.75,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`flag_test_h` `h`"
                    ],
                    "table": "`flag_test_h2` `h2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 1760,
                          "chosen": true,
                          "cause": "clustered_pk_chosen_by_heuristics"
                        },
                        {
                          "rows_to_scan": 3600,
                          "filtering_effect": [
                          ],
                          "final_filtering_effect": 1,
                          "access_type": "range",
                          "range_details": {
                            "used_index": "flag"
                          },
                          "resulting_rows": 3600,
                          "cost": 1.16e6,
                          "chosen": false
                        }
                      ]
                    },
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 1600,
                    "cost_for_plan": 1922.8,
                    "chosen": true
                  }
                ]
              },
              {
                "plan_prefix": [
                ],
                "table": "`flag_test_h2` `h2`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "flag",
                      "rows": 3600,
                      "cost": 364.93,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "flag"
                      },
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 3600,
                "cost_for_plan": 364.93,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`flag_test_h2` `h2`"
                    ],
                    "table": "`flag_test_h` `h`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 1260,
                          "chosen": true,
                          "cause": "clustered_pk_chosen_by_heuristics"
                        },
                        {
                          "rows_to_scan": 1600,
                          "filtering_effect": [
                          ],
                          "final_filtering_effect": 1,
                          "access_type": "range",
                          "range_details": {
                            "used_index": "flag"
                          },
                          "resulting_rows": 1600,
                          "cost": 1.16e6,
                          "chosen": false
                        }
                      ]
                    },
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 3600,
                    "cost_for_plan": 1624.9,
                    "chosen": true
                  }
                ]
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`h2`.`flag` = 1) and (`h`.`flag` = 1) and (`h`.`id` = `h2`.`id`))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`flag_test_h2` `h2`",
                  "attached": null
                },
                {
                  "table": "`flag_test_h` `h`",
                  "attached": "(`h`.`flag` = 1)"
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`flag_test_h2` `h2`"
              },
              {
                "table": "`flag_test_h` `h`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

4-3. コスト係数を変えたケース

コスト係数を変えたケース

mysql> SELECT * FROM information_schema.optimizer_trace\G
*************************** 1. row ***************************
                            QUERY: SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `h`.`id` AS `id` from `flag_test_h` `h` join `flag_test_h2` `h2` where ((`h`.`id` = `h2`.`id`) and (`h`.`flag` = 1) and (`h2`.`flag` = 1))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`h`.`id` = `h2`.`id`) and (`h`.`flag` = 1) and (`h2`.`flag` = 1))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(multiple equal(`h`.`id`, `h2`.`id`) and multiple equal(1, `h`.`flag`) and multiple equal(1, `h2`.`flag`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(multiple equal(`h`.`id`, `h2`.`id`) and multiple equal(1, `h`.`flag`) and multiple equal(1, `h2`.`flag`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(multiple equal(`h`.`id`, `h2`.`id`) and multiple equal(1, `h`.`flag`) and multiple equal(1, `h2`.`flag`))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`flag_test_h` `h`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              },
              {
                "table": "`flag_test_h2` `h2`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`flag_test_h` `h`",
                "field": "id",
                "equals": "`h2`.`id`",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h` `h`",
                "field": "flag",
                "equals": "1",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h` `h`",
                "field": "id",
                "equals": "`h2`.`id`",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h2` `h2`",
                "field": "id",
                "equals": "`h`.`id`",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h2` `h2`",
                "field": "flag",
                "equals": "1",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h2` `h2`",
                "field": "id",
                "equals": "`h`.`id`",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`flag_test_h` `h`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1347159,
                    "cost": 180974
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "flag",
                      "usable": true,
                      "key_parts": [
                        "flag",
                        "id"
                      ]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "flag",
                    "cost": 136207,
                    "chosen": true
                  },
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "flag",
                        "ranges": [
                          "1 <= flag <= 1"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 1600,
                        "cost": 162.79,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "flag",
                      "rows": 1600,
                      "ranges": [
                        "1 <= flag <= 1"
                      ]
                    },
                    "rows_for_plan": 1600,
                    "cost_for_plan": 162.79,
                    "chosen": true
                  }
                }
              },
              {
                "table": "`flag_test_h2` `h2`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1352657,
                    "cost": 146896
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "flag",
                      "usable": true,
                      "key_parts": [
                        "flag",
                        "id"
                      ]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "flag",
                    "cost": 136773,
                    "chosen": true
                  },
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "flag",
                        "ranges": [
                          "1 <= flag <= 1"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 3600,
                        "cost": 365.03,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "flag",
                      "rows": 3600,
                      "ranges": [
                        "1 <= flag <= 1"
                      ]
                    },
                    "rows_for_plan": 3600,
                    "cost_for_plan": 365.03,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`flag_test_h` `h`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "flag",
                      "rows": 1600,
                      "cost": 162.78,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "flag"
                      },
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 1600,
                "cost_for_plan": 162.78,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`flag_test_h` `h`"
                    ],
                    "table": "`flag_test_h2` `h2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 1760,
                          "chosen": true,
                          "cause": "clustered_pk_chosen_by_heuristics"
                        },
                        {
                          "rows_to_scan": 3600,
                          "filtering_effect": [
                          ],
                          "final_filtering_effect": 1,
                          "access_type": "range",
                          "range_details": {
                            "used_index": "flag"
                          },
                          "resulting_rows": 3600,
                          "cost": 1.16e6,
                          "chosen": false
                        }
                      ]
                    },
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 1600,
                    "cost_for_plan": 1922.8,
                    "chosen": true
                  }
                ]
              },
              {
                "plan_prefix": [
                ],
                "table": "`flag_test_h2` `h2`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "flag",
                      "rows": 3600,
                      "cost": 365.02,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "flag"
                      },
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 3600,
                "cost_for_plan": 365.02,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`flag_test_h2` `h2`"
                    ],
                    "table": "`flag_test_h` `h`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 14760,
                          "chosen": true,
                          "cause": "clustered_pk_chosen_by_heuristics"
                        },
                        {
                          "rows_to_scan": 1600,
                          "filtering_effect": [
                          ],
                          "final_filtering_effect": 1,
                          "access_type": "range",
                          "range_details": {
                            "used_index": "flag"
                          },
                          "resulting_rows": 1600,
                          "cost": 1.16e6,
                          "chosen": false
                        }
                      ]
                    },
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 3600,
                    "cost_for_plan": 15125,
                    "pruned_by_cost": true
                  }
                ]
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`h2`.`flag` = 1) and (`h`.`flag` = 1) and (`h2`.`id` = `h`.`id`))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`flag_test_h` `h`",
                  "attached": null
                },
                {
                  "table": "`flag_test_h2` `h2`",
                  "attached": "(`h2`.`flag` = 1)"
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`flag_test_h` `h`"
              },
              {
                "table": "`flag_test_h2` `h2`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.01 sec)

4-4. 4-1. と 4-2. のdiff

計算されたコスト値の変化により、駆動表／内部表が入れ替わっているのが分かります。

1と2のdiff

$ diff opt_tr_1.txt opt_tr_2.txt
109c109
<                     "cost": 146282
---
>                     "cost": 137609
360c360
<                           "cost": 3960,
---
>                           "cost": 1260,
381,382c381,382
<                     "cost_for_plan": 4324.9,
<                     "pruned_by_cost": true
---
>                     "cost_for_plan": 1624.9,
>                     "chosen": true
390c390
<               "original_condition": "((`h2`.`flag` = 1) and (`h`.`flag` = 1) and (`h2`.`id` = `h`.`id`))",
---
>               "original_condition": "((`h2`.`flag` = 1) and (`h`.`flag` = 1) and (`h`.`id` = `h2`.`id`))",
395c395
<                   "table": "`flag_test_h` `h`",
---
>                   "table": "`flag_test_h2` `h2`",
399,400c399,400
<                   "table": "`flag_test_h2` `h2`",
<                   "attached": "(`h2`.`flag` = 1)"
---
>                   "table": "`flag_test_h` `h`",
>                   "attached": "(`h`.`flag` = 1)"
408c408
<                 "table": "`flag_test_h` `h`"
---
>                 "table": "`flag_test_h2` `h2`"
411c411
<                 "table": "`flag_test_h2` `h2`"
---
>                 "table": "`flag_test_h` `h`"

4-5. 4-1. と 4-3. のdiff

計算されたコスト値のみ変化しています。

1と3のdiff

$ diff opt_tr_1.txt opt_tr_3.txt
109c109
<                     "cost": 146282
---
>                     "cost": 180974
128c128
<                     "cost": 136193,
---
>                     "cost": 136207,
149c149
<                         "cost": 162.76,
---
>                         "cost": 162.79,
168c168
<                     "cost_for_plan": 162.76,
---
>                     "cost_for_plan": 162.79,
197c197
<                     "cost": 136746,
---
>                     "cost": 136773,
218c218
<                         "cost": 364.94,
---
>                         "cost": 365.03,
237c237
<                     "cost_for_plan": 364.94,
---
>                     "cost_for_plan": 365.03,
262c262
<                       "cost": 162.75,
---
>                       "cost": 162.78,
277c277
<                 "cost_for_plan": 162.75,
---
>                 "cost_for_plan": 162.78,
332c332
<                       "cost": 364.93,
---
>                       "cost": 365.02,
347c347
<                 "cost_for_plan": 364.93,
---
>                 "cost_for_plan": 365.02,
360c360
<                           "cost": 3960,
---
>                           "cost": 14760,
381c381
<                     "cost_for_plan": 4324.9,
---
>                     "cost_for_plan": 15125,

5. おっと、

書くのに時間が掛かって、Advent Calendarの自分の記事の公開日と被った…。

Qiitaに投稿したMySQL 8.0関連記事