Description:
MySQL 4.1 is not able to execute join on long text fields with preffix indexes efficiently,
according to EXPLAIN:
This is pretty serious issue as varchar(N) with n>255 is converted to text field.
It does not happen with VARCHAR(255).
MySQL 4.1:
mysql> explain select * from a,b where a.t=b.t;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
| 1 | SIMPLE | a | ALL | t | NULL | NULL | NULL | 5 | |
| 1 | SIMPLE | b | ALL | t | NULL | NULL | NULL | 10 | Range checked for each record (index map: 0x1) |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
2 rows in set (0.01 sec)
MySQL 4.0
+-------+------+---------------+------+---------+------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+------+-------------+
| b | ALL | t | NULL | NULL | NULL | 10 | |
| a | ALL | t | NULL | NULL | NULL | 4 | Using where |
+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (9.10 sec)
Note. FORCE INDEX does not change explain on 4.1
How to repeat:
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
`t` text,
KEY `t` (`t`(80))
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `a` VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
CREATE TABLE `b` (
`t` text,
KEY `t` (`t`(80))
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `b` VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
explain select * from a,b where a.t=b.t;
Description: MySQL 4.1 is not able to execute join on long text fields with preffix indexes efficiently, according to EXPLAIN: This is pretty serious issue as varchar(N) with n>255 is converted to text field. It does not happen with VARCHAR(255). MySQL 4.1: mysql> explain select * from a,b where a.t=b.t; +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+ | 1 | SIMPLE | a | ALL | t | NULL | NULL | NULL | 5 | | | 1 | SIMPLE | b | ALL | t | NULL | NULL | NULL | 10 | Range checked for each record (index map: 0x1) | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+ 2 rows in set (0.01 sec) MySQL 4.0 +-------+------+---------------+------+---------+------+------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+------+---------+------+------+-------------+ | b | ALL | t | NULL | NULL | NULL | 10 | | | a | ALL | t | NULL | NULL | NULL | 4 | Using where | +-------+------+---------------+------+---------+------+------+-------------+ 2 rows in set (9.10 sec) Note. FORCE INDEX does not change explain on 4.1 How to repeat: DROP TABLE IF EXISTS `a`; CREATE TABLE `a` ( `t` text, KEY `t` (`t`(80)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `a` VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'); CREATE TABLE `b` ( `t` text, KEY `t` (`t`(80)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `b` VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); explain select * from a,b where a.t=b.t;