Bug #10162 Optimizer: Problem with Indexes
Submitted: 25 Apr 2005 21:13 Modified: 28 Apr 2005 15:26
Reporter: Andre Justus Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.4 OS:Linux (Debian)
Assigned to: Sergei Golubchik CPU Architecture:Any

[25 Apr 2005 21:13] Andre Justus
Description:
Mysql 5.0.4 behaves buggy when using (multiple)indexes and joins,whereas mysql 4.1.10 behaves as expected.
I don't exactely know where the problem is, but in the 'how to repeat' part, the bug is described detailed enough, I hope.

How to repeat:
CREATE TABLE `table_a` (
  `gid` smallint(5) unsigned NOT NULL default '0',
  `x` int(11) NOT NULL default '0',
  `y` int(11) NOT NULL default '0',
  `art` int(11) NOT NULL default '0',
  PRIMARY KEY  (`gid`,`x`,`y`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `table_a` VALUES (1, -5, -8, 2);
INSERT INTO `table_a` VALUES (1, 2, 2, 1);
INSERT INTO `table_a` VALUES (1, 1, 1, 1);

CREATE TABLE `table_b` (
  `gid` smallint(5) unsigned NOT NULL default '0',
  `x` int(11) NOT NULL default '0',
  `y` int(11) NOT NULL default '0',
  `id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`gid`,`id`,`x`,`y`),
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `table_b` VALUES (1, -5, -8, 1);
INSERT INTO `table_b` VALUES (1, 1, 1, 1);
INSERT INTO `table_b` VALUES (1, 2, 2, 1);

CREATE TABLE `table_c` (
  `set_id` smallint(5) unsigned NOT NULL default '0',
  `id` tinyint(4) unsigned NOT NULL auto_increment,
  `name` char(12) NOT NULL default '',
  PRIMARY KEY  (`id`,`set_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `table_c` VALUES (0, 1, 'a');
INSERT INTO `table_c` VALUES (1, 1, 'b');
INSERT INTO `table_c` VALUES (0, 2, 'c');
INSERT INTO `table_c` VALUES (1, 2, 'd');
INSERT INTO `table_c` VALUES (1, 3, 'e');
INSERT INTO `table_c` VALUES (1, 4, 'f');
INSERT INTO `table_c` VALUES (1, 5, 'g');
INSERT INTO `table_c` VALUES (1, 6, 'h');
        
#########################################

EXPLAIN SELECT name
FROM table_a a
LEFT JOIN table_b av ON a.x = av.x
AND a.y = av.y
LEFT JOIN table_c al ON a.art = al.id
WHERE av.id =1
AND av.x = -5
AND av.y =-8
AND a.gid =1
AND av.gid =1
AND al.set_id =1

mysql 5.0.4==>
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------+
|  1 | SIMPLE      | a     | ref    | PRIMARY       | PRIMARY | 2       | const            |    2 |                          |
|  1 | SIMPLE      | av    | index  | NULL          | PRIMARY | 14      | NULL             |    3 | Using where; Using index |
|  1 | SIMPLE      | al    | eq_ref | PRIMARY       | PRIMARY | 3       | test.a.art,const |    1 | Using where              |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------+
3 rows in set (0,00 sec)

mysql4.1.10 =>

+----+-------------+-------+-------+---------------+---------+---------+-------------------------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref                     | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY |      10 | const,const,const       |    1 |             |
|  1 | SIMPLE      | av    | const | PRIMARY,id    | PRIMARY |      14 | const,const,const,const |    1 | Using index |
|  1 | SIMPLE      | al    | const | PRIMARY       | PRIMARY |       3 | const,const             |    1 |             |
+----+-------------+-------+-------+---------------+---------+---------+-------------------------+------+-------------+
3 rows in set (0.00 sec)

When I have discovered the bug I had about 160000 rows in table_a and 450 rows in table_b, table_c is the same like above.
After a querytime of 5 minutes I killed the query,
the Explain there was the same like above in mysql 4.1.10 but mysl 5.1.4 was a bit different:

+----+-------------+-------+--------+---------------+---------+---------+------------
-------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref
       | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+------------
-------+------+--------------------------+
|  1 | SIMPLE      | a     | ALL    | PRIMARY       | NULL    | NULL    | NULL
       | 7651 | Using where              |
|  1 | SIMPLE      | al    | eq_ref | PRIMARY       | PRIMARY | 3       | test.a.art
,const |    1 | Using where              |
|  1 | SIMPLE      | av    | index  | NULL          | PRIMARY | 14      | NULL
       |  449 | Using where; Using index |
+----+-------------+-------+--------+---------------+---------+---------+------------
-------+------+--------------------------+
3 rows in set (0,00 sec)
[26 Apr 2005 22:38] Sergei Golubchik
Fixed in 5.0.6
[26 Apr 2005 23:34] 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/internals/24341
[28 Apr 2005 15:26] Paul DuBois
Noted in 5.0.6 changelog.