Bug #25971 Optimizer ignores key on left join in version 5.0.27, 4.1.10 but not in 4.0.27
Submitted: 31 Jan 2007 11:34 Modified: 20 Jun 2010 22:51
Reporter: Thomas Koch Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.0.36-BK, 5.0.27 OS:Linux (SuSE Linux 9.3 (x86-64))
Assigned to: CPU Architecture:Any
Tags: Optimizer ignores key on left join

[31 Jan 2007 11:34] Thomas Koch
Description:
Optimizer ignores key on left join with version 5.0.27 and 4.1.10a, but uses it with version 4.0.27 as expected.
With 4.0.27 the query on the full dataset takes 15sec and with 5.0.27 several hours.
"force index" doesn't have any effect.

How to repeat:
CREATE TABLE `OS` (
  `code` varchar(10) NOT NULL default '',
  `creation` date default NULL,
  `os` text NOT NULL,
  PRIMARY KEY  (`os`(38),`code`),
  KEY `idx_code` (`code`)
) ENGINE=MyISAM;

INSERT INTO `OS` VALUES ('101M','1997-12-13','Physeter catodon'),('102M','1997-12-15','Physeter catodon'),('103L','1992-09-29','Enterobacteria phage T4'),('192L','1995-06-13','Enterobacteria phage T4'),('1u5i','2004-07-27','Rattus norvegicus'),('103D','1994-12-16','Homo sapiens');

CREATE TABLE `names` (
  `tax_id` mediumint(8) unsigned NOT NULL default '0',
  `name_txt` text NOT NULL,
  PRIMARY KEY  (`name_txt`(38),`tax_id`)
) ENGINE=MyISAM;

INSERT INTO `names` VALUES (9606,'Homo sapiens'),(9755,'Physeter catodon'),(10116,'Rattus norvegicus');

explain SELECT OS.code, OS.creation, OS.os, names.tax_id  FROM OS left JOIN names ON OS.os=names.name_txt;
[2 Feb 2007 15:37] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with latest 5.0.36-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.36 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `OS` (
    ->   `code` varchar(10) NOT NULL default '',
    ->   `creation` date default NULL,
    ->   `os` text NOT NULL,
    ->   PRIMARY KEY  (`os`(38),`code`),
    ->   KEY `idx_code` (`code`)
    -> ) ENGINE=MyISAM;

Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO `OS` VALUES ('101M','1997-12-13','Physeter catodon'),
    -> ('102M','1997-12-15','Physeter catodon'),
    -> ('103L','1992-09-29','Enterobacteria phage T4'),
    -> ('192L','1995-06-13','Enterobacteria phage T4'),
    -> ('1u5i','2004-07-27','Rattus norvegicus'),
    -> ('103D','1994-12-16','Homo sapiens');
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE `names` (
    ->   `tax_id` mediumint(8) unsigned NOT NULL default '0',
    ->   `name_txt` text NOT NULL,
    ->   PRIMARY KEY  (`name_txt`(38),`tax_id`)
    -> ) ENGINE=MyISAM;

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `names` VALUES (9606,'Homo sapiens'),
    -> (9755,'Physeter catodon'),(10116,'Rattus norvegicus');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> explain SELECT OS.code, OS.creation, OS.os, names.tax_id
    -> FROM OS left JOIN names ON OS.os=names.name_txt\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: OS
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: names
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra:
2 rows in set (0.00 sec)

mysql> explain SELECT OS.code, OS.creation, OS.os, names.tax_id FROM OS FORCE I
NDEX (PRIMARY) left JOIN names FORCE INDEX (PRIMARY) ON OS.os=names.name_txt\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: OS
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: names
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra:
2 rows in set (0.00 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysqladmin -uroot shutdown
STOPPING server from pid file /home/openxs/dbs/5.0/var/suse.pid
070202 15:52:13  mysqld ended

[1]+  Done                    bin/mysqld_safe

While on 4.0.30-BK we have:

openxs@suse:~/dbs/4.0> bin/mysql -uroot -proot test --host=127.0.0.1 --port=3340
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.30-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `OS` (   `code` varchar(10) NOT NULL default '',   `creatio
n` date default NULL,   `os` text NOT NULL,   PRIMARY KEY  (`os`(38),`code`),
 KEY `idx_code` (`code`) ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `names` (   `tax_id` mediumint(8) unsigned NOT NULL default
 '0',   `name_txt` text NOT NULL,   PRIMARY KEY  (`name_txt`(38),`tax_id`) ) EN
GINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `names` VALUES (9606,'Homo sapiens'), (9755,'Physeter catodon'), (10116,'Rattus norvegicus');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `OS` VALUES ('101M','1997-12-13','Physeter catodon'), ('102M','1997-12-15','Physeter catodon'), ('103L','1992-09-29','Enterobacteria phage T4'), ('192L','1995-06-13','Enterobacteria phage T4'), ('1u5i','2004-07-27','Rattus norvegicus'), ('103D','1994-12-16','Homo sapiens');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> explain SELECT OS.code, OS.creation, OS.os, names.tax_id FROM OS left JO
IN names ON OS.os=names.name_txt\G
*************************** 1. row ***************************
        table: OS
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra:
*************************** 2. row ***************************
        table: names
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 40
          ref: OS.os
         rows: 1
        Extra:
2 rows in set (0.01 sec)

This is a bug in 5.0.x optimizer.
[14 Feb 2007 23:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19917

ChangeSet@1.2414, 2007-02-14 15:39:29-08:00, igor@olga.mysql.com +7 -0
  Fixed bug #25971: indexes on text columns were ignored when ref accesses
  were evaluated.
  According to the new rules for string comparison partial indexes on text
  columns can be used in the same cases when partial indexes on varchar
  columns can be used.
[15 Feb 2007 6:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19923

ChangeSet@1.2414, 2007-02-14 22:06:41-08:00, igor@olga.mysql.com +7 -0
  Fixed bug #25971: indexes on text columns were ignored when ref accesses
  were evaluated.
  According to the new rules for string comparison partial indexes on text
  columns can be used in the same cases when partial indexes on varchar
  columns can be used.
[28 Feb 2007 17:47] Sergei Glukhov
Fixed in 5.0.38, 5.1.17-beta
[6 Mar 2007 19:52] Paul Dubois
Noted in 5.0.38, 5.1.17 changelogs.
[16 Mar 2007 20:27] [ name withheld ]
I just retrieved the current source tree (5.0.40) using bk, and this bug does not appear to be fixed. Left Join's are still ignoring the keys even with a force.
[5 May 2010 15:13] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 2:54] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:48] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:18] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:45] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[30 May 2010 1:04] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 11:49] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:27] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:14] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)