joinするときは照合順序に気を付ける

照合順序が違うものどうしをjoinすると、インデックスとか全く効かなくなる ことがある。
utf8_bin と ujis_bin だと中身が英数字のみであってもアウト。
最低限、joinするカラム同士の照合順序は合わせておくのが肝要。

MariaDB [tanuki]> explain select count(*) from tableA inner join tableB on tableA.C3=tableB.C1;
+------+-------------+--------+-------+---------------+---------+---------+------------------+------+-------------+
| id   | select_type | table  | type  | possible_keys | key     | key_len | ref              | rows | Extra       |
+------+-------------+--------+-------+---------------+---------+---------+------------------+------+-------------+
|    1 | SIMPLE      | tableA | index | NULL          | PRIMARY | 103     | NULL             |  227 | Using index |
|    1 | SIMPLE      | tableB | ref   | PRIMARY,C1    | C1      | 50      | tanuki.tableA.C3 |    1 | Using index |
+------+-------------+--------+-------+---------------+---------+---------+------------------+------+-------------+

MariaDB [tanuki]> explain select count(*) from tableA inner join tableB on tableA.C3=tableB.C1;
+------+-------------+--------+-------+---------------+---------+---------+------+---------+--------------------------------------------------------------+
| id   | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows    | Extra                                                        |
+------+-------------+--------+-------+---------------+---------+---------+------+---------+--------------------------------------------------------------+
|    1 | SIMPLE      | tableA | index | NULL          | PRIMARY | 103     | NULL |     227 | Using index                                                  |
|    1 | SIMPLE      | tableB | index | NULL          | C1      | 50      | NULL | 3631385 | Using where; Using index; Using join buffer (flat, BNL join) |
+------+-------------+--------+-------+---------------+---------+---------+------+---------+--------------------------------------------------------------+