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)
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)