Bug #31234 Optimizer doesn't take into account that latin1 is subset of utf8
Submitted: 27 Sep 2007 12:48
Reporter: Victoria Reznichenko Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:any OS:Any
Assigned to: CPU Architecture:Any

[27 Sep 2007 12:48] Victoria Reznichenko
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);
[27 Sep 2007 12:51] MySQL Verification Team
tables latin1_tbl and utf_tbl

Attachment: tables.sql (application/octet-stream, text), 2.47 KiB.