| 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: | |
| 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 | ||
[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)
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;