Description:
I don't know if you'll reply at this adress without a support contract, but I'd
like to bring this to your attention, and if possible get a pointer to what to
do.
This is a rather serious issue we have discovered since upgrading to MySQL
4.0.16-standard.
It seems that WHERE IN ( .. ) works differently from what it did in 3.23.57,
when combined with two or more INNER JOINs. It does not seem to use indexes,
and behaves like OR.
Is there a reasonable explanation to why this occurs? Is there a reference in
the manual to this? In 3.23, the indexes were used just perfect.
Tables:
team: 97173504 bytes, 50151424 bytes index, 293156 rows
series: 2637824 bytes, 3178496 bytes index, 43588 rows
division: 16384 bytes, 16384 bytes index, 189 rows
CREATE TABLE `series` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(32) NOT NULL default '',
`division_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `division_id_index` (`division_id`),
KEY `name_index` (`name`),
CONSTRAINT `0_2144` FOREIGN KEY (`division_id`) REFERENCES `division` (`id`)
) TYPE=InnoDB
CREATE TABLE `division` (
`id` int(10) unsigned NOT NULL auto_increment,
`level` tinyint(3) unsigned NOT NULL default '0',
`league_id` smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `league_id_index` (`league_id`),
CONSTRAINT `0_2106` FOREIGN KEY (`league_id`) REFERENCES `league` (`id`)
) TYPE=InnoDB
| team |CREATE TABLE `team` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`user_id` bigint(20) unsigned default NULL,
`country_id` tinyint(3) unsigned NOT NULL default '0',
...
PRIMARY KEY (`id`),
KEY `series_id_index` (`series_id`),
KEY `team_name_index` (`name`),
KEY `user_id_index` (`user_id`),
KEY `country_id_index` (`country_id`),
CONSTRAINT `0_2160` FOREIGN KEY (`user_id`) REFERENCES `mz.mzuser` (`id`),
CONSTRAINT `0_2161` FOREIGN KEY (`series_id`) REFERENCES `series` (`id`),
CONSTRAINT `0_2162` FOREIGN KEY (`country_id`) REFERENCES `mz.country` (`id`)
) TYPE=InnoDB
mysql> SELECT T.id, D.level FROM team T INNER JOIN series S ON T.series_id =
S.id INNER JOIN division D ON S.division_id = D.id WHERE T.id IN (14098,
13156);
+-------+-------+
| id | level |
+-------+-------+
| 13156 | 9 |
| 14098 | 9 |
+-------+-------+
2 rows in set (0.62 sec)
mysql> explain SELECT T.id, D.level FROM team T INNER JOIN series S ON
T.series_id = S.id INNER JOIN division D ON D.id = S.division_id WHERE T.id IN
(14098, 13156);
+-------+------+---------------------------+-------------------+---------+------
+------+--------------------------+
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+-------+------+---------------------------+-------------------+---------+------
+------+--------------------------+
| D | ALL | PRIMARY | NULL | NULL | NULL
| 189 | | <== WHY?
| S | ref | PRIMARY,division_id_index | division_id_index | 4 | D.id
| 103 | Using index | <== WHY?
| T | ref | PRIMARY,series_id_index | series_id_index | 5 | S.id
| 2 | Using where; Using index |
+-------+------+---------------------------+-------------------+---------+------
+------+--------------------------+
3 rows in set (0.00 sec)
mysql> SELECT T.id, D.level FROM team T LEFT JOIN series S ON T.series_id =
S.id INNER JOIN division D ON S.division_id = D.id WHERE T.id IN (14098,
13156);
+-------+-------+
| id | level |
+-------+-------+
| 13156 | 9 |
+-------+-------+
2 rows in set (0.04 sec)
mysql> explain SELECT T.id, D.level FROM team T LEFT JOIN series S ON
T.series_id = S.id INNER JOIN division D ON S.division_id = D.id WHERE T.id IN
(14098, 13156);
+-------+--------+---------------+---------+---------+---------------+------+---
----------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+--------+---------------+---------+---------+---------------+------+---
----------+
| T | range | PRIMARY | PRIMARY | 8 | NULL | 2 |
Using where | <==
| S | eq_ref | PRIMARY | PRIMARY | 4 | T.series_id | 1 |
| <==
| D | eq_ref | PRIMARY | PRIMARY | 4 | S.division_id | 1 |
|
+-------+--------+---------------+---------+---------+---------------+------+---
----------+
3 rows in set (0.00 sec)
mysql> explain SELECT T.id, D.level FROM team T INNER JOIN series S ON
T.series_id = S.id INNER JOIN division D ON S.division_id = D.id WHERE T.id IN
(14098);
+-------+------+---------------------------+-------------------+---------+------
+------+--------------------------+
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+-------+------+---------------------------+-------------------+---------+------
+------+--------------------------+
| D | ALL | PRIMARY | NULL | NULL | NULL
| 189 | |
| S | ref | PRIMARY,division_id_index | division_id_index | 4 | D.id
| 103 | Using index |
| T | ref | PRIMARY,series_id_index | series_id_index | 5 | S.id
| 2 | Using where; Using index |
+-------+------+---------------------------+-------------------+---------+------
+------+--------------------------+
3 rows in set (0.00 sec)
mysql> explain SELECT T.id, D.level FROM team T INNER JOIN series S ON
T.series_id = S.id INNER JOIN division D ON S.division_id = D.id WHERE T.id =
14098;
+-------+-------+---------------------------+---------+---------+-------+------+
-------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+-------+-------+---------------------------+---------+---------+-------+------+
-------+
| T | const | PRIMARY,series_id_index | PRIMARY | 8 | const | 1
| |
| S | const | PRIMARY,division_id_index | PRIMARY | 4 | const | 1
| |
| D | const | PRIMARY | PRIMARY | 4 | const | 1
| |
+-------+-------+---------------------------+---------+---------+-------+------+
-------+
3 rows in set (0.00 sec)
How to repeat:
see above
