Description:
Optimizer doesn't take into account that latin1 is subset of utf8 and query doesn't use index. However with manually specified CONVERT( .. USING latin1) index is used.
mysql> EXPLAIN SELECT * FROM latin1_tbl l, utf_tbl u WHERE l.col1=u.col1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | l | ALL | NULL | NULL | NULL | NULL | 11 | |
| 1 | SIMPLE | u | ALL | col1 | NULL | NULL | NULL | 6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM latin1_tbl l, utf_tbl u WHERE l.col1=CONVERT(u.col1 using latin1);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 7 | |
| 1 | SIMPLE | l | ref | col1 | col1 | 11 | func | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)
How to repeat:
1. restore table from dump file
2. run the following EXPLAINs:
EXPLAIN SELECT * FROM latin1_tbl l, utf_tbl u WHERE l.col1=u.col1;
EXPLAIN SELECT * FROM latin1_tbl l, utf_tbl u WHERE l.col1=CONVERT(u.col1 using latin1);