MySQL 8.0.1からJOIN_ORDERヒントが書ける

こんな、ORDER BY狙いのキーを使いたくなるクエリーがあるじゃろ？

mysql80> EXPLAIN SELECT Name, Language, Population, Percentage FROM CountryLanguage LEFT JOIN Country ON Country.Code= CountryLanguage.CountryCode WHERE Country.continent = 'Asia' ORDER BY Percentage LIMIT 5;
+----+-------------+-----------------+------------+------+------------------------------+---------+---------+--------------------+------+----------+----------------------------------------------+
| id | select_type | table           | partitions | type | possible_keys                | key     | key_len | ref                | rows | filtered | Extra                                        |
+----+-------------+-----------------+------------+------+------------------------------+---------+---------+--------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | Country         | NULL       | ALL  | PRIMARY,index_code_continent | NULL    | NULL    | NULL               |  239 |    14.29 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | CountryLanguage | NULL       | ref  | PRIMARY,CountryCode          | PRIMARY | 3       | world.Country.Code |    4 |   100.00 | NULL                                         |
+----+-------------+-----------------+------------+------+------------------------------+---------+---------+--------------------+------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

JOINしているクエリーでORDER BY狙いのキーを効かせるにはORDER BY狙いのキーが駆動表側にないといけない ので、

1. STRAIGHT_JOIN に書き換えてORDER BYで使っているカラムを駆動表に固定する。ただしSTRAIGHT_JOINは内部結合なので、LEFT JOINは書き換えられない。
2. USE INDEXかFORCE INDEX でORDER BY狙いのキーを狙い撃つ。大概の場合はこれで上手く動くんだけれど、最悪の場合 内部表のままORDER BY狙いのキーを使ってインデックススキャンががががが

INNER JOINなら 1. + 2. （たまに、STRAIGHT_JOINでもORDER BY狙いのキーを取らないことがあったりした。最近少ない気がする）、そうでなければ 2. だけとしてORDER BY狙いのキーを押し込むことが多かったけれど、MySQL 8.0.1からは JOIN_ORDERのヒント句 が使えるようになった。

mysql80> EXPLAIN SELECT /*+ JOIN_ORDER (CountryLanguage, Country) */ Name, Language, Population, Percentage FROM CountryLanguage LEFT JOIN Country ON Country.Code= CountryLanguage.CountryCode WHERE Country.continent = 'Asia' ORDER BY Percentage LIMIT 5;
+----+-------------+-----------------+------------+--------+------------------------------+------------------+---------+-----------------------------------+------+----------+-------------+
| id | select_type | table           | partitions | type   | possible_keys                | key              | key_len | ref                               | rows | filtered | Extra       |
+----+-------------+-----------------+------------+--------+------------------------------+------------------+---------+-----------------------------------+------+----------+-------------+
|  1 | SIMPLE      | CountryLanguage | NULL       | index  | PRIMARY,CountryCode          | index_percentage | 4       | NULL                              |    5 |   100.00 | Using index |
|  1 | SIMPLE      | Country         | NULL       | eq_ref | PRIMARY,index_code_continent | PRIMARY          | 3       | world.CountryLanguage.CountryCode |    1 |    14.29 | Using where |
+----+-------------+-----------------+------------+--------+------------------------------+------------------+---------+-----------------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql80> EXPLAIN SELECT /*+ JOIN_PREFIX (Country) */ Name, Language, Population, Percentage FROM CountryLanguage LEFT JOIN Country ON Country.Code= CountryLanguage.CountryCode WHERE Country.continent = 'Asia' ORDER BY Percentage LIMIT 5;
+----+-------------+-----------------+------------+------+------------------------------+---------+---------+--------------------+------+----------+----------------------------------------------+
| id | select_type | table           | partitions | type | possible_keys                | key     | key_len | ref                | rows | filtered | Extra                                        |
+----+-------------+-----------------+------------+------+------------------------------+---------+---------+--------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | Country         | NULL       | ALL  | PRIMARY,index_code_continent | NULL    | NULL    | NULL               |  239 |    14.29 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | CountryLanguage | NULL       | ref  | PRIMARY,CountryCode          | PRIMARY | 3       | world.Country.Code |    4 |   100.00 | NULL                                         |
+----+-------------+-----------------+------------+------+------------------------------+---------+---------+--------------------+------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql80> EXPLAIN SELECT /*+ JOIN_SUFFIX (Country) */ Name, Language, Population, Percentage FROM CountryLanguage LEFT JOIN Country ON Country.Code= CountryLanguage.CountryCode WHERE Country.continent = 'Asia' ORDER BY Percentage LIMIT 5;
+----+-------------+-----------------+------------+--------+------------------------------+------------------+---------+-----------------------------------+------+----------+-------------+
| id | select_type | table           | partitions | type   | possible_keys                | key              | key_len | ref                               | rows | filtered | Extra       |
+----+-------------+-----------------+------------+--------+------------------------------+------------------+---------+-----------------------------------+------+----------+-------------+
|  1 | SIMPLE      | CountryLanguage | NULL       | index  | PRIMARY,CountryCode          | index_percentage | 4       | NULL                              |    5 |   100.00 | Using index |
|  1 | SIMPLE      | Country         | NULL       | eq_ref | PRIMARY,index_code_continent | PRIMARY          | 3       | world.CountryLanguage.CountryCode |    1 |    14.29 | Using where |
+----+-------------+-----------------+------------+--------+------------------------------+------------------+---------+-----------------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

JOIN_ORDER(先に来るテーブル, 後に来るテーブル), または JOIN_PREFIX(先に来るテーブル), JOIN_SUFFIX(後に来るテーブル) の3つの書き方で指定できるぽい。

ORDER BY狙いのキーなら一番外側にあればそれでいいので、3つ以上の時も JOIN_ORDER より JOIN_PREFIX がいいのかな。

これでLEFT JOINでもORDER BY狙いのキーが狙いやすくなってすてきだ。

​