Bug #6072 MySQL 4.1 performance regression while comparing text fields
Submitted: 13 Oct 2004 19:22 Modified: 23 Nov 2004 16:42
Reporter: Peter Zaitsev (Basic Quality Contributor) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1.6 (bk) OS:Any (all)
Assigned to: Timour Katchaounov CPU Architecture:Any

[13 Oct 2004 19:22] Peter Zaitsev
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;
[23 Nov 2004 16:42] Timour Katchaounov
This is expected behavior and the issue will be addressed in a future feature.